开发者

Optimize this query with multiple joins

I have a query that keeps timing out in SQL Server. The biggest table in the query only has a few hundred rows. Basically I'm trying to match up data in a bunch of tables with the novaPost field in tblNovaPosts to create a report. How should I change this query to make it run faster?

SELECT TOP (100) PERCENT tblNovaPosts.type, tblNovaPosts.novaPost,
ISNULL(SUM(tblAuditTrail.amount), 0) AS FUNDING,
ISNULL(SUM(tbl510.allot), 0) AS ALLOT,
ISNULL(SUM(tblStanfin.oblig), 0)
+ ISNULL(SUM(tblSof.obligationsCum), 0)
+ ISNULL(SUM(tblSpecAppt.obligations), 0) 
+ ISNULL(SU开发者_JAVA百科M(tblJlens.obligationsCum), 0)
- ISNULL(SUM(viewReimbObs.reimbObs), 0) AS OBLIGATED, 
ISNULL(SUM(tblSof.commitmentsNonCum), 0)
+ ISNULL(SUM(tblRmt.commitment),0)
+ ISNULL(SUM(tblReimb.commitmentsNonCum), 0)
- ISNULL(SUM(viewReimbObs.reimbObs), 0) AS DIRCOMMIT
FROM tblNovaPosts LEFT OUTER JOIN
tblAuditTrail ON tblNovaPosts.novaPost = tblAuditTrail.novaPost LEFT OUTER JOIN
tbl510 ON tblNovaPosts.novaPost = tbl510.novaPost LEFT OUTER JOIN
tblStanfin ON tblNovaPosts.novaPost = tblStanfin.novaPost LEFT OUTER JOIN
tblSof ON tblNovaPosts.novaPost = tblSof.novaPost LEFT OUTER JOIN
tblSpecAppt ON tblNovaPosts.novaPost = tblSpecAppt.novaPost LEFT OUTER JOIN
tblJlens ON tblNovaPosts.novaPost = tblJlens.novaPost LEFT OUTER JOIN
viewReimbObs ON tblNovaPosts.novaPost = viewReimbObs.novaPost1 LEFT OUTER JOIN
tblRmt on tblNovaPosts.novaPost = tblRmt.novaPost LEFT OUTER JOIN
tblReimb ON tblNovaPosts.novaPost = tblReimb.novaPost
GROUP BY tblNovaPosts.type, tblNovaPosts.novaPost
ORDER BY tblNovaPosts.type, tblNovaPosts.novaPost


View the query plan. Oftentimes SQL Server will give you an index recommendation.

Click this button:

Optimize this query with multiple joins


There is something suspicious about all the LEFT OUTER JOINs. Are you quite sure that is what you need?

Also, I notice you are outer joining to a table called tblAuditTrail. That seems likely to produce a cartesian product of 2 tables.

Perhaps you should test each join individually to check you are not actually requesting ten gzillion rows instead of just the few hundred you were expecting.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