slow query

Post Reply
tony.malandain
Posts: 57
Joined: Sat Mar 27, 2004 6:39 pm

slow query

Post by tony.malandain »

Hi Guys,

I'm trying to build a select query that makes a join between 2 tables.
the query runs but the performance is poor.
Can somebody tell me what I'm doing wrong?

table1: DocUrl Hash DocId + other fields
there is an index on DocUrl,Hash
table2: DocUrl Hash DocId +other fields
There is also an index on DocUrl,Hash.

Table1 has about 450 records while table2 have thousands.
my query is:
select t1.DocumentUrl as docUrl, t1.DocId as oldId, t2.DocId as newId from table1 as t1, table2 as t2 where t1.DocumentUrl = t2.DocumentUrl and t1.Hash = t2.Hash

Thanks.
User avatar
John
Site Admin
Posts: 2622
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

slow query

Post by John »

If you run with tsql and add -V you should see whether it is iterating over t1 or t2. If it is not doing t1 first then you can add a sql statement "set nooptimize='join'" to prevent it reordering the tables.
John Turnbull
Thunderstone Software
tony.malandain
Posts: 57
Joined: Sat Mar 27, 2004 6:39 pm

slow query

Post by tony.malandain »

what do you mean by iterating over t1?
Does that mean transforming the query into:
select ..... where t1.DocumentUrl = 'some Url' and t1.Hash = 'some Hash';?
If so, that's what it did (and setting nooptimize='join' behaved the same way). But that didn't change much.

The query still takes about 2.7s (for 450 records to identify) while inserting the records and building the index takes only 0.26s
User avatar
John
Site Admin
Posts: 2622
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

slow query

Post by John »

For each of the 450 records in t1 it will do a query against t2. Does it run faster if reran immediately? The 170 or so queries a second you are seeing is around the transaction rates for many disks if it is needing to hit the disk for the t2 queries.
John Turnbull
Thunderstone Software
tony.malandain
Posts: 57
Joined: Sat Mar 27, 2004 6:39 pm

slow query

Post by tony.malandain »

I have 2 SQL vortex commands one after each other.
They both take the same time.
The thing I don't understand is why it need to make 170 accesses to the disk.
I have indexes on both table, it should be able to load both indexes in memory and use them instead.
Any way I can force that?
User avatar
John
Site Admin
Posts: 2622
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

slow query

Post by John »

That's why I was asking if it was faster if the same query is ran again immediately after, as unless the system is low on memory compared to the memory needed for the query it should then have everything in memory and not need to hit the disk. If you are on unix and "time" the tsql command you can see the amount of time spent by Texis, by the OS, and how much is waiting for IO.
John Turnbull
Thunderstone Software
Post Reply