Tuning Question - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tuning Question

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-07-30 : 09:20:45

I am on SQL 2000, and I use TOAD for SQL Server. I wrote a fairly long and complex SQL statement and then had TOAD optimize it. The plan cost for mine is 96.96 and TOAD recommended a script that only costs 1.09.

However, the changes between the 2 are weird. Please look at the following where I have highlighted the differences.

Mine:

SELECT Inmast.fpartno AS [Part No], 
       Inmast.fdescript AS [Description], 
       Inmast.frev AS [Rev], 
       Inonhd.fbinno AS [Bin No], 
       Inonhd.flocation AS [Location], 
       Inonhd.flot AS [Lot], 
       (SELECT TOP 1 (q.fcdoc) --, J.fsono 
          FROM M2MDATA01.dbo.qalotc q 
               INNER JOIN M2MDATA01.dbo.jomast J 
                  ON J.fjobno = q.fcdoc  
           WHERE q.fcpartrev = 'REP' 
           AND q.fctype = 'J' 
           AND inonhd.flot = q.fclot 
           AND inonhd.fpartno = q.fcpartno 
         ORDER BY q.fddate ASC) AS [Job Num], 
       (SELECT TOP 1 (J.fsono) 
          FROM M2MDATA01.dbo.qalotc q 
               INNER JOIN M2MDATA01.dbo.jomast J 
                  ON J.fjobno = q.fcdoc 
         WHERE q.fcpartrev = 'REP' 
           AND q.fctype = 'J' 
           AND inonhd.flot = q.fclot 
           AND inonhd.fpartno = q.fcpartno 
         ORDER BY q.fddate ASC) AS [Sales No], 
       syc.[SYC Sales No] AS [SYC Sales No], 
       syc.[Inq No] AS [Inq No], 
       syc.[Cust Po No] AS [Cust Po No], 
       syc.[Next Action] AS [Next Action], 
       syc.[Repair Type] AS [Repair Type], 
       syc.[Problem] AS [Problem] 
  FROM m2mdata01.dbo.inmast inmast 
       JOIN dbo.inonhd inonhd 
         ON Inonhd.fac = Inmast.fac 
            AND Inonhd.fpartno = inmast.fpartno 
            AND Inonhd.fpartrev = Inmast.frev 
       LEFT OUTER JOIN dbo.InOnHdDl 
         ON InOnHdDl.fiInOnHdID = InOnHd.identity_column 
       LEFT JOIN (SELECT sycslm.fcinqno AS [Inq No], 
                         sycslm.fcpartno, 
                         sycslm.fcsono AS [SYC Sales No], 
                         sycslm_ext.LOTSERIAL AS [LOTSERIAL], 
                         sycslm.FCCUSTPO AS [Cust Po No], 
                         sycslm.fminquiry AS [Problem], 
                         sycslm_ext.NEXTACTION AS [Next Action], 
                         sycslm_ext.REPAIRTYPE AS [Repair Type] 
                    FROM M2MDATA01..SYCSLM_EXT sycslm_ext 
                         INNER JOIN m2mdata01.dbo.sycslm sycslm 
                            ON sycslm.[identity_column] = sycslm_ext.[FKey_ID] 
                   WHERE sycslm.fcstatus = 'OPEN') SYC 
         ON ltrim (rtrim (Inonhd.flot)) = ltrim (rtrim (syc.LOTSERIAL)) 
            AND ltrim (rtrim (syc.fcpartno)) = ltrim (rtrim (inonhd.fpartno)) 
 WHERE Inmast.fsource NOT IN ('P', 'F') 
   AND inonhd.fpartrev = 'REP' 
   AND inonhd.flocation = 'FR' 
 ORDER BY inonhd.flot

TOAD:

