Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Unanswered: Update-statement efficient enough?

    I have a working update-statement but I'm not sure it's as efficient as it can be.

    Two tables:
    Order
    -orderid
    -date
    -archive
    -bunchofothercolumns

    Orderrecord
    -article
    -category
    -orderid (foreign key referencing orderid in order-table)
    -bunchofothercolumns

    The column archive has a default value of '-'. If an order has one or more orderrecords with certain categories the archivecolumn needs to be set to 'Y', if not it gets updated to 'N'.

    The updatestatement I have:
    Code:
    update order
    set archive =
    	case
    		when
    			(orderid in (select orderid from orderrecord
    			 where category in
    				('aa','bb','cc','dd','ee','ff') )
    		then
    			'Y'
    		else
    			'N'
    	end
    where archive = '-'
    This statement is run once every night and does the trick. There should be a few hundred records every time that need to be updated, but I fear the subselect in the CASE will prove to be the bottleneck sometime. ORDER contains 900.000 records, ORDERRECORD around 1.5 million. Both will grow with respectively 100/200 and 500-ish records a day.

    Is my query good enough or can I optimise it a bit more?
    I'm not crazy, I'm an aeroplane!

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,

    To start, we could use a lit bit more info.
    So if you can post the field types and the types of keys and indexes present.
    Also you could see for yourself the queryplan.
    Code:
    set noexec on
    go
    set showplan on
    go
    Since you are using the 'archive' field, i'm assuming its not a key, so you should create an index. ( you should check the impact on other processes, i'm not a fan of creating index "left and right", in other processes may lead the optimizer to make poor choices, but in this case what i have is this info).

    Also when was the last time that statistics were updated on those 2 tables?

    Regarding the code, i like to use case, but in this case since you have a subquery i would split the update into 2.

    You will have smaller subgroups.

    Code:
    update order
    set  ord.archive = 'Y'
    from orderrecord o, order ord
    where ord.archive = '-'
    and o.category in ('aa','bb','cc','dd','ee','ff') )
    and ord.orderid = o.orderid
    
    update order
    set archive = 'N'
    where set archive != 'Y'
    This way you will use the key for the first, it will be faster.
    And for the second you use the index you will create for archive.

    But since you are experimenting you should try both solutions and post them here with the resulting query plans and execution times as well.

    That way we can see if is worthy or not to change the code.


    Hope it helps.

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Catarrunas, thank you for your contribution.
    While I was testing my query, the results eventually showed it wasn't the right query. I rewrote it completely different, splitting it in 2 so it also accomodated other must-haves and nice-to-haves of the customer.
    The number of records to be processed is no longer an issue, and it all works like a charm
    I'm not crazy, I'm an aeroplane!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •