August 8, 2008 at 4:35 am
Hi,
I have been working on optimising 1 of our search SP's over the last few days and although I have managed a decent improvement, there is still an area of where it is taking too long to execute.
It looks as though it is when the tables are joined together to bring back the full data set. The way it works is that I have a temp table that I populate just the transaction IDs that hit the search results. This bit is pretty quick (I thought this would be the slow bit).
It then joins the temp table onto the live tables which contain the rest of the data, which looks a bit like:
SELECT Column1, Column2, Column3, Column4
FROM tmpTable t
INNER JOIN customer c ON c.TransactionID = t.transactionID
INNER JOIN header h ON h.TransactionID = c.transactionID
INNER JOIN address a ON a.TransactionID = h.transactionID
INNER JOIN misc m ON m.TransactionID = a.transactionID
The transaction id is a clustered index on all live tables (which contain about 30million rows each) and the indexes are rebuilt twice a week. The temporary table is also indexed in the transaction id. I thought that that, despite the number of rows in the tables that as the joins are on the transaction id, it should still be the quick part.
Its worth noting that the joins to the live table get slower with the number on transactions returned.
There's probably something I'm missing, I don't know if the way the inner joins are laid out is causing the issue. Each live table has 1 row per transaction, so I don't think changing the order the tables are joined will make any difference?
August 8, 2008 at 6:54 am
The order which you specify the tables has no effect. The optimiser will join the tables in the order it considers most optimal.
Can you post the definition of the tables, the definitions of any indexes on those tables and the execution plan please. To save the execution plan, right click on the plan, choose save as and save it as a .sqlplan file. Then zip it and attach to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2008 at 7:02 am
can you try using table level lock hint? e.g. with (nolock), i found some times non-ignorable outcomes by using this hint.
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
August 8, 2008 at 7:13 am
Muhammad Tariq (8/8/2008)
can you try using table level lock hint? e.g. with (nolock), i found some times non-ignorable outcomes by using this hint.
Ow. Table and locking hints should be the last resort for fixing an issue and only applied carefully and with full understanding of the effects.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2008 at 7:26 am
I agree - avoid using hints - they are a last resort. NOLOCK, in particular, is a commonly misused and misunderstood hint and should rarely be used.
I did notice in your query you are joining each subsequent table rather than always joining to t.transactionID. They may cause the optimizer to nest loops because you are likely to be getting loop joins because of the sizes of the tables. You may want to try this:
[font="Courier New"]SELECT Column1, Column2, Column3, Column4
FROM tmpTable t
INNER JOIN customer c ON c.TransactionID = t.transactionID
INNER JOIN header h ON h.TransactionID = t.transactionID
INNER JOIN address a ON a.TransactionID = t.transactionID
INNER JOIN misc m ON m.TransactionID = t.transactionID[/font]
And like Gail said, post the execution plan and as much DDL as you can.
August 8, 2008 at 8:57 am
An execution plan would sure make a difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply