I have a problem with SQL and VB.

The problem is when I run an SQL query in SQL tools, I get a response back
in 19 seconds.  This is too long, but that is another issue.

When I run the same code in VB through RDO I get the response in 1minute 10
seconds.  This is very, very bad.  I have to loop through this 300 times
for 96 different reports.  This adds up to 40 days of procesing time - not
exactly what my boss had in mind.  :-)

I am relatively new to VB and SQL, but I do understand RDO (I think).

To illustrate the example, a code sample is below:
SELECT DISTINCT r. FieldA
FROM    view1 r, view2 i
WHERE  r.FeildB = i.FieldX AND
             r.FieldA > 0 AND
              i.FieldY = ?  AND
              r.FieldC > '6/15/96'

An example of the parameter is 1234 (long integer).
Table1 (view1) has 700,000 records, Table2 (view2) has 50,000 records.
It does look as though SQL is using indexes on r.FieldA and i.FieldX.  It
then creates two temp. worktables.

I have tried changing to a subquery using EXISTS, but RDO never seems to
work for me with subqueries.  Additionally, EXISTS does not appear to be
faster under SQL tools.

The strangest part is that the time is so different between SQLtools and
VB-RDO.  Why is that?  And more importantly, how can I correct it?

All suggestions and inputs are welcome.

Thanks, in advance.

John Andre

What you may want to do is create this as a stored procedure within SQL
Server 6.0, accepting parameters.  It will run much faster that dynamically
executing the SQL statement.  I had a similar problem and converted the VB
code to a stored procedure and the data is returned within 10 to 15
seconds.

Hope this helps.

If those tables are in fact view, change the sql to go against that
tables. that will speed it up quite a bit.

indexing numbers and then testing for >0 is only effective if there are
a lot of negative numbers. Otherwise you don't want to touch the index.
i.e. if 50% of the number are > 0 then it needs to read 1/2 of the
records anyways which means its probably going to read all of the disk
pages so having to read the index is actually EXTRA work!!!!!

r.FeildB needs to be indexed for i.fieldx to be of value.

is r.FieldC > '6/15/96' distinctive? i.e. are there only a few records
that pass the test? if so index r.fieldc.

The point of indicies is that when you search for them you use them only
when the search will return a SMALL portion of the records. If your
table holds 20 records per disk page, having to read 1/20 of the records
will cause you to read most of the table!

exists clauses are really only effective when you need to see if
something is in a subtable and don't care what or how many. As a core
filter in such a direct join it gain much.

is i.FieldY always a single value? if so, and there are only a few
records that match that criteria, put an index on it and massage the SQL
for force the query to use that, and only that index.

Todd Verstraten

http://www.tier3.com