Cursor and performance problem - SQL 7.0

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

SQL Server Development (2000)

Cursor and performance problem - SQL 7.0

Author

Topic

czmm5q
Starting Member

1 Post

Posted - 2004-03-04 : 16:42:19

Hi everyone,

I do have a question regarding the performance and usage of CURSOR. I launch the following SQL and it is LONG, really LONG, really LONG to get the result ( near 2 hours). Is there a problem calling a CURSOR into another cursor ? In fact this similar code is launch in another language using SQL (SQR) and it takes about 11 minutes !?!?! Can someone give me a hints on why it is so long !?

Thanks,

DECLARE tmp_location_cursor CURSOR FOR
SELECT DISTINCT location FROM inventory WHERE location = 'ASSEMBLY' OR location = 'MAIN' ORDER BY location

OPEN tmp_location_cursor

FETCH NEXT FROM tmp_location_cursor
INTO @location

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'echo ' + @location + ' >> C:\Temp\' + @file

/* Copy the location field into the file */
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

DECLARE tmp_cursor CURSOR FOR
SELECT it.itemnum, it.description, it.stocktype, it.in2, inv.avgcost
FROM item it, inventory inv
WHERE inv.itemnum = it.itemnum AND inv.location = @location ORDER BY it.itemnum
OPEN tmp_cursor

FETCH NEXT FROM tmp_cursor
INTO @item_num, @item_desc, @item_stocktype, @item_in2, @inv_avgcost

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ''

SELECT @curbal = SUM(invb.curbal)
FROM invbalances invb
WHERE invb.itemnum = @item_num AND invb.location = @location AND invb.curbal <> 0

SET @total = @total + @curbal
SET @total = @total * @inv_avgcost

SET @subtotal = @subtotal + @total

SET @item_num = @item_num + replicate(' ',(9 - (datalength(@item_num))))
SET @item_desc = RTRIM(@item_desc) + replicate(' ',(85 - (datalength(@item_desc))))

SET @bal = CONVERT(varchar,@curbal)
IF @bal IS NULL SET @bal = '0.00'
SET @bal = replicate(' ',(19 - (datalength(@bal)))) + @bal

SET @avgcost = CONVERT(varchar,@inv_avgcost)
IF @avgcost IS NULL SET @avgcost = '0.00'
SET @avgcost = replicate(' ',(15 - (datalength(@avgcost)))) + @avgcost

SET @tot = CONVERT(varchar,@total)
IF @tot IS NULL SET @tot = '0.00'
SET @tot = replicate(' ',(30 - (datalength(@tot)))) + @tot

SET @item_stocktype = @item_stocktype + replicate(' ',(25 - (datalength(@item_stocktype))))
SET @item_in2 = @item_in2 + replicate(' ',(6 - (datalength(@item_in2))))

SET @cmd = 'echo ' + @item_num + ' ' + @item_desc + ' ' + @bal + ' ' + @avgcost + ' ' + @tot + ' ' + @item_stocktype + ' ' + @item_in2 + ' >> C:\Temp\' + @file

EXEC master..xp_cmdshell @cmd, NO_OUTPUT

FETCH NEXT FROM tmp_cursor
INTO @item_num, @item_desc, @item_stocktype, @item_in2, @inv_avgcost

END /* End FETCH Loop for tmp_cursor */

CLOSE tmp_cursor
DEALLOCATE tmp_cursor

/* Grand-Total for each location for all items */

SET @subtot = CONVERT(varchar,@subtotal)

If @subtot IS NULL SET @subtot = '0.00'

SET @cmd = 'echo TOTAL FOR ' + @location + ' = ' + @subtot +' >> C:\Temp\' + @file

EXEC master..xp_cmdshell @cmd, NO_OUTPUT

FETCH NEXT FROM tmp_location_cursor
INTO @location

END /* End FETCH Loop for tmp_storeloc_cursor */

CLOSE tmp_location_cursor
DEALLOCATE tmp_location_cursor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-04 : 16:45:04

