asked
Joining a master table took a long time to execute
Having a transaction header table, transaction details table and a master table. In details table there are multiple records for a header record. And each detail row refers a record from master table. Query 1 SELECT 1 FROM HeaderTable INNER JOIN DetailTable ON HeaderTable.HeaderID = DetailTable.HeaderID This will return around 4000 records in 40 ms. Query 2 SELECT 1 FROM DetailTable INNER JOIN MasterTable ON DetailTable.MasterRefID = MasterTable.MasterID This will return around 4000 records in 16 ms. Query 3 SELECT 1 FROM HeaderTable INNER JOIN DetailTable ON HeaderTable.HeaderID = DetailTable.HeaderID INNER JOIN MasterTable ON DetailTable.MasterRefID = MasterTable.MasterID This will return around 4000 records in 1400 ms. Why this last query took long time to execute? Note: All the tables has required indices also.
So the common table between the first 2 queries is DetailTable. In each of the first 2 queries, there may be a different index being used to access DetailTable. In the third query, only one of these will be chosen initially, and whilst it may be a good index for one of the joins, may not be a good index for the other join, resulting in lookups. So maybe you should look at the execution plan to see if there is a different indexing strategy to employ.
Yeah, really hard to say without seeing the full structure as well as the data distribution, or, the execution plan.
The same index? In all 3 plans? Could you post the 3 execution plans as xml files here.
The same indexes cannot be used the same way in all three queries. All three queries will be different because they all refer to different tables and have different join criteria. You're seriously missing a level of understanding on what the execution plans are telling you. If you can't post the .sqlplan files for us to look at, then I'd suggest you at least get a [copy of this book][1] on execution plans and read it. [1]:
https://www.simple-talk.com/books/sql-books/sql-server-execution-plans,-second-edition,-by-grant-fritchey/
Why are you surprised it took longer to process if the last query uses 3 tables and the others just 2?. The execution plans can absolutely not be equal simply because of this fact. The answers to your question lies in the execution plan for the third query. So, look for clues there. Also, I suggest you start with the biggest table in the FROM clause.
16 People are following this question.
