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.
| Author |
Topic |
|
avesh
Starting Member
3 Posts |
 Posted - 2013-03-25 : 10:05:12
|
| HiWhat 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 isselect 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_DATESimple enoughIf 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? |
 |
|
|
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. |
|
|
|
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. |
|
|
|
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 |
|
|
|
|
|
|
Querying million rows tables. Way too slow