Yes there are performance problems calling a cursor within a cursor. In fact, calling even one cursor is a performance problem. SQL is designed for set-based queries not row by row.

On the SQR query, is the hardware comparable to SQL Server?

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-05 : 08:50:11

quote:
I do have a question regarding the performance and usage of CURSOR. I launch the following SQL and it is LONG, really LONG, really LONG to get the result ( near 2 hours).

Then you should love this, I doubt it will take more than a few minutes:

SELECT CASE GROUPING(inv.location) WHEN 1 THEN 'TOTAL FOR ' + inv.location + ' = ' ELSE Null END AS Location,
it.itemnum,
CASE GROUPING(it.itemnum) WHEN 1 THEN Null ELSE MAX(it.description) END AS Description,
sum(invb.curbal) AS CurBal,
CASE GROUPING(it.itemnum) WHEN 1 THEN Null ELSE MAX(inv.avgcost) END AS AvgCost,
--total goes here
CASE GROUPING(it.itemnum) WHEN 1 THEN Null ELSE MAX(it.stocktype) END AS StockType,
CASE GROUPING(it.itemnum) WHEN 1 THEN Null ELSE Max(it.in2) END AS IN2
FROM item it
INNER JOIN inventory inv ON inv.itemnum = it.itemnum
INNER JOIN invbalances invb ON invb.itemnum = it.itemnum AND invb.location = inv.location
WHERE inv.location IN('MAIN','ASSEMBLY') AND invb.curbal <> 0
GROUP BY inv.location, it.itemnum
WITH ROLLUP
HAVING GROUPING(it.itemnum)=0 --this may be optional
ORDER BY inv.location, GROUPING(inv.location), it.itemnum, GROUPING(it.itemnum)

Run that in Query Analyzer before trying to output it to a file (more on the file in a second). Look at the output and see how closely it matches what you're looking for. Don't concern yourself with column widths or headers, that comes later. If the results are close then you can modify it to get what you're looking for.

For the life of me I could not figure out what you were trying to do with the @total and @subtotal variables (you left out the code where these were defined, and probably more) They way they are written, both will hold the same value at the time they are written out, and it appears they are meant to generate a running total for the file. I commented the section for the total in the code above; without a better description of what that total is supposed to calculate I can't do more to provide it.

As far as outputting this to a file, use bcp. It can output fixed-width files very easily using a format file. Books Online has the details. DO NOT USE XP_CMDSHELL WITH ECHO. It's horrendously inefficient the way you're using it. bcp will blow you away with its speed and it's really the right tool for this job. Alternately, you can use osql to output the file, but you'd have to CAST() each column to a char datatype of the appropriate width, it's much messier and less flexible than bcp with a format file.

While you're in Books Online, look up ROLLUP, CUBE, and GROUPING as well.

Lastly, what you're trying to output here is a report, not a query, and you need to look at having a reporting software generate the file for you instead of SQL.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 11:03:09

Nice Rob!!

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-05 : 13:15:51

OK, my ighnorance is showing..(what again?)
'
WHAT is CASE GROUPING?

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-05 : 13:47:23

Brett,
That's what you have when you use ROLLUP. Basically, when you are on the "subtotal" item, display things a bit differently. That's what that is doing.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-05 : 14:26:54

I got it...just did a lookup in BOL..don't use it much...

Is it mostly used for warehouses?

Seems like you could do it with out it...albiet not as easy....

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-05 : 14:59:00

I use it for some high level system wide reporting tools.

It gives me something that looks like this:


CustomerA     5
CustomerB     10
CustomerC     20
Total         35

Depending on how you write stuff you can even have subtotals (maybe by month by customer and then the total at the bottom. I'd not use this if I had some reporting tool that would do the math for me. Using ROLLUP makes your stored proc more complicated. The instances I use it for I don't have much control of the output other than "loop through the result set and spit it out" type stuff.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources