Post reply

Multiple conditions in where clause will cause any performance issues ?

  • May 29, 2015 at 5:15 am

    #304543

    Hi there,

    I have to update a column in a table by checking multiple conditions, so far I have using "One update for one condition" approach.

    here is the example :

    Create Table #Test (Name Varchar(Max),Age Varchar(max),TaxID Varchar(max),ValidationMsg Varchar(max) )

    UPDATE #Test

    SET ValidationMsg= ISNULL(ValidationMsg,'') +'Name is Mandatory and length should not be greater than 15'

    WHERE (LEN(IsNull(Name ,'')) < 0 AND LEN(IsNull(Name ,'')) < 15)

    UPDATE #Test

    SET ValidationMsg= ISNULL(ValidationMsg,'') + 'Age Should be numeric'

    WHERE isnumeric(Age ) = 0

    UPDATE #Test

    SET ValidationMsg= ISNULL(ValidationMsg,'') + 'TaxID should be 9 digit'

    WHERE len(TaxID ) <> 9

    Now I thought to put that all in a single query. So I have come up with a solution like following .

    UPDATE #Test

    SET ValidationMsg= ISNULL(ValidationMsg,'')

    + Case When (LEN(IsNull(Name ,'')) < 0 AND LEN(IsNull(Name ,'')) < 15) then 'Name is Mandatory and length should not be greater than 15' else '' END

    + Case When isnumeric(Age ) = 0 then 'Age Should be numeric' else '' END

    + Case When len(TaxID ) <> 9 then 'TaxID should be 9 digit'else '' END

    WHERE (LEN(IsNull(Name ,'')) < 0 AND LEN(IsNull(Name ,'')) < 15)

    OR isnumeric(Age ) = 0

    OR len(TaxID ) <> 9

    In production system i have nearly 100 columns to check like above. No index is defined.

    So adding multiple condition in single where clause would cause any performance issue ?

  • May 29, 2015 at 5:32 am

    #1801533

    Test and see, with all the OR conditions that last update will have to be a table scan, but without indexes it'd have to be a table scan anyway

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 30, 2015 at 4:40 am

    #1801805

    I see varchar(MAX) for all the columns.

    You cant put index on it.

    The size limit is 900 bytes, so that is varchar900 or nvarchar450

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply