Article: Cursor Performance - 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

Site Related Forums

Article Discussion

Article: Cursor Performance

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-16 : 20:30:44

A recent article on 4GuysFromRolla.com had an ASP script to generate the Transact-SQL code for a cursor. I've always thought that cursors where slower than set-based SQL but I never knew how much slower. Read on to the results with a couple of suprises thrown in.

Article Link.

chessclock
Starting Member

8 Posts

Posted - 2006-05-29 : 13:57:44

/* can somebody tell me please: will this cursor work?
the problem is using SELECT statement within a cursor,
retreiving @StockTicketNumber for every record, and retrieving
appropriate totals for every @StockTicketNumber.
If the cursor does not work, is there any alternative
(like Temp tables, etc.) Thank you! */

DECLARE @unit_cost money, @WholesalePrice
money, @SUM_WholesalePrice money,
@SUM_VehicleSalvageCost money,
@StockTicketNumber text

/* here is the cursor itself */

@SUM_WholesalePrice money = 1 /* avoiding division by zero */
@SUM_VehicleSalvageCost money = 0

DECLARE get_unit_cost_cursor CURSOR

SET get_unit_cost_cursor = CURSOR FOR SELECT
UnitCost, WholesalePrice, StockTicketNumber
FROM Inventory

OPEN get_unit_cost_cursor

FETCH NEXT FROM get_unit_cost_cursor INTO @unit_cost

/* here is the loop */
WHILE (@@FETCH_STATUS = 0)

BEGIN
IF @unit_cost = 0

/* now getting the sum 1 and 2 using
subqueries, then calculating and updating */

UPDATE Inventory SET UnitCost = @SUM_VehicleSalvageCost * @WholesalePrice / @SUM_WholesalePrice
WHERE (CURRENT OF get_unit_cost_cursor) AND
@SUM_WholesalePrice = (SELECT SUM(WholesalePrice) FROM Inventory WHERE StockTicketNumber = @StockTicketNumber)
AND @SUM_VehicleSalvageCost = (SELECT SUM(VehicleSalvageCost) FROM VehicleStockDetails WHERE StockTicketNumber = @StockTicketNumber)

/* ELSE no update is taking place */

FETCH NEXT FROM get_unit_cost_cursor INTO @unit_cost
END

CLOSE get_unit_cost_cursor
DEALLOCATE @get_unit_cost_cursor

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-29 : 17:03:18

Chesslock, this thread is only like 4½ years old. New record!? for thread resurrection

If you really want quick help on this post the table structure of Inventory and VehicleStockDetails,
and some sample data, and example of what you want to accomplish.
Reading through that cursor code, just gives me a headache.

rockmoose

chessclock
Starting Member

8 Posts

Posted - 2006-05-30 : 08:06:35

Rockmoose,
thank you for your reply!

I have two tables: the Inventory table (some 300,000 records) and VehicleStockDetails (some 30,000 records).
Inventory table has UnitID, StockTicketNumber, WholesalePrice, and UnitCost fields (I omit the other 60 fields). VehicleStockDetails table has VehicleID, StockTicketNumber, and VehicleSalvageCost field.

All zero and NULL UnitCost fields needs to be updated based on the formula:
for each record, a UnitCost = WholesalePrice (of the same record) *
SUM(VehicleSalvageCost) / SUM(WholesalePrice)

Every Inventory (table) record has a WholesalePrice (field) used in a calculation of UnitCost (field to be updated). Also, for every record, two totals (used in a calculation of UnitCost) must be calculated by querying two separate tables (Inventory and VehicleStockDetails) by a StockTicketNumber corresponding to the CURRENT record.
This is why two queries are kind of connected to the cursor.

In MS docs I have found that I can't use more that one SELECT statement when using a cursor (in other words, there won't be any SELECT statements within a cursor). This is why I suspect that my solution (posted earlier) will not work!..

So, the tricky question is:
How can I use cursors (or can I use something else), if I need to update every record using a separate query (two queries) for each record to get the values for the update?

quote:
Originally posted by rockmoose

Chesslock, this thread is only like 4½ years old. New record!? for thread resurrection

If you really want quick help on this post the table structure of Inventory and VehicleStockDetails,
and some sample data, and example of what you want to accomplish.
Reading through that cursor code, just gives me a headache.

rockmoose

hye ji
Starting Member

1 Post

Posted - 2010-02-03 : 03:46:29

quote:
Originally posted by chessclock

Rockmoose,
thank you for your reply!

I have two tables: the Inventory table (some 300,000 records) and VehicleStockDetails (some 30,000 records).
Inventory table has UnitID, StockTicketNumber, WholesalePrice, and UnitCost fields (I omit the other 60 fields). VehicleStockDetails table has VehicleID, StockTicketNumber, and VehicleSalvageCost field.

All zero and NULL UnitCost fields needs to be updated based on the formula:
for each record, a UnitCost = WholesalePrice (of the same record) *
SUM(VehicleSalvageCost) / SUM(WholesalePrice)

Every Inventory (table) record has a WholesalePrice (field) used in a calculation of UnitCost (field to be updated). Also, for every record, two totals (used in a calculation of UnitCost) must be calculated by querying two separate tables (Inventory and VehicleStockDetails) by a StockTicketNumber corresponding to the CURRENT record.
This is why two queries are kind of connected to the cursor.

In MS docs I have found that I can't use more that one SELECT statement when using a cursor (in other words, there won't be any SELECT statements within a cursor). This is why I suspect that my solution (posted earlier) will not work!..

So, the tricky question is:
How can I use cursors (or can I use something else), if I need to update every record using a separate query (two queries) for each record to get the values for the update?

quote:
Originally posted by rockmoose

Chesslock, this thread is only like 4½ years old. New record!? for thread resurrection

If you really want quick help on this post the table structure of Inventory and VehicleStockDetails,
and some sample data, and example of what you want to accomplish.
Reading through that cursor code, just gives me a headache.

rockmoose

hey..i dont know if you have your solutions here because, it's been 4 years... but may i know exactly what it is you want to do?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources