SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Cursor Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/16/2001 :  20:30:44  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 09/18/2001 :  13:04:09  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Graz, thanks for the article! It's good to have real performance results and not just a debate on "ideals". Keep 'em comin'!

-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD!
Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 09/18/2001 :  13:58:35  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
Heh, one time i was working on this table that had no primary key set, had about 50,000 rows in it. i was looking at ways to increase performance and started with creating a primary key with a unique index, and created indexes on a couple other ID columns.
the pickle i was in was i added the column, set it to int, but couldnt set it to not allow nulls as i had 50,000 rows there without data in that column, so i couldnt make it an identity column, and therefore no indexing.
so i created the column, set it to int.
at this point the easiest way i could think of to just get the problem taken care of and not having to deal with it ever again (once there were incremental int values, i could make it what i needed and autonumbering would take over) so i wrote up a quick cursor to do it.. start at row one, update the column, increment the id value, move on.. etc..
on 50,000 rows this cursor took 18 minutes to run.
haha.
so spake graz, dont use cursors.

Go to Top of Page

khalik
Constraint Violating Yak Guru

United Arab Emirates
443 Posts

Posted - 09/19/2001 :  07:08:09  Show Profile  Reply with Quote
hi

i have similar problem.. i have a huge base on which i need to retrive data from diffrent tables with diffrent condition and do calculation. so i am using cursors but it takes hell lot of time.. what can be the alternative menthod to cursors... i need to scan through the entire base.........

hopeful for a helping tip
bye bye
khalik


Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 09/19/2001 :  08:07:18  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Temp tables and derived tables are common ways of getting around using a cursor. Even though there are some performance hits in using a temp table, if you break up what you're trying to do in smaller pieces within a SP, you're much better off than using a cursor. Feel free to post your code to one of the forums. There are plenty of people here who can suggest ways around the cursor for you if they can see the code and get an idea of what you're trying to accomplish.

Mike
"A program is a device used to convert data into error messages."

Edited by - mfemenel on 09/19/2001 08:07:51
Go to Top of Page

Teroman
Posting Yak Master

United Kingdom
115 Posts

Posted - 09/19/2001 :  08:14:58  Show Profile  Send Teroman an AOL message  Reply with Quote
For that filling in the identity column you could do the following:

declare @nid int
set @nID = 0

update myTable
set @nID = ID_Column = @nID + 1

does it in 1 go, much better than nasty cursor action!!

col

Go to Top of Page

khalik
Constraint Violating Yak Guru

United Arab Emirates
443 Posts

Posted - 09/19/2001 :  08:33:01  Show Profile  Reply with Quote

thanks mfemenel
i have 8 stored procedures and huge line of code.. it a bill run for trunk radio services
so i need to check the diffrent states of the radios and there events...
can u give a example on temp table instead of useing cursors (a small one so that i can get a idea)


hope for soon



Go to Top of Page

btrimpop
Posting Yak Master

USA
214 Posts

Posted - 09/19/2001 :  11:03:48  Show Profile  Reply with Quote
I noticed the only optimization on the cursor in the examples was read_only. I wonder what the performance difference would be if the cursor was specified fast_forward?

I've done some benchmarking of row based processing using a cursor vs a temp table and the cursor with fast_forward specified was actually faster. Of course that's in a row based system. In set based systems the curse...uh cursor is way slower. Unfortunately I have some situations where the only way I can process the data is on a row by row basis. At least the only way I can think of.

Go to Top of Page

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 09/22/2001 :  16:35:09  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
I mentioned in this posting http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=8642&FORUM_ID=5&CAT_ID=3&Topic_Title=%22where exists%22 doesn%27t do what I think it should%2E%2E&Forum_Title=Developer that I'd seen a 165x improvement from my stored procedure taking 1 hour 17 minutes down to 28 seconds by rewriting it without cursors. The SQL code is there if anybody needs any more prodding to explore alternatives to cursor usage!! :-)


Go to Top of Page

PiecesOfEight
Posting Yak Master

USA
200 Posts

Posted - 09/28/2001 :  16:29:45  Show Profile  Reply with Quote
This code is from searchdatbase.com.

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci772081,00.html

It uses a table variable with identity col and a while loop -- the table variable might give better perf, although if the standard table was already in memory... It would be interesting to see the numbers on this approach compared to a cursor.



DECLARE @tablenames TABLE 
(
table_id INT IDENTITY,
table_name SYSNAME
)

INSERT INTO @tablenames (table_name)
SELECT name FROM sysobjects
WHERE type='U'

DECLARE @i INT
DECLARE @tablename SYSNAME

SET @i = 1

WHILE (@i <= (SELECT MAX(table_id) FROM @tablenames))

BEGIN
SELECT @tablename = table_name
FROM @tablenames
WHERE table_id = @i

EXEC ('GRANT UPDATE ON ' + @tablename
+ ' TO public')

SET @i = @i + 1
END


Go to Top of Page

chessclock
Starting Member

Canada
8 Posts

Posted - 05/29/2006 :  13:57:44  Show Profile  Reply with Quote
/* 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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/29/2006 :  17:03:18  Show Profile  Reply with Quote
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
Go to Top of Page

chessclock
Starting Member

Canada
8 Posts

Posted - 05/30/2006 :  08:06:35  Show Profile  Reply with Quote
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

Go to Top of Page

hye ji
Starting Member

Philippines
1 Posts

Posted - 02/03/2010 :  03:46:29  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000