Do I need a cursor here?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Do I need a cursor here??

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 13:04:49

Hi,

When a user logs in, I need to iterate through the contents of their shopping cart and update the prices with the values from the main catalog.

Some of the items in the cart are priced using price bands. ( e.g. 1 - $2, 2+ - $1) These price bands for these cart items are stored in a seperate table.

So for each shopping cart item, I need to look up the corresponding item in the main catalog, check to see if its been updated and, if it has, check to see if it uses price bands. If it is using them then the price bands in the cart's price bands table need to be replaced with the bands from the main catalog.

Considering the amount of work which potentially needs to be done for each cart item, do I need to use a cursor to iterate through the cart?

Cheers,

X-Factor

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-19 : 14:37:40

Nope, you don't need a cursor:

CREATE PROCEDURE UpdateCart @cartID int AS
UPDATE Cart SET Price=IsNull(Band.Price, Master.Price)
FROM Cart INNER JOIN Master ON Cart.PartNumber=Master.PartNumber
LEFT JOIN Band ON Cart.PartNumber=Band.PartNumber AND Cart.BandNumber=Band.BandNumber
WHERE Cart.CartID=@cartID

Without the actual structure that's the best I can do. Essentially, the shopping cart table is joined to the master catalog table to get the regular price. It is also left joined to the bands table to see if they've been banded in the cart table, if there is a match it will use that price instead.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 15:24:24

Thanks for your reply.

The query you suggested only has an UPDATE. How would it handle the situation where a product has been switched form band pricing to single pricing?

In this instance the cart price bands would need to be deleted altogether.

Here is the relevant structure...

PRODUCTS --
productID int,
price money,
priceStyle int,

PRODUCTPRICEBANDS
priceBandID int,
productID int,
lowerBound int,
price money

CART
itemID int,
cartID varchar,
productID int,
priceStyle int,
price money

CARTPRICEBANDS
itemID int,
lowerBound int,
price money

This isn't actually the full story. There's a third price style which is to price a product by a product combination. e.g. (color, size )

With this style, a cart item references a combo and stores in the price field the combo's price.

I don't see how a single query can handle the logic required here. That's why I thought it necessary to deal with each item one by one.

Cheers,

X-Factor

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 15:32:28

Not sure what you want to do here but in the worst case you can split the entries into types.
First update those whose pricing type ahs changed from band to single
Then for single to band
Then those whose price has changed.

Depends how many items you are expecting but if it's more than a couple this will be a lot more efficient than a cursor (which you should never use).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 16:14:58

Once a user has entered a product into their cart, the price should be fixed until the end of the session even if an admin changes the price in the main catalog.

When the user logs in, the prices in the cart should be updated so that they are in synch with the catalog.

I believe this is called price protection. Perhaps its not worth doing?

Anyhow, the updating operation is not simply an UPDATE.

For example, if a product is put into the cart and has price bands and then an admin deletes the price bands from the product in the catalog and gives the product a single price, when the user logs back in the, item in the cart needs to also switch from price bands to single price and this would also involve deleting the price bands being used by the cart.

What is so bad about a cursor? It says in the doc that they're very efficient on the server.

Cheers,

X.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 16:29:36

What are the pricebands? Something to do wit the number of products you buy?

Not sure what the requirement is but

update Cart
set price = p.price
from Cart c
join Products p
on p.ProductID = c.ProductID

update CARTPRICEBANDS
set Price = p.price
from CARTPRICEBANDS c
join PRODUCTPRICEBANDS p
on p.ProductID = c.ProductID
and p.lowerBound = c.LowerBound

delete CARTPRICEBANDS
from CARTPRICEBANDS c
left outer join PRODUCTPRICEBANDS p
on p.ProductID = c.ProductID
and p.lowerBound = c.LowerBound
where p.ProductID is null

insert CARTPRICEBANDS
(
itemID ,
lowerBound ,
price
)
select
c1.itemID ,
p.lowerBound ,
p.price
from PRODUCTPRICEBANDS p
join (select distinct itemID, ProductID from Cart) c1
on c1.ProductID = p.ProductID
left outer join CARTPRICEBANDS c
on c.ProductID = p.ProductID
and c.lowerBound = p.LowerBound
where c.ProductID is null

Cursors in sql server are a lot less resource hugry than they used to be. The problem is that they force you into row by row processing which is very inefficient.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-19 : 16:32:28

create table t (EmpID varchar(20) primary key clustered, Salary int)

And now we want to increase the salary of each 5th employee by 2 times
(if they're ordered by their EmpIDs (asc)):

update t set Salary=Salary*2 where
(select count(*) from t tt where tt.EmpID<=t.EmpID) % 5 = 0

Is it obviously that this way of updating will be faster than via a cursor?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-19 : 16:44:47

quote:
What is so bad about a cursor?

To answer that, think about putting sugar in a cup of tea or coffee. You can use a spoon, or you can use tweezers. Cursors are tweezers; they can only handle one row at a time, as tweezers can only pick up one grain of sugar at a time.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 16:57:57

quote:
What are the pricebands? Something to do wit the number of products you buy?

Indeed. For example,

1-2 items = £4 each
3-7 = £3
8+ £2

I'm using the following query to look up the price...

UPDATE ShoppingCart SET UnitCost =

(SELECT TOP 1 price FROM ShoppingCartPriceBands WHERE lowerBound <= @quantityNew and cartItemID = @itemID ORDER BY lowerBound DESC)

WHERE itemID = @itemID

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 17:11:08

quote:
Cursors are tweezers; they can only handle one row at a time

But surely within a normal UPDATE or what have you, even though the single operation may modify a multitude of rows, each row is still also going to have to be modified done one by one?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-19 : 17:31:43

No. SQL Server reads data in pages and extents, regardless of how much data resides on them. If all of the data that needs to be updated is on the same page, then then entire page can be updated in one operation. Even if the data resides on multiple pages, the row-by-row navigation of a cursor is eliminated.

Also, when you use a cursor, you are essentially bypassing the work that the query optimizer performs, and losing the benefits. One of the central points of a relational database is that the internal mechanics of the actual data storage are not important. The query processor/optimizer takes the query definition in SQL and finds the most effective way to retrieve and process the data. A cursor can only interfere with and slow down this process.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 17:39:14

You think a relational database will take an update statement and for each row affected retrieve it, update it, write it then go on to the next row? That would be a tad inneficient.
I've had this argument put forward before, forcibly and at length - particularly from Oracle people. No idea where it comes from.
Try it and see (you can put in a cursor if you think it will make a difference).
I guess this will work - haven't tried it.
You might like to start with less than 20 inserts into #a as I suspect that will take a long time for the loop (but not the update) and you should see a difference at a much lower number.

create table #a (i int identity, s char(1000))
create unique index ix on #a (i)

set nocount on

insert #a select '1'
declare @i int, @maxi int
select @i = 1
while @i < 20
begin
insert #a (s) select s from #a
select @i = @i + 1
end

select count(*) from #a

select getdate()
update #a set s = '1'
select getdate()
select @i = 0, @maxi = max(i) from #a
while @i <= @maxi
begin
set @i = @i + 1
update #a set s = '2' where i = @i
end
select getdate()

drop table #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 17:39:40

OK, well a series of SQL statements handling the different updates and deletions seems to be the way to go here.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 17:57:08

quote:
I've had this argument put forward before, forcibly and at length - particularly from Oracle people. No idea where it comes from.

Oh no! I'm not looking for any trouble here or one of those absurd threads which span several pages containing many insulting remarks.

I'm genuinely ignorant about how databases do their stuff and I appreciate your insights.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-19 : 18:01:14

[code]update Cart
set price = p.price
from Cart c
join Products p
on p.ProductID = c.ProductID
[/code]

This update should lock the cart items being updated. Will it also lock the products being read from?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-19 : 18:06:49

Yes, it will hold an update lock that prevents someone else from changing the value, although they would still be able to read it.

If you need to control the level of isolation of transactions, see Books Online under "SET TRANSACTION ISOLATION LEVEL". It describes how each type of operation (SELECT, UPDATE, etc.) is affected by the 4 different isolation levels.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 18:31:39

It will take an exclusive lock on Cart and a shared lock on Products. These locks will be dropped on completion of the statement unless within a transaction (in which case the lock on Cart will be held).
You can see the locks by

begin tran
update Cart
set price = p.price
from Cart c (holdlock)
join Products p (holdlock)
on p.ProductID = c.ProductID

select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
object_name(rsc_objid) ,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status

from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u

where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and req_spid = @@spid
and rsc_dbid = db_id()
and object_name(rsc_objid) in ('cart','products')

rollback tran

I got the lock query from master..sp_lock and changed it a bit (also see http://www.nigelrivett.net/sp_nrLocks.html).
Also run it without the holdlocks to see the difference.
If you are interested in this sort of thing get a copy of inside sql server by Kalen Delaney. You can see it under books on this site.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-19 : 18:32:11

I'll experiment my case (though it needs to be more complex)
tomorrow and report results. And I'm still convinced that in
say 1% of cases cursors are more effective than
the set-based approach.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 18:40:37

No idea what the percentage is but there are some cases where a cursor is fater than the set based equivalent (I think only when correlated subqueries are involved) but the gain is not that impressive.
This happens so rarley that it is a good rule of thumb to never use a cursor until trying all other methods.
Using cursors when they are not necessary can slow down the process by thousands of times (as will will have found if you tried the code I supplied).

For things like calling an SP for every row in a table there is no option other than row by row processing and you might as well use a cursor. I wouldn't because if you wanted to do any manipulation or aggregates on the data as well as calling the sp it would be much faster with a temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-19 : 18:45:13

OK, Nigel...

PS My percentage here is just for fun :)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-20 : 07:49:28

Just have finished testing. And I can swear

it was impressive!!


                  Cursor      Set-Based Update
------------------------------------------------
1st execution     58sec       2h 09m 09sec

2nd execution     34sec       3h 08m 18sec

3rd execution     2m 15sec
------------------------------------------------

Any comments will be greatly appreciated. What did I miss here?

CREATE TABLE [t] (
	[EmpID] [varchar] (50) NOT NULL ,
	[Salary] [int] NULL ,
	CONSTRAINT [PK_t] PRIMARY KEY  CLUSTERED 
	(
		[EmpID]
	)  ON [PRIMARY] 
)
GO

Sample data from the table t (50000 rows in it):


EmpID                                              Salary      
-------------------------------------------------- ----------- 
0001A490EBDF-B799-2EA4-31D9-8883393F               1
000328DE1323-A2B8-EB24-4215-3C36901D               1
0003CB84B278-1FF9-4EE4-5EE2-0CD1D04D               1
--------------------------------------------------------------

Microsoft SQL Server  7.00 - 7.00.623 (Intel X86) 
	Nov 27 1998 22:20:07 
	Copyright (c) 1988-1998 Microsoft Corporation
	Desktop Edition on Windows 4.10 (Build 1998:  )

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources