|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?站点注册
×
各位大侠:
能否帮小弟看一下以下的sql有哪些地方可以优化的?并且请告诉一下优化的原则.谢谢!其中tblWithholdApps的数据量已经达到600万,其他两张表的数据量在200左右。索引有:tab1.TransDate,tab1.OperationCode, tab2.OperationCode
(1)SELECT tab2.ProjectCode,tab1.OperationCode,tab2.OperationName,tab3.ProjectName,tab1.CashFlag,count(*) AS counts,SUM(tab1.TotalAmount) AS totalAmount FROM tblWithholdApps AS tab1,tblOperationInfo AS tab2,tblProjectInfo AS tab3
WHERE tab1.TransDate>=' ' AND tab1.TransDate<=' ' AND tab2.RecPayFlag=tab3.RecPayFlag AND tab1.OperationCode = tab2.OperationCode
AND tab2.ProjectCode = tab3.ProjectCode AND tab1.SerialType='1' AND tab1.Status='0'
GROUP BY tab2.ProjectCode,tab1.OperationCode,tab2.OperationName,tab3.ProjectName,tab1.CashFlag
(2)SELECT tab2.BranchCode,tab1.OperationCode,tab2.OperationName,tab3.BankChName,tab1.CashFlag,count(*) AS counts,SUM(tab1.TotalAmount) AS totalAmount FROM tblWithholdApps AS tab1,tblOperationInfo AS tab2,tblBankInfo AS tab3
WHERE tab1.TransDate>='' AND tab1.TransDate<='' AND tab1.OperationCode = tab2.OperationCode
AND tab1.SerialType='1' AND tab1.Status='0' AND tab2.BranchCode=tab3.BankCode AND tab2.BranchCode IN(数据量在100左右)
GROUP BY tab2.BranchCode,tab1.OperationCode,tab2.OperationName,tab3.BankChName,tab1.CashFlag
另外:如果哪位大侠有sybase数据库sql优化文档的能否发给小弟一份。谢谢!
[ 本帖最后由 johnsonboby 于 2008-7-23 17:06 编辑 ] |
|