query optimizing

  • Declare

    @a money,

    @Picklist varchar(10)

    -- for testing

    SELECT

    @a = 1000,

    @Picklist

    = 'Exclude' -- 'Include'

    IF

    (@Picklist = 'Exclude')

    BEGIN

    select acct_code,

    part_no

    ,location

    ,description

    ,sku_no

    ,list_status

    ,pub_date

    ,pub_status

    ,succession_status

    ,void

    ,in_stock

    ,avg_cost

    ,std_cost

    ,((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var

    ,(avg_cost - std_cost)*in_stock as extended_diff

    ,in_stock*avg_cost as extended_avg_cost

    from dfi_inv_master_list with (NOLOCK)

    where ((avg_cost - std_cost)*in_stock >= @a

    or (avg_cost - std_cost)*in_stock <= -1* @a)

    and avg_cost <> 0

    and in_stock <> 0

    order by ABS((avg_cost - std_cost)*in_stock) desc

    END

    ELSE

    BEGIN

    select acct_code, part_no

    , location

    , description, sku_no

    , list_status

    , pub_date

    , pub_status

    , succession_status

    , void

    , in_stock

    , avg_cost

    , std_cost

    , ((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var

    , (avg_cost - std_cost)*in_stock as extended_diff

    , in_stock*avg_cost as extended_avg_cost

    from

    dfi_inv_master_list with (NOLOCK)

    where ((avg_cost - std_cost)*in_stock >= @a

    or (avg_cost - std_cost)*in_stock <= -1* @a)

    and (avg_cost <> 0 OR avg_cost = 0)

    and (in_stock <> 0 OR in_stock = 0)

    order by ABS((avg_cost - std_cost)*in_stock) desc

    END

  • Why don't you just delete this part from the second query, it is always true:

    and (avg_cost <> 0 OR avg_cost = 0)

    and (in_stock <> 0 OR in_stock = 0)

  • Assuming @a will always be a positive number you can change

    where ((avg_cost - std_cost)*in_stock >= @a

    or (avg_cost - std_cost)*in_stock <= -1* @a)

    to

    where (ABS((avg_cost - std_cost)*in_stock) >= @a)



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Yes you are correct. any more suggestions plz

    Thanks

  • Here is a big one. If these queries are inside a stored procedure, I'd seperate them into their own procedure and use a master stored procedure to call the approriate procedure based on the inputs to the master stored procedure.

  • where ((avg_cost - std_cost)*in_stock >= @a

    or (avg_cost - std_cost)*in_stock <= -1* @a)

    will cause a scan every time, as well as the computation.

    I don't know that much about computed columns (so please, don't be shy to correct me, gurus- and I wouldn't take my word as the end-all-be-all, OP) but I know you can have a PERSISTED computed column which stores and retains the calculated value. This will at least get rid of having to do the math every single time, plus you might even be able to get a seek out of it (PERSISTED computed columns can be indexed).

    Like I said, I don't have all that much experience with these so I can't speak as to how they affect mass INSERTS and UPDATES. Someone please elaborate (or feel free to tell me my idea is terrible :hehe:)

  • Mike McQueen (10/7/2009)


    where ((avg_cost - std_cost)*in_stock >= @a

    or (avg_cost - std_cost)*in_stock <= -1* @a)

    will cause a scan every time, as well as the computation.

    I don't know that much about computed columns (so please, don't be shy to correct me, gurus- and I wouldn't take my word as the end-all-be-all, OP) but I know you can have a PERSISTED computed column which stores and retains the calculated value. This will at least get rid of having to do the math every single time, plus you might even be able to get a seek out of it (PERSISTED computed columns can be indexed).

    Like I said, I don't have all that much experience with these so I can't speak as to how they affect mass INSERTS and UPDATES. Someone please elaborate (or feel free to tell me my idea is terrible :hehe:)

    Actually, a computed column that isn't persisted can be indexed. It just needs to be deterministic. But, your basic premise is spot on.

    Also, regardless of that suggestion, the following will also force a full table scan or, at best, a full index scan...

    order by ABS((avg_cost - std_cost)*in_stock) desc

    You need to use the ol' "Divide'n'Conquer" methods to get any performance out of this code... Can you use a stored procedure? Also, can you use temp tables?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff- Thanks for the follow up, good to know I wasn't too far off 🙂

    I do have a question though- why would the order by cause a scan on the entire table?

  • It's not the Order by so much as the ABS function in the Order by I'm pretty sure.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • mtassin (10/8/2009)


    It's not the Order by so much as the ABS function in the Order by I'm pretty sure.

    But wouldn't it only need to ABS() the rows that meet the WHERE criteria?

  • I'd leave Jeff to explain exactly because I sometimes get confused as to why SCANs creep up in situations like this but.

    As I recall the order of operations in a SQL statement goes something like this

    FROM

    WHERE

    SELECT

    GROUP BY

    ORDER BY

    If the ABS function was a calculated field in the query above (I did a quick look and didn't see it) and the ORDER BY referenced that calculated field by name, I'd think it would sort by the result set.

    i.e.

    SELECT

    valuechange = ABS((endingstock - startingstock)*Cost)

    FROM

    inventory

    ORDER BY

    valuechange desc

    You might get a sort based on the result set. But because the ORDER BY is instead

    ORDER BY ABS((endingstock - startingstock)*Cost)

    SQL basically has to go back after the data again to get the values for endingstock, startingstock and cost.

    Again I'm probably off a bit in this description, but this is kind of what I think is going on.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • I definately need Jeff to explain because I've confused myself.

    I made a (VERY) limited test.

    create table #inventory(itemcode varchar(10),startlevel int, endlevel int, cost money)

    create index funfun on #inventory(itemcode,startlevel,endlevel,cost)

    insert into #inventory

    select

    'Apples',

    100,

    50,

    .35

    UNION ALL

    SELECT

    'Bannanas',

    75,

    50,

    .25

    UNION ALL

    SELECt

    'Peaches',

    80,

    20,

    .10

    SELECT

    itemcode,

    stockvalue = ABS((endlevel - startlevel)*Cost)

    FROM

    #inventory

    WHERE

    itemcode = 'Apples'

    order by

    ABS((endlevel - startlevel)*Cost)

    SELECT

    itemcode,

    stockvalue = ABS((endlevel - startlevel)*Cost)

    FROM

    #inventory

    WHERE

    itemcode = 'Apples'

    order by

    stockvalue

    And I get index seeks on both... so there's more to this than I thought. 🙂

    Jeff?



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Heh... you've confused me, as well. 😛 I'll have to study that a bit... I'll be back.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ah... got it. If you gen the actual execution plans for both queries, you'll see that the INDEX SEEK is used to resolve the predicate "WHERE itemcode = 'Apples'" and that the sort is taking 78% of the total workload (careful folks... the execution plan isn't ALWAYS right on something like this). And it's not just the ABS that's causing the wayward sort time... it's the fact that it's sorting a calculation of columns rather than columns.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Let's take this one step further. In a lot of cases, if an index is used by the sort, the sort operation in the execution plan will frequently not even appear (not ALWAYS true but IS TRUE in this particular case). Run the following code with the actual execution plan turned on to see what I mean. I've added a third query that shows this case-in-point...

    drop table #inventory

    go

    create table #inventory(itemcode varchar(10),startlevel int, endlevel int, cost money)

    create index funfun on #inventory(itemcode,startlevel,endlevel,cost)

    insert into #inventory

    select

    'Apples',

    100,

    50,

    .35

    UNION ALL

    SELECT

    'Bannanas',

    75,

    50,

    .25

    UNION ALL

    SELECt

    'Peaches',

    80,

    20,

    .10

    SELECT

    itemcode,

    stockvalue = ABS((endlevel - startlevel)*Cost)

    FROM

    #inventory

    WHERE

    itemcode = 'Apples'

    order by

    ABS((endlevel - startlevel)*Cost)

    SELECT

    itemcode,

    stockvalue = ABS((endlevel - startlevel)*Cost)

    FROM

    #inventory

    WHERE

    itemcode = 'Apples'

    order by

    stockvalue

    --===== Additional code shows that an index was used

    -- by the ORDER BY because of the absence of the

    -- "SORT" operation in the execution plan. It

    -- doesn't always work out this way, but this is

    -- a good example of when it does.

    SELECT

    itemcode,

    stockvalue = ABS((endlevel - startlevel)*Cost)

    FROM

    #inventory

    WHERE

    itemcode = 'Apples'

    order by

    StartLevel, EndLevel, Cost

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 26 total)

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