SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Querying million rows tables. Way too slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

avesh
Starting Member

USA
3 Posts

Posted - 03/25/2013 :  10:05:12  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 03/25/2013 :  10:22:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
3 Posts

Posted - 03/25/2013 :  11:05:44  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 03/25/2013 :  13:28:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
3 Posts

Posted - 03/26/2013 :  14:07:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000