Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Querying million rows tables. Way too slow

Author  Topic 

avesh
Starting Member

3 Posts

Posted - 2013-03-25 : 10:05:12
Hi
What would you do if you had to run a SQL againts three tables to find rows that should be updated? All three tables have about 4-5 million rows each. The simplified SQL is

select COUNT(1)
FROM TABLE1 T1,
TABLE2 T2,
TABLE3 T3

where T2.TRANSACTION_ID=T1.TRANSACTION_ID
and T2.TRANSACTION_ID=T3.TRANSACTION_ID
and T1.STATUS_FLAG in ('I','C')
and T2.LAST_UPDATE_DATE<>T3.LAST_UPDATE_DATE


Simple enough

If I exclude the last line it returns a result in about 25 seconds which I could live with since it is a part of an etl job. If I put it back then it runs for many many minutes.

What can I do to speed it up?
Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-25 : 10:22:47
Sounds like you could use an index on LAST_UPDATE_DATE on both Table2 and Table3. Did you look at the execution plan for the query (both with and without the last line)? Did you see any table or clustered index scans?
Go to Top of Page

avesh
Starting Member

3 Posts

Posted - 2013-03-25 : 11:05:44
This is what the tuning advisor said too with estimated improvement of only 11%. I was trying to avoid creating any more indexes I guess. I built those eventually and the SQL is much faster now. Will see how those new indexes will slow down something else. The execution plan was showing and is showing one instance of the non-clustered index. Now it uses the new index I just built (cost 36%). With a statement like 'date1 <> date2' it will always scan and not seek, right? Thanks for assistance btw.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-25 : 13:28:24
It depends on how many other rows are returned via the other conditions. If you only have a few hundred rows where STATUS_FLAG IN('I','C') then it may use the index and do a bookmark lookup. Is there an index on that column too?

Make sure your statistics are also up-to-date.
Go to Top of Page

avesh
Starting Member

3 Posts

Posted - 2013-03-26 : 14:07:20
Yeah I updated statistics. The index on STATUS_FLAG would not help since only few records there have something else but "I" or "C". I have a filtered index to find "D" rows though. Thanks for you help
Go to Top of Page
   

- Advertisement -