请大家帮忙sql语句调优 谢谢

[flybird]

INSERT INTO temp1000all
SELECT
ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcBalance),0)
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcavgbalance1),0)
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance1),0) as cshqsdjnc
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance2),0) as cshqsdjjm
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance3),0) as cshqsdjym
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance1),0) as cshqrjjnc
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance2),0) as cshqrjjjm
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance3),0) as cshqrjjym
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,am.name,amg.name,am.id,amg.id
FROM Rate ra
LEFT JOIN Am_Amg_Rel aar ON aar.id = ra.am_amg_id
LEFT JOIN AccountFull af ON af.caccountNo = ra.accountNo
LEFT JOIN temp1000Rebate tempRe ON tempRe.accountNo = af.caccountNo
LEFT JOIN AccountManager am ON am.id = aar.amid
LEFT JOIN AccountManagerGroup amg ON amg.id = aar.amgid
WHERE af.cDate = '20090621'
AND af.ncustomerid <> 0
AND af.ncustomerid IS NOT NULL
AND af.citemcode IN(20202,21101,21102,21103,21104,21702)
GROUP BY am.id, amg.id, am.name, amg.name ,af.citemcode

[wanglei8]

先尽量过滤掉数据，然后再join

[阿三]

看看执行计划，在相关的表上建立合适的索引

af.cDate = '20090621'
AND af.ncustomerid <> 0
AND af.ncustomerid IS NOT NULL
AND af.citemcode IN(20202,21101,21102,21103,21104,21702)

这些条件从af中查是多少计划，能不能把它做成一个子查询,然后再与ra左联接

[flybird]

恩
可以把 AccountFull af 做成一个子查询，然后做连接，应该会快一下


不过现在的瓶颈还是在多个左连接处

能不能有什么办法尽量减少左连接的数量
或者用内连接来做

谢谢

[quiettown]

[Quote=引用 1 楼 zxf_feng 的回复:]
看看执行计划，在相关的表上建立合适的索引

af.cDate = '20090621'
AND af.ncustomerid <> 0
AND af.ncustomerid IS NOT NULL
AND af.citemcode IN(20202,21101,21102,21103,21104,21702)

这些条件从af中查是多少计划，能不能把它做成一个子查询,然后再与ra左联接
[/Quote]
UP，感觉AF应该是数据量最大的基表，这些过滤后，应该数据量会大幅减少。另外ncustomerid条件可不可以写成>0合并两个条件，使该字段上索引可用？