Query Optimization - Why does this speed up the Query?

Question

I'm using Quest's TOAD for SQL Server on a SQL Server 2000 Server.

Here is my query:

SELECT CASE SLCE.GroupName WHEN 'Other' THEN ARM.FBCOMPANY 
                           WHEN 'Inter Co.' THEN ARM.FBCOMPANY 
                           ELSE SLCE.GroupName END AS [Company Name], 
       ARM.fcustno AS [Cust No], 
       ARM.fbcompany AS [Cust Name], 
       ARM.fcinvoice AS [Invoice No], 
       ARM.fdgldate AS [Post Date], 
       year(arm.fdgldate) AS [Year Posted], 
       CASE ARM.fcsource WHEN 'S' THEN 'Shipper' 
                         WHEN 'O' THEN 'Sales Order' 
                         WHEN 'R' THEN 'Receiver' 
                         WHEN 'C' THEN 'Customer' 
                         ELSE ARM.fcsource END AS [Source Doc Type], 
       CASE ARM.fcstatus WHEN 'N' THEN 'New' 
                         WHEN 'U' THEN 'Unpaid' 
                         WHEN 'P' THEN 'Partially Paid' 
                         WHEN 'F' THEN 'Paid in Full' 
                         WHEN 'H' THEN 'Held' 
                         WHEN 'V' THEN 'Voided' 
                         ELSE ARM.fcstatus END AS [Invoice Status], 
       ARM.fpono AS [Cust PO No], 
       ARM.fsalespn AS [Sales Person], 
       ARI.fitem AS [Item No], 
       ARI.fprodcl AS [Prod Class], 
       ARI.fshipkey AS [Qty Invoiced], 
       ARI.ftotprice AS [Net Invoiced], 
       ARI.fpartno AS [Part No], 
       ARI.frev AS [Part Rev], 
       cast(ARI.fmdescript AS VARCHAR(20)) AS [Part Description], 
       ARM.fsono AS [Sales No], 
       ARI.fsokey AS [SO Rels Key], 
       ARI.fordqty AS [Qty Ordered], 
       RED.[YEAR] AS [Year], 
       RED.PERIOD AS [RF Period] 
  FROM dbo.armast ARM 
       INNER JOIN dbo.aritem ARI 
          ON ARM.FCINVOICE = ARI.FCINVOICE 
       INNER JOIN slcdpm SLC 
          ON SLC.fcustno = ARM.fcustno 
       LEFT OUTER JOIN slcdpm_ext SLCE 
         ON SLC.identity_column = SLCE.fkey_id 
       INNER JOIN REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED 
          ON RED.date = CAST (FLOOR (CAST (ARM.fdgldate AS FLOAT)) AS DATETIME) 
 WHERE ARM.fcstatus <> 'V' 
   AND RED.[YEAR] = year(getdate()) 
   AND ari.frev = 'REP' 
   AND ARI.fsalesacc IN ('4010001', '4010002', '4010003', '4010004', '4010005', '4010006', '4010007', '4010008', '4010009', '4010010', '4010018', '4010019', '4010020', '4010021', '4010031', '4010050', '4022000', '4031000', '4045000', '4055000', '4057000', '4060000', '4070000')

Here is TOAD's option (with differences highlighted) is:

INNER JOIN dbo.aritem ARI 
          ON ARM.FCINVOICE = ***COALESCE (ARI.FCINVOICE , ARI.FCINVOICE)*** 
       INNER JOIN slcdpm SLC 
          ON SLC.fcustno = ARM.fcustno 
       LEFT OUTER JOIN slcdpm_ext SLCE 
         ON SLC.identity_column = SLCE.fkey_id 
       INNER JOIN REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED 
          ON RED.date = CAST (FLOOR (CAST (ARM.fdgldate AS FLOAT)) AS DATETIME) 
 WHERE ARM.fcstatus <> 'V' 
   AND RED.[YEAR] = year(getdate()) 
   AND ari.frev = 'REP' 
   AND ARI.fsalesacc IN ('4010001', '4010002', '4010003', '4010004', '4010005', '4010006', '4010007', '4010008', '4010009', '4010010', '4010018', '4010019', '4010020', '4010021', '4010031', '4010050', '4022000', '4031000', '4045000', '4055000', '4057000', '4060000', '4070000') 
   ***AND ARI.[fpartno] >= CHAR(0)***

Can someone please tell me why that coalesce and additional and statement speed up this query by more than 50%?

Answer 1

Have you taken a look at the Actual Execution Plans. These should show you the different approaches that SQL Server took in executing these queries.

Answer 2

It's definitely an odd one. The execution plan should tell you for sure, but performance changes like this in databases almost always come down to an index. So my best guess is that somehow sql server was missing an index it could use and adding these odd changes made it stand out better.

But if you're looking to learn the 'why' involved so that next time you can write your query to be faster in the first place, there's really nothing there.

Answer 3

A complete WAG:

I am going to guess that fpartno has a "not null" condition on it (so the added filter always passes), and that Toad happens to know that SQLServer is not smart enough to detect that >=CHAR(0) is always true. So this suggests that Toad is trying to guide, in a very oblique way, the optimizer to use something that has fpartno in it. That something could be a composite index on (fcinvoice, fpartno).. do you have one of those?

Like the others said, the explain plan should prove helpful in explaining the mystery.

Answer 4

Neither of these changes have any effect on the logical meaning of the query (i.e., they are "no ops")

The only physical effect that the COALESCE on the ON clause would be to prevent the optimizer from trying to use an index for ARI.FCINVOICE.

Likewise, the only physical effect that the ">= CHAR(0)" could have on the optimizer might be to prod it to consider using a indexed range scan (or also a seek) on an index that had ARI.[fpartno] in it.

So my conclusion would be that TOAD is trying to manipulate the optimizer into using a particular index without actually forcing it to use that index with an explicit HINT. Actually, that's kind of clever as the real problem with the T-SQL optimizer hints is their dependencies and fragility.