AK47 AK47 - 1 month ago 9
SQL Question

Casting inside NOT IN is making query run slower

I have to update Records of my TEMP table (variable of User defined table type) based on the following condition.


  1. ArticleNumber in my TEMP table is VARCHAR(100)

  2. ArticleNumber in my Article Table is BIGINT

  3. I want to update all records as IsValidArticle = 0 when ArticleNumber from my TEMP table is not there in Article table



SOLUTION 1:

Casting ArticleNumber of a Physical table. This takes around 10 mins.

UPDATE @RebalanceRecsSet
SET [IsValidArticle] = 0
,ProcessedStatus = 'INVALID'
WHERE Article NOT IN (
SELECT CAST(ArticleNumber AS VARCHAR(100)) FROM MDR.dbo.Article
)


SOLUTION 2:

This works in 10 secs. But, I Can't do this. Because it can have few NONINT (BIGINT) values in my TEMP table

UPDATE @RebalanceRecsSet
SET [IsValidArticle] = 0
,ProcessedStatus = 'INVALID'
WHERE CAST(Article AS BIGINT) NOT IN
(SELECT ArticleNumber FROM MDR.dbo.Article)


Did I miss something? Is there any other way to achieve this?


Note: ArticleNumber of my TEMP table will be
VARCHAR(100)
.

Answer

Try this instead, it does not have the expensive convert. The conversion error is avoided, unless you have some weird varchars in your table variable which are considered numeric. If you have alot of rows in your table variable, it may be more effective to use a temporary table.

UPDATE  r
SET [IsValidArticle] = 0 
    ,ProcessedStatus = 'INVALID'
FROM @RebalanceRecsSet r
WHERE
  Article NOT IN 
    ( SELECT ArticleNumber 
      FROM MDR.dbo.Article 
      WHERE isnumeric(r.Article) = 1)

A different and more effective way of writing the update:

UPDATE  r
SET [IsValidArticle] = 0 
    ,ProcessedStatus = 'INVALID'
FROM
  @RebalanceRecsSet r
LEFT JOIN 
  MDR.dbo.Article
ON
  isnumeric(r.Article) = 1 and
  x.ArticleNumber = r.Article
WHERE x.ArticleNumber is null

It may be worth mentioning that empty varchar will equal 0 in the comparison.