SQL语句优化-----请大哥大姐们帮忙？

[jxhdy2001]

/*GetData: SQL:*/SELECT FI.FID, OI.FValue AS FFuncUrl, ('Org:'+OS.FSpace + OS.FID + OS.FKind) AS FOrgUrl, ('Biz:'+FS.FSpace + FS.FID+FS.FKind) AS FProcUrl, 'Biz:'+FS2.FPath + FS2.FID + FS2.FKind AS FFuncFileName, FS2.FDisplayName AS FFuncDisplayName, FS2.FParam AS FParam, FS2.FOrder AS FFuncOrder FROM [dbo].S_OrgSystem OS, [dbo].S_OrgIndex as OI LEFT JOIN [dbo].S_FileIndex AS FI ON (FI.FID IN ('Proc.Entry.FuncRef', 'Proc.Activity.FuncRef') AND FI.FValue = OI.FValue) LEFT JOIN [dbo].S_FileSystem AS FS ON (FS.FGUID = FI.FSysGUID) AND (FS.FInvalid = 0) LEFT JOIN [dbo].S_FileSystem AS FS2 ON ('Biz:'+ FS2.FSpace + FS2.FID + FS2.FKind) = OI.FVALUE AND (FS2.FInvalid = 0) WHERE OI.FID = 'FuncAlloc.Run' AND OI.FSysGUID IN (SELECT DISTINCT OSB.FGUID FROM [dbo].[S_OrgSystem] OSA, [dbo].[S_OrgSystem] OSB WHERE ((OSA.FDept + '&' + OSA.FPosition + '&' + OSA.FPerson) IN ('AJ.02&YW&00019')) AND (((OSA.FSpace + OSA.FID + OSA.FKind) = (OSB.FSpace + OSB.FID+OSB.FKind)) OR (OSA.FSpace + OSA.FID + OSA.FKind) LIKE (OSB.FSpace + OSB.FID + OSB.FKind + '\%')) UNION SELECT FGUID FROM [dbo].[S_OrgSystem] WHERE FID IN ('YW') AND FKind = '.PTN') AND OI.FSysGUID = OS.FGUID

这条SQL语句执行太慢了，每次都要七八秒，请问如何优化？

[no_mIss]

这么多left join 这么多in ,这么多拼串,还有like ,distinct

我觉得难优化..

[jxhdy2001]

自己顶一下

[w75251455]

汗

[jxhdy2001]

我自己改了一下：

SELECT P.FID, OI.FFuncURL, ('Org:'+FO.FSpace + FO.FFileName) AS FOrgUrl, P.FProcUrl, 'Biz:'+FS.FPath + FS.FFileName AS FFuncFileName, FS.FDisplayName AS FFuncDisplayName, FS.FParam, FS.FOrder FROM (SELECT FSysGUID, FValue as FFuncURL FROM S_OrgIndex WHERE FID='FuncAlloc.Run' AND FSysGUID IN(SELECT DISTINCT OSB.FGUID FROM [dbo].[S_OrgSystem] OSA, [dbo].[S_OrgSystem] OSB WHERE ((OSA.FDept + '&' + OSA.FPosition + '&' + OSA.FPerson) IN ('AF.09&YW&01002')) AND (((OSA.FSpace + OSA.FID + OSA.FKind) = (OSB.FSpace + OSB.FID+OSB.FKind)) OR (OSA.FSpace + OSA.FID + OSA.FKind) LIKE (OSB.FSpace + OSB.FID + OSB.FKind + '\%')) UNION SELECT FGUID FROM [dbo].[S_OrgSystem] WHERE FID IN ('YW') AND FKind = '.PTN')) OI LEFT JOIN S_FileSystem AS FS ON ('Biz:'+FS.FSpace+FS.FFileName = OI.FFuncURL) AND (FS.FInvalid = 0) LEFT JOIN S_OrgSystem AS FO ON FO.FGUID=OI.FSysGUID AND (FO.FInvalid=0) LEFT JOIN (SELECT FI.FID, FI.FValue, ('Biz:'+SF.FSpace + SF.FFileName) AS FProcUrl FROM S_FileIndex FI LEFT JOIN S_FileSystem SF ON SF.FGUID=FI.FSysGUID WHERE FI.FID IN ('Proc.Entry.FuncRef', 'Proc.Activity.FuncRef')) P ON OI.FFuncURL=P.FValue

请问二者的性能有区别吗？

[CathySun118]

where相当于inner jion，用where替换left jion查询结果会不一样