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 |
|
bcanonica
Starting Member
35 Posts |
 Posted - 2009-03-23 : 13:30:05
|
| I have a query that joins to 16 tables. 14 left joins and 2 inner joins. It takes over 15 minutes to run making basically useless in a web application. I was wondering what you pros do to improve the performance of a complex query like this. What steps do you take and in what order? I have some ideas, but I would love to hear your guys thought process. |
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-03-24 : 05:15:11
|
| 1st look at the execution plan to see which parts of the query make it run slow. Looking into indexes on tables that you are joining should also help. And try to cut down on the joins maybe if you rethink your table structure you'll be able to make this query in 5-6 joins. |
 |
|
|
heavymind
Posting Yak Master
115 Posts |
Posted - 2009-03-24 : 05:22:00
|
| there are some best practices for query writing and optimization of existing queries using them you can optimize such queries without need to see the actual execution plan.The main rule for joins - joining fields must be indexed.Thanks, VadymMCITP DBA 2005/2008Chief DBA at http://www.db-staff.com |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-03-24 : 10:43:10
|
| asgast problem is this is an existing table structure that I inherited. That is all great stuff any resources or articles on the steps to take or any other ideas out there. |
|
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-03-24 : 11:50:42
|
| I have to find a good website on this topic, sorry. Show us our code, may be we will spot some problems. Check execution plan, you can find tips on how to use it in google.I would start with execution plan and indexes.There is one solution, not the best. I would advise you to try other methods first. Its really simple, but if you stick to it all the time, in half a year you'll have hell in our DB.Just create a table that will hold all the data you get with your select and, populate it, later filter it to show the needed rows. It will run faster than our 16 join query. There times when this is the only solution. |
|
|
|
|
|
|
Query performance