SELECT Inmast.fpartno AS [Part No], 
       Inmast.fdescript AS [Description], 
       Inmast.frev AS [Rev], 
       Inonhd.fbinno AS [Bin No], 
       Inonhd.flocation AS [Location], 
       Inonhd.flot AS [Lot], 
       (SELECT TOP 1 q.fcdoc --, J.fsono 
          FROM M2MDATA01.dbo.qalotc q, 
               M2MDATA01.dbo.jomast J 
         WHERE q.fcpartrev = 'REP' 
           AND q.fctype = 'J' 
           AND inonhd.flot = q.fclot 
           AND inonhd.fpartno = q.fcpartno 
           AND J.fjobno = q.fcdoc 
         ORDER BY q.fddate ASC) AS [Job Num], 
       (SELECT TOP 1 J.fsono 
          FROM M2MDATA01.dbo.qalotc q, 
               M2MDATA01.dbo.jomast J 
         WHERE q.fcpartrev = 'REP' 
           AND q.fctype = 'J' 
           AND inonhd.flot = q.fclot 
           AND inonhd.fpartno = q.fcpartno 
           AND J.fjobno = q.fcdoc 
         ORDER BY q.fddate ASC) AS [Sales No], 
       syc.[SYC Sales No] AS [SYC Sales No], 
       syc.[Inq No] AS [Inq No], 
       syc.[Cust Po No] AS [Cust Po No], 
       syc.[Next Action] AS [Next Action], 
       syc.[Repair Type] AS [Repair Type], 
       syc.[Problem] AS [Problem] 
  FROM m2mdata01.dbo.inmast inmast 
       JOIN dbo.inonhd inonhd 
         ON Inonhd.fac = Inmast.fac 
            AND Inonhd.fpartno = inmast.fpartno 
            AND Inonhd.fpartrev >= Inmast.frev 
            AND Inonhd.fpartrev <= Inmast.frev 
       LEFT OUTER JOIN dbo.InOnHdDl 
         ON InOnHdDl.fiInOnHdID = InOnHd.identity_column 
       LEFT JOIN (SELECT sycslm.fcinqno AS [Inq No], 
                         sycslm.fcpartno, 
                         sycslm.fcsono AS [SYC Sales No], 
                         sycslm_ext.LOTSERIAL AS [LOTSERIAL], 
                         sycslm.FCCUSTPO AS [Cust Po No], 
                         sycslm.fminquiry AS [Problem], 
                         sycslm_ext.NEXTACTION AS [Next Action], 
                         sycslm_ext.REPAIRTYPE AS [Repair Type] 
                    FROM m2mdata01.dbo.sycslm sycslm 
                         INNER JOIN M2MDATA01..SYCSLM_EXT sycslm_ext 
                            ON sycslm.[identity_column] = COALESCE (sycslm_ext.[FKey_ID] , sycslm_ext.[FKey_ID]) 
                   WHERE sycslm.fcstatus = 'OPEN') SYC 
         ON ltrim (rtrim (Inonhd.flot)) = ltrim (rtrim (syc.LOTSERIAL)) 
            AND ltrim (rtrim (syc.fcpartno)) = ltrim (rtrim (inonhd.fpartno)) 
 WHERE Inmast.fsource NOT IN ('P', 'F') 
   AND inonhd.fpartrev = 'REP' 
   AND inonhd.flocation = 'FR' 
   AND inonhd.[fpartno] >= CHAR(0) 
 ORDER BY inonhd.flot

Anyone have any idea why these changes dramatically speed up the query? I am trying to learn to write better code.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-30 : 11:22:07

What metric are you using for "Cost?" Did you try running them in different order to see if the cost goes down after data gets into the cache? Also, do they return the same results?

Just comparing:
AND Inonhd.fpartrev = Inmast.frev

to

AND Inonhd.fpartrev >= Inmast.frev
AND Inonhd.fpartrev <= Inmast.frev

Seems very odd since the toad version is bascially matching everything...

Any chance you can show us the query plan (preferably in XML) for each query?

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 11:49:19

What's the difference betweem


       , (	  SELECT TOP 1 (q.fcdoc) --, J.fsono 
				FROM M2MDATA01.dbo.qalotc q 
          INNER JOIN M2MDATA01.dbo.jomast J 
                  ON J.fjobno			= q.fcdoc  
			   WHERE q.fcpartrev		= 'REP' 
				 AND q.fctype			= 'J' 
				 AND inonhd.flot		= q.fclot 
				 AND inonhd.fpartno		= q.fcpartno 
			ORDER BY q.fddate ASC)					AS [Job Num] 			-- Derived Table
       , (	  SELECT TOP 1 (J.fsono) 
				FROM M2MDATA01.dbo.qalotc q 
          INNER JOIN M2MDATA01.dbo.jomast J 
                  ON J.fjobno			= q.fcdoc 
			   WHERE q.fcpartrev		= 'REP' 
				 AND q.fctype			= 'J' 
				 AND inonhd.flot		= q.fclot 
				 AND inonhd.fpartno		= q.fcpartno 
            ORDER BY q.fddate ASC)					AS [Sales No]			-- Derived Table

And



       , (	  SELECT TOP 1 (q.fcdoc), J.fsono 
				FROM M2MDATA01.dbo.qalotc q 
          INNER JOIN M2MDATA01.dbo.jomast J 
                  ON J.fjobno			= q.fcdoc  
			   WHERE q.fcpartrev		= 'REP' 
				 AND q.fctype			= 'J' 
				 AND inonhd.flot		= q.fclot 
				 AND inonhd.fpartno		= q.fcpartno 
			ORDER BY q.fddate ASC)					AS [Job Num] 			-- Derived Table

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources