3

I have two UPDATE queries that are similar in structure, yet the SQL Server query plan for one shows indexes being used, and for the other it shows only a regular table scan.

The following are the queries (as per the query plan, #1 does not use indexes, #2 does)- 

 UPDATE Payment_Metadata 
    SET 
    Payment_Metadata.CommodityCode = 'RAW MATERIALS', 
    Payment_Metadata.C1 = 'RAW MATERIALS', 
    Payment_Metadata.C2 = 'INGREDIENTS', 
    Payment_Metadata.C3 = 'OTHER ', 
    Payment_Metadata.RuleText = '---', 
    Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, 
    Payment_Metadata.IsExcluded = 0, 
    Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' 
    FROM 
    Payment_Metadata 
    WHERE 
    Payment_Metadata.IsProcessed = 0 
    AND (Payment_Metadata.EnrichedVendor = 'NFL' 
         OR Payment_Metadata.Vendor_No = 'NFL')

The second query uses an index:

UPDATE Payment_Metadata 
SET 
Payment_Metadata.CommodityCode = 'RAW MATERIALS', 
Payment_Metadata.C1 = 'RAW MATERIALS', 
Payment_Metadata.C2 = 'INGREDIENTS', 
Payment_Metadata.C3 = 'OTHER ', 
Payment_Metadata.RuleText = '---', 
Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, 
Payment_Metadata.IsExcluded = 0, 
Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' 
FROM 
Payment_Metadata 
WHERE Payment_Metadata.IsProcessed = 0 
  AND (Payment_Metadata.EnrichedVendor = '0202054' OR  
        Payment_Metadata.Vendor_No = '0202054')

The following is the table definition:

CREATE TABLE [dbo].[Payment_Metadata](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Company_Code] [varchar](1024) NULL,
    [Comp_Code_Desc] [varchar](1024) NULL,
    [Vendor_Acct_Group] [varchar](1024) NULL,
    [Vendor_No] [varchar](10) NULL,
    [Vendor_Name] [varchar](1024) NULL,
    [Vendor_ABN] [varchar](1024) NULL,
    [Vendor_PTerm] [varchar](1024) NULL,
    [Vendor_PTerm_Desc] [varchar](1024) NULL,
    [Purchasing_Group] [varchar](1024) NULL,
    [Purchasing_Group_Des] [varchar](1024) NULL,
    [PO_DocType] [varchar](1024) NULL,
    [PO_DocType_Desc] [varchar](1024) NULL,
    [Purchasing_Document] [varchar](1024) NULL,
    [PO_Date] [varchar](1024) NULL,
    [PO_CreatedBy] [varchar](1024) NULL,
    [Plant] [int] NULL,
    [Item_Number] [varchar](1024) NULL,
    [Material_Number] [varchar](1024) NULL,
    [Material_Group] [varchar](7) NULL,
    [Material_Group_Desc] [varchar](1024) NULL,
    [Account_Assignment] [varchar](32) NULL,
    [Acct_Assignment_Desc] [varchar](1024) NULL,
    [GL_Account] [varchar](7) NULL,
    [GL_Account_Desc] [varchar](1024) NULL,
    [PO_Desc] [varchar](64) NULL,
    [PO_Quantity] [decimal](10, 2) NULL,
    [Order_UOM] [varchar](1024) NULL,
    [Order_Price_Unit] [varchar](1024) NULL,
    [Invoice_Receipt] [varchar](1024) NULL,
    [Invoice_Reference] [varchar](1024) NULL,
    [Invoice_Date] [datetime] NOT NULL,
    [Invoice_Scan_Date] [datetime] NULL,
    [Invoice_Item] [int] NULL,
    [Invoice_Amount] [decimal](15, 2) NULL,
    [GST] [decimal](10, 2) NOT NULL,
    [Invoice_Gross_Amount] [decimal](15, 2) NULL,
    [Currency] [varchar](1024) NULL,
    [Document_Type] [varchar](1024) NULL,
    [Document_Number] [varchar](1024) NULL,
    [Document_Date] [datetime] NOT NULL,
    [Posting_Date] [datetime] NOT NULL,
    [Payment_Term] [varchar](1024) NULL,
    [Baseline_Date] [datetime] NOT NULL,
    [Due_Date] [datetime] NOT NULL,
    [Payment_Document] [varchar](1024) NULL,
    [Clearing_Date] [datetime] NOT NULL,
    [CommodityCode] [varchar](128) NULL,
    [RuleText] [nvarchar](max) NULL,
    [IsProcessed] [bit] NOT NULL,
    [DataSource] [varchar](5) NULL,
    [IsContracted] [bit] NOT NULL,
    [IsPreferred] [bit] NOT NULL,
    [VendorRiskScore] [varchar](5) NULL,
    [EnrichedVendor] [varchar](128) NULL,
    [LastUpdatedIndex] [int] NOT NULL,
    [LogText] [nvarchar](max) NULL,
    [IsExcluded] [bit] NOT NULL,
    [C1] [varchar](50) NULL,
    [C2] [varchar](50) NULL,
    [C3] [varchar](50) NULL,
    [OriginalVendor] [varchar](60) NULL,
    [AdjustedAmount] [decimal](15, 2) NULL
) ON [PRIMARY]

