Avoiding Row work in SQL

Question

Avoiding Row work in SQL

mike 57299

SSCrazy

Points: 2529
More actions
February 14, 2014 at 12:54 pm

#304423

Hi all...
Can this be done without a cursor:
create table #Order (Order_id int, qty int, ship_qty int)
insert into #Order
values
(1, 10, 0),
(2, 5, 0),
(3, 2, 0),
(4, 30, 0),
(5, 20, 0),
(6, 10, 0),
(7, 10, 0)
declare @onhandint = 30
declare @qtyint = 0
DECLARE @orderint = 0
DECLARE @ship_qtyint = 0
declare dbOrders cursor for
SELECT Order_id, qty FROM #Order order by Order_id
OPEN dbOrders
FETCH NEXT FROM dbOrders INTO @Order, @qty
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ship_qty = casewhen @onhand = 0 then 0
when @onhand >= @qty then @qty
when @onhand < @qty then @onhand end
update #Order set ship_qty = @ship_qty where Order_id = @order
set @onhand = @onhand - @ship_qty
FETCH NEXT FROM dbOrders INTO @Order, @qty
END
CLOSE dbOrders
DEALLOCATE dbOrders
SELECT * FROM #Order
drop table #Order
Thank you!
Mike

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

To get this right withuot a cursor, the best option is a method (which some call undocumented feature) called Quirky Update. You must read about this method on the following article before using it to understand and follow all the rules: http://www.sqlservercentral.com/articles/T-SQL/68467/

Here's an example:

create table #Order (Order_id int PRIMARY KEY CLUSTERED, qty int, ship_qty int)

insert into #Order

values

(1, 10, 0),

(2, 5, 0),

(3, 2, 0),

(4, 30, 0),

(5, 20, 0),

(6, 10, 0),

(7, 10, 0)

declare @onhandint = 30

declare @qtyint = 0

DECLARE @orderint = 0

DECLARE @ship_qtyint = 0

UPDATE o SET

@ship_qty = ship_qty = casewhen @onhand = 0 then 0

when @onhand >= qty then qty

when @onhand < qty then @onhand end ,

@onhand = @onhand - @ship_qty,

@qty = qty

FROM #Order o WITH (TABLOCKX)

OPTION( MAXDOP 1)

SELECT * FROM #Order

drop table #Order

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Are you limited to SQL 2008 for this? Or do you have SQL 2012 available?

2012 has a feature that allows this kind of "quirky update" to be done using windowing functions and avoiding the quirky update. It is many times faster and is set based.

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

mike 57299 SSCrazy Points: 2529 More actions · Answer 3

Actually,

Yes - we will be moving to SQL 2012 for the client that needs this. Can you elaborate?

Thanks,

Mike

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Here is an article that discusses it (running totals)

http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/

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

mike 57299 SSCrazy Points: 2529 More actions · Answer 5

According to the article, the Quirky Update is still significantly faster than the 'Over' option. Has there been an update or change on this?

Can others chime in as to which is better?

Thanks All!

TomThomson SSC Guru Points: 104773 More actions · Answer 6

mike 57299 (2/16/2014)
According to the article, the Quirky Update is still significantly faster than the 'Over' option. Has there been an update or change on this?
Can others chime in as to which is better?
Thanks All!

I think quirky update is still faster, so it should be used unless there is a good reason not to; but it should include the checks suggested by Paul and myself to ensure that if some update to the data engine (in a bug-fix, service pack, or new release) invalidates it that is detected and the detection results in an automatic switch to alternative code (using windowing functions).

Tom

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

SQLRNNR (2/14/2014)
Are you limited to SQL 2008 for this? Or do you have SQL 2012 available?
2012 has a feature that allows this kind of "quirky update" to be done using windowing functions and avoiding the quirky update. It is many times faster and is set based.

It may be many times faster than a cursor but the QU method blows the doors off of even the new 2012 methods. Please see the following article...

http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/

Still, if you don't trust "undocumented" methods, such as the Quirky Update, then the new functionality in 2012 would be the better way to go as it's a bit faster than a very well written cursor.

--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 Moden SSC Guru Points: 1004356 More actions · Answer 8

Ah.... sorry. I didn't scroll down before posting and didn't notice that you also referred to Wayne's fine article on the subject.

--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)