Is this query bad?

  • Hi - I had written a data correction script and I was told that this is one of the worst queries written as this would perform badly. I want to know if anyone else had any thoughts / suggestions

    UPDATE Orders SET Orders.OrderTypeDescription =

    CASE

    WHEN AF.OrderId = 'A' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'X')

    WHEN AF.OrderId = 'B' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'Y')

    WHEN AF.OrderId = 'C' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'Z')

    WHEN AF.OrderId = 'D' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'YY')

    WHEN aF.OrderId = 'E' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'ZZ')

    End

    FROM Orders AF

    INNER JOIN with <5> tables

    WHERE

    AND AF.OrderType in

    (

    'X',

    'Y',

    'Z',

    'XX',

    'YY',

    'ZZ'

    )

  • Being a correction query that you run only once, I wouldn't worry too much about it - as long as is doing its job.

    Your concert should be to put constraints/foreign keys to avoid that thing to happen in the future.

  • (SELECT OrderTypeId FROM LookUp Where OrderType = 'X')

    Will return a constant value , so why not use the constant ?



    Clear Sky SQL
    My Blog[/url]

  • wat about this?

    create table tempstore(id int identity (1,1),

    OrderID nchar(2),

    OrderType nchar(3))

    insert into tempstore(OrderID,OrderType)

    select 'A', 'X' union

    select 'B','Y'union

    select'C','Z' union

    select'D','YY' union

    select'E','ZZ'

    UPDATE Orders

    SET Orders.OrderTypeDescription = t.OrderType

    FROM Orders AF join tempstore t

    on

    INNER JOIN with <5> tables

    WHERE

    u can only run the update statement, periodically, also you can eliminate the "where AF.OrderType in

    (

    'X',

    'Y',

    'Z',

    'XX',

    'YY',

    'ZZ'

    ) "

    as the table only has these values

  • Agree with Dave, lose the sub selects and use the direct value. one of a few options you could use

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • Thank you'll for your response.

    2 good points, 1) being a data correction script how much we should worry about query being good performant 2) Use of constant values

  • Even though it may be a run-once query, performance should still be a consideration. If you are running this in a production environment, with millions of rows, and the query is a bad performing query - you could potentially create timeouts, connection problems, unhappy customers, etc.

    This query is nowhere close to being as bad as many of the queries I have seen. However, you will see some performance gain by removing the subqueries as already mentioned.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

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