As you can see, the only change in the WHERE clause is the usage of numeric vs non-numeric characters (which I suspect should not impact the query plan?)

There is one non-clustered index on each of the columns EnrichedVendor and Vendor_No.

Any help would be appreciated.

UPDATE: Including the query plans below, and I noticed that query #1 suggests an index (in green). That may be the solution to the answer.

Without indexes enter image description here

Improve this question
7
  • 3
    And the number of records affected by update are
    – Sabin B
    Commented Apr 14, 2015 at 9:08
  • In most update cases, the index is used to locate the records that will be modified.
    – Sabin B
    Commented Apr 14, 2015 at 9:17
  • @sabinbio: About 2 million more or less.
    – Faredoon
    Commented Apr 14, 2015 at 23:54
  • 1
    @Spörri: There are 40170 records for '0202054' and 0 records for 'NFL'. Just wanted to mention that the WHERE clause is generated by a program (it's not hand-written) based on a set of values coming from an external source. So 'NFL' in this case is one such value which gets embedded into this query, but does not have any matching records.
    – Faredoon
    Commented Apr 15, 2015 at 5:50
  • 1
    So the only change is not the numeric or varchar data but the number of affected rows. Scanning the table is deemed more effective by the optimizer based on the histograms. Check the posts referenced by @tom v
    – Spörri
    Commented Apr 15, 2015 at 10:02

3 Answers 3

Reset to default
5

SQL Server uses statistics to determine an execution plan. If an index is available, so are statistics, and SQL server will determine the path of least work. This could be using the index or doing a table scan.

In your example SQL server has determined that a table scan is less work than doing an index seek and a bookmark lookup.

What you can see is that one of your querys is "less selective", ie it probably updates more records, meaning that more bookmark lookups are needed to satisfy the second query. SQL server just estimated that for that query the sum of index seeks + bookmark lookups are more work than simply doing a table scan.

Since SQL server reads entire pages, and not records, your query (as indicated by statistics) needs to be selective enough for the index to be used, if SQL server estimates that it's going to have to read every page anyway it will go for a table scan.

See here and here for a more indepth explanation with sample data and results

Improve this answer
4

The EnrichedVendor is not the same in the queries. I suspect that when you get a table scan you are working with products where the EnrichedVendor has a good majority of all the records in the table and therefore it makes sense to read all the table and the other EnrichedVendor has a much smaller percentile of the total records in the table.

But you also just might need to update the statistics for the table.

Improve this answer
2

Can you change the update? In general "or" clauses are bad for performance / index usage. If you would do 2 updates,

First update with one of the keys:

update ...
WHERE IsProcessed = 0 AND EnrichedVendor = '1024'

and the rest of the rows:

update ...
WHERE IsProcessed = 0 AND Vendor_No = 'NFL' and EnrichedVendor != '1024'

Usually this kind of approach works better, even if it looks more complicated

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.