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 |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2005-02-24 : 09:46:47
|
| Hi,Do you know how this query can be optimized apart from indexing the fields?I am thinking about changing the where clause somehow.Any thoughts please?ThanksSELECT tbF.FileID, Action_Requirement_Task= tbAC.Action,tbF.ActionBy, tbSS.servername, tbSS.servershare, tbF.Path, tbF.Owner, tbVC.ValueCentre, tbSS.country, tbF.Size, tbF.LastAccessed, tbF.LastModified, tbF.Imported, tbF.Extension FROM dbo.tblFile tbF LEFT JOIN dbo.tblFileAction tbAC ON tbF.ActionID = tbAC.ActionID INNER JOIN dbo.tblservershares tbSS ON tbF.servershareid = tbSS.servershareid LEFT JOIN dbo.tblUser tbU ON tbF.Owner = tbU.UserID LEFT JOIN dbo.tblValueCentre tbVC ON tbU.ValueCentreid = tbVC.ValueCentreid where tbF.fileid in (select distinct fileid from tblfilescanstring where fileid in (select fileid from tblfilescan where (tblFilescan.DCTConversionReqd <> 0 or tblFilescan.DCTConversionReqd is null) and (tblFileScan.Excluded <> 1 or tblFileScan.Excluded is null))) And tbF.FileID in (select distinct fileid from tblfileactivity where dbo.fn_GetLastActionID (fileid) in (100,205,207,210,230,300,500,600)) And tbSS.Country = @Country |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-02-24 : 12:18:19
|
| do you have an execution plan that you can post?-ec |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-24 : 13:25:09
|
| Couple of things:-Change the IN() conditions to a JOIN, or use EXISTS()-Avoid using DISTINCT-Avoid using UDF's, especially if they access tables, and also if they are called for each row of the queryYou have 3 embedded sub-queries, one of which calls a UDF on each row. It's not likely to perform any better with this syntax.You also should post the DDL for all of the tables involved in this query. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-02-24 : 14:52:58
|
| Can not avoid using distinct because of duplicates.I have tried doing the things you suggested but can not.Could you kindly adgust the sql for me please?Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-24 : 19:07:34
|
| I can't adjust the SQL without doing the things I recommended, which you claim cannot be done.And you CAN avoid DISTINCT:...where EXISTS (select * from tblfilescanstring ss inner join tblfilescan s on ss.fileid=s.fileid inner join tblfileactivity a on ss.fileid=a.fileidwhere isnull(tblFilescan.DCTConversionReqd,0)<>0 and IsNull(tblFileScan.Excluded,1) <> 1and ss.fileid=tbF.FileIDAnd dbo.fn_GetLastActionID (ss.fileid) in (100,205,207,210,230,300,500,600))And tbSS.Country = @Country |
|
|
|
|
|
|
|
|
sql optimization