sql server data mining: I have a query which used 2 tables in the from clause and used the where clause to get the result like so
Select tb1.*, tb2.
from tb1, tb
where tb1.id = tb2.i
and tb1.data_dt = '20031231

This query for these tables where are large, about 2 million rows a piece, returned the correct result, but took almost 20 minutes. I checked the execution plan it it did make use of the indexes on both which is the column ID and is a clustered index. Where as if I write the query like so

Select tb1.*, tb2.
from tb1 inner join tb2 on tb1.id = tb2.i
where tb1.data_dt = '20031231

this runs in about a minute. This is very confusing. We do this in the sybase world and have no difference in performance, is there some way that MSSQL treats the first syntax that will cause it to run so long? Thank

Dav

Dave,

Did you clear out the server's cache before running the second query? Those
two queries should certainly be identical in terms of performance and
execution plan. Try again, but this time run the following to clear the
cache and get real results before testing each query:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE



[quoted text, click to view]
returned the correct result, but took almost 20 minutes. I checked the
execution plan it it did make use of the indexes on both which is the column
ID and is a clustered index. Where as if I write the query like so:
[quoted text, click to view]
sybase world and have no difference in performance, is there some way that
MSSQL treats the first syntax that will cause it to run so long? Thanks
[quoted text, click to view]

Not one convert... Two million of them! (one for every row in your table)

[quoted text, click to view]
converting the date column to match an input variable and not the other way
around. When I rewrote the query as an inner join I did this automatically
when I wrote it, not paying attention to the way the original query did it.
It is amazing how much time a convert added though. Thanks