July 7, 2010 at 1:56 pm
Hi guys,
When i started my new job a few months ago I inherited a script that must be run every month and it contains a cursor. Here is the deal, I'm working with roughly 500,000 rows of data, and when I run the cursor below it runs for almost 3 hours before it finishes.
The reason the cursor is used it that we have a table, tbl.Bills_Endnotes, where it stores a bill number, bill line number, and end note. in this table a single bill will have multiple endnotes assigned to one line. (see below)
billidno line endnote
8788 1 23
8788 1 65
8788 1 1001
8788 2 41
8788 2 6
After using the cursor the output would look like this:
billidno line endnote
8788 1 23,65,1001
8788 2 41,6
If I run that cursor on say a 30,000 row data set it completes in a few seconds, but it seems once you got over around 50k rows it takes forever to run.
Now, I'm a newb when it comes to cursors, and I tried to understand the two part article that was recently published describing how to eliminate cursors, but to be honest I didnt understand it.
If someone could help me make this thing run faster in some way shape or form I would be forever greatful. this thing is the bane of my existance right now lol.
IF OBJECT_ID ('tempdb..#temp2')IS NOT NULL DROP TABLE #temp2
CREATE TABLE #temp2
(BILLIDNOINT,
LINEINT,
endnoteVARCHAR(255))
INSERT INTO #temp2
SELECT DISTINCT
b.billIDNo,
bb.line_no,
' ' as endnote
from #temp1 b
join bills bb on b.billidno = bb.billidno
/**** CURSOR BELOW ********/
IF OBJECT_ID ('tempdb..#TempEndnoteCombination')IS NOT NULL DROP TABLE #TempEndnoteCombination
CREATE TABLE #TempEndnoteCombination
(BillIDNo INT,
LineINT,
Combination VARCHAR(200))
DECLARE CombinationCursor CURSOR FORWARD_ONLY STATIC FOR
SELECT DISTINCT t.BillIDNo,
t.line,
RTRIM(LTRIM(en.endnote))
FROM #temp2 t WITH (NOLOCK)
JOIN Bills_Endnotes en WITH (NOLOCK) ON en.BillIDNo = t.BillIDNo and en.Line_no = t.line
--WHERE t.billIDNo = en.billIDNo
ORDER BY t.BillIDNo, t.line, RTRIM(LTRIM(en.endnote))
OPEN CombinationCursor
DECLARE @BillIDNo4 INT
SET @BillIDNo4 = 0
DECLARE @Line INT
SET @Line = 0
DECLARE @endnote VARCHAR(5)
SET @endnote = ''
FETCH NEXT FROM CombinationCursor
INTO @BillIDNo4, @Line, @endnote
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT DISTINCT BillIDNo, Line, Combination
FROM #TempEndnoteCombination WHERE BillIDNo = @BillIDNo4 and line = @Line
IF @@ROWCOUNT = 0
INSERT INTO #TempEndnoteCombination
VALUES(@BillIDNo4, @Line, @endnote)
ELSE
UPDATE #TempEndnoteCombination
SET Combination = Combination + ', ' + @endnote
WHERE BillIDNo = @BillIDNo4 and line = @Line
FETCH NEXT FROM CombinationCursor
INTO @BillIDNo4, @Line, @endnote
END
CLOSE CombinationCursor
DEALLOCATE CombinationCursor
/* end EndNote code cursor */
UPDATE #temp2
SET endnote = (SELECT combination FROM #TempEndnoteCombination WHERE billIDNo = #temp2.billIDNo and line = #temp2.line)
TIA!
Code
July 7, 2010 at 2:05 pm
we're going to need a little more than this. Can you provide table layouts, sample data, and expected results? It'll be better than us guessing at what the layouts look like
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 2:28 pm
If you are just trying to concatenate rows rows into a single column, see the following link.
July 7, 2010 at 3:07 pm
I re-read your problem. See how this works? I found this concept from another thread on this site. I'm not going to take credit for all of it
create table #table1 (BillIDNo int not null, Line int not null, EndNote int not null)
insert into #table1
select 8788, 1, 23 union all
select 8788, 1, 65 union all
select 8788, 1, 1001 union all
select 8788, 2, 41 union all
select 8788, 2, 6
SELECT [BillIDNo], Line,
STUFF(
( SELECT ',' + cast(EndNote as varchar)
FROM #table1 t2
WHERE t1.[BillIDNo]=t2.[BillIDNo]
AND t1.Line = t2.Line
FOR XML PATH('')
),
1,
1,''
) AS EndNote
FROM #table1 t1
GROUP BY [BillIDNo], Line
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 3:16 pm
Thanks, Mike. I was in the middle of re-writing my original post so it would be more intuitive as you suggested. I see what is going on in the script you posted, and this looks like it will work for me! I'm going to do some testing now. Thanks alot for the assist!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply