Certain Queries Taking An Age in VB6, SQL Server using RDO 
Author Message
 Certain Queries Taking An Age in VB6, SQL Server using RDO

Hi There,

I'm running queries on a SQL Server (tried both 6.5 and 7) from VB6 using
RDO,
and certain queries seem to take a long time to execute while others are
fine.
I've checked all indexes on the tables in question, and in fact, adding some
indexes caused it to go slower.  Something which occurs in every statement
which goes slow is the 'exists' statement, rewriting the statement to not
use
exists makes it run much faster (though the development time to do this for
our existing search screen would be huge).  Running the same query through
an ISQL session goes fine.

I have put in debug statements to time execution of queries.  Here are some
results:

21.78 Secs:
Select * from Organisations where   exists (select * from Contacts where
( cont_1stname like 'John%') and ( cont_lastname like 'Smith%') and
Contacts.org_key = Organisations.org_key)    order by org_name

0.38 Secs:
Select * from  Site where   exists (select * from Contacts where
( cont_1stname like 'John%') and ( cont_lastname like 'Smith%') and
Contacts.site_key = Site.site_key) and Site.org_key = 1673
order by site_postcode

Most of the queries, including some very large ones run even quicker than
the second one I have posted here.  I've just thought maybe that caching
is responsible for the second query here going quicker?

Anyway, any thoughts on the matter gratefully recieved.

Cheers,
Steve Dark



Sat, 19 May 2001 03:00:00 GMT  
 Certain Queries Taking An Age in VB6, SQL Server using RDO
Steve,

I'm no expert in this area but I think refining your SQL might help.  Take
your second example.  I think what the DB engine will do is select all the
sites with an org_key = 1673 (probably a small number of records which it
will cache in memory).  It will then process the subquery.

In your first example it needs to bring the whole Organisations table into
memory (perhaps too big to be happily cached) and then run the subquery.

As an untested example, I would try a small rebracketing:

SELECT * FROM Organisations, Contacts WHERE

    EXISTS (SELECT * FROM Contacts WHERE Cont_1stname LIKE 'John%' AND
                    cont_lastname LIKE 'Smith%')

AND Contacts.org_key = Organisations.org_key

ORDER BY org_name

The idea (as I say untested) is to try to cut down the number of
organisations that need to be cached for the sub-query to work.  I have a
vague recollection that (generally) SQL evaluates right-to-left.

Just a thought,

Peter

Quote:

>Hi There,

>I'm running queries on a SQL Server (tried both 6.5 and 7) from VB6 using
>RDO,
>and certain queries seem to take a long time to execute while others are
>fine.
>I've checked all indexes on the tables in question, and in fact, adding
some
>indexes caused it to go slower.  Something which occurs in every statement
>which goes slow is the 'exists' statement, rewriting the statement to not
>use
>exists makes it run much faster (though the development time to do this for
>our existing search screen would be huge).  Running the same query through
>an ISQL session goes fine.

>I have put in debug statements to time execution of queries.  Here are some
>results:

>21.78 Secs:
>Select * from Organisations where   exists (select * from Contacts where
>( cont_1stname like 'John%') and ( cont_lastname like 'Smith%') and
>Contacts.org_key = Organisations.org_key)    order by org_name

>0.38 Secs:
>Select * from  Site where   exists (select * from Contacts where
>( cont_1stname like 'John%') and ( cont_lastname like 'Smith%') and
>Contacts.site_key = Site.site_key) and Site.org_key = 1673
>order by site_postcode

>Most of the queries, including some very large ones run even quicker than
>the second one I have posted here.  I've just thought maybe that caching
>is responsible for the second query here going quicker?

>Anyway, any thoughts on the matter gratefully recieved.

>Cheers,
>Steve Dark




Sun, 20 May 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. VB6 application using SQL Server 2000 migrating db to SQL Server 2

2. Using RDO to do SQL updates on SQL Server 6.0

3. rdo sql server 2000 vb6

4. How to execute SQL that queries multiple databases using rdo

5. HOW TO RETREIVE DATA FROM SQL SERVER USING RDO

6. 2nd SQL Server update after update text field corrupts row - using RDO

7. Multiple Resultsets using SQL Server and RDO?

8. Pessimistic Locking Using RDO & SQL Server

9. Accesssing SQL Server Text data types using RDO

10. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

11. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

 

 
Powered by phpBB® Forum Software