June 14, 2011 at 1:13 pm
I'm trying to reduce the number of records that are returned with each fetch next using this cursor declaration
DECLARESTART_PROCESS CURSOR DYNAMIC FOR
SELECT LOAN_ID, STEP_CODE, ACTUAL_COMPLETION_DATE
FROM #START
WHERE RECORD_READ = 0
ORDER BY LOAN_ID,ACTUAL_COMPLETION_DATE,STEP_CODE
FOR UPDATE OF RECORD_READ
I'm updating the RECORD_READ = 1 when I find the matching conditions, but when I close and reopen the cursor the fetch next continues to bring in all of the records even if the RECORD_READ = 1. I thought the Dynamic cursor queried the #START table for each fetch next command. Any ideas
June 14, 2011 at 1:43 pm
I'm trying to reduce the number of records that are returned with each fetch next using this cursor declaration
DECLARE START_PROCESS CURSOR DYNAMIC FOR
SELECT LOAN_ID, STEP_CODE, ACTUAL_COMPLETION_DATE
FROM #START
WHERE RECORD_READ = 0
ORDER BY LOAN_ID,ACTUAL_COMPLETION_DATE,STEP_CODE
FOR UPDATE OF RECORD_READ
I'm updating the RECORD_READ = 1 when I find the matching conditions, but when I close and reopen the cursor the fetch next continues to bring in all of the records even if the RECORD_READ = 1. I thought the Dynamic cursor queried the #START table for each fetch next command. Any ideas
Phil
You might start by giving the rest of the code with some sample data and you could get a faster response.
"There are no problems! Only solutions that have yet to be discovered!":cool:
June 14, 2011 at 1:47 pm
I'm trying to find out why the updated records are still in the fetch next of the START cursor after they RECORD_READ is set to 1. I removed the order by statement, but it didn't work either. What exactly would make the cursor DYNAMIC
Here is the update statement
IF @END_STEP_CODE= '304'
BEGIN
IF @START_STEP_CODE = '619'
BEGIN
UPDATE #COMPLETE
SET START_ACTUAL_COMPLETION_DATE = CASE
WHEN @START_ACTUAL_COMPLETION_DATE > @PREV_END_ACTUAL_COMPLETION_DATE THEN
@START_ACTUAL_COMPLETION_DATE
ELSE DATEADD(DD,1,@PREV_END_ACTUAL_COMPLETION_DATE)
END
FROM #COMPLETE c
WHERE
c.loan_id= @END_LOAN_ID and
c.step_code = @END_STEP_CODE and
c.end_actual_completion_date = @END_ACTUAL_COMPLETION_DATE
UPDATE #START
SET RECORD_READ = 1
FROM #START c
WHERE
c.loan_id= @START_LOAN_ID and
c.step_code = @START_STEP_CODE and
c.actual_completion_date = @START_ACTUAL_COMPLETION_DATE
SET @PREV_END_ACTUAL_COMPLETION_DATE = @END_ACTUAL_COMPLETION_DATE
END
END
June 14, 2011 at 1:52 pm
I'm trying to find out why the updated records are still coming into the cursor after they RECORD_READ is set to 1. I removed the order by statement, but it didn't work either. What exactly would make the cursor DYNAMIC
Phil,
DYNAMIC
Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.
more information on it here: http://msdn.microsoft.com/en-us/library/ms180169.aspxHope that helps.
"There are no problems! Only solutions that have yet to be discovered!":cool:
June 14, 2011 at 1:53 pm
I would be willing to bet that you don't need a **cough**cursor**cursor** to do an update. You will benefit in huge performance gains by converting to a set based solution. Post some ddl and sample data and we can have a go at it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2011 at 2:00 pm
It's not so much using a cursor to do an update, but rather finding the starting record that matches the end record and finding any overlaps in the actual completion dates. Should I be updating the cursor result set? I was under the impression that the DYNAMIC cursor requeried the underlying tables after each fetch thus eliminating records that had already been updated.
June 14, 2011 at 2:00 pm
Sean
I would be willing to bet that you don't need a **cough**cursor**cursor** to do an update. You will benefit in huge performance gains by converting to a set based solution. Post some ddl and sample data and we can have a go at it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Thats exactly what I said earlier! Give enough info so we can actually provide a solution.
"There are no problems! Only solutions that have yet to be discovered!":cool:
June 14, 2011 at 2:05 pm
Phil
It's not so much using a cursor to do an update, but rather finding the starting record that matches the end record and finding any overlaps in the actual completion dates. Should I be updating the cursor result set? I was under the impression that the DYNAMIC cursor requeried the underlying tables after each fetch thus eliminating records that had already been updated.
Phil
You are right! This is an example of a dynamic cursor not that I recomend one.
Declare @user-id varchar(100)
create table #users (UserID varchar(100))
declare @sqlstatement nvarchar(4000)
set @sqlstatement = 'Insert into #users (userID) SELECT userId FROM users' exec(@sqlstatement)
declare users_cursor
cursor for Select UserId from #Users
OPEN users_cursor
FETCH NEXT FROM users_cursor INTO @userid
WHILE @@FETCH_STATUS = 0
BEGIN EXEC asp_DoSomethingStoredProc @userid
FETCH NEXT FROM users_cursor
INTO @userid
END
CLOSE users_cursor
DEALLOCATE users_cursor
drop table #users
"There are no problems! Only solutions that have yet to be discovered!":cool:
June 14, 2011 at 2:06 pm
Here is the temp table ddl
CREATE TABLE #complete
(LOAN_ID varchar(13),
step_code varchar(3),
start_actual_completion_date datetime,
end_actual_completion_date datetime
)
INSERT #complete
(loan_id,
step_code,
end_actual_completion_date
)
SELECT
LOAN_ID,
STEP_CODE,
ACTUAL_COMPLETION_DATE
FROM dbo.fct_foreclosure_step
WHERE CARVEOUT_IND = 1 and step_code in
('051','081','504','053','207','230','502','419','615','807',
'813','304','308','696','518','696','518','676','677',
'133','151','725', '974', 'D02','D04') and
CURRENT_RECORD_IND = 1 and loan_id in ('0000000379','0000001759')--and loan_id ='0000050393'--'0000001250','0000001759','0000004247','0000004252','0000006583')
ORDER BY LOAN_ID,ACTUAL_COMPLETION_DATE,STEP_CODE
CREATE TABLE #START
(loan_id varchar(13),
step_code varchar(3),
actual_completion_date datetime,
record_readint
)
CREATE NONCLUSTERED INDEX [IX_LOAN_ID] ON #START (loan_id,actual_completion_date,step_code)
INSERT #START
(loan_id,
step_code,
actual_completion_date,
record_read
)
SELECT
LOAN_ID,
STEP_CODE,
ACTUAL_COMPLETION_DATE,
0
FROM dbo.fct_foreclosure_step
WHERE CARVEOUT_IND = 1 and step_code in
('050','080','503','052','206','229','501', '418','614','806',
'812','305','307','695','517','619','695','517', '675',
'132','150','724','973', 'D01','D03') AND
CURRENT_RECORD_IND = 1 and loan_id in ('0000000379','0000001759')
ORDER BY LOAN_ID,ACTUAL_COMPLETION_DATE,STEP_CODE
DECLAREEND_PROCESS CURSOR FOR
SELECT LOAN_ID, STEP_CODE, END_ACTUAL_COMPLETION_DATE
FROM #COMPLETE
ORDER BY LOAN_ID,END_ACTUAL_COMPLETION_DATE,STEP_CODE
DECLARESTART_PROCESS CURSOR DYNAMIC FOR
SELECT LOAN_ID, STEP_CODE, ACTUAL_COMPLETION_DATE,RECORD_READ
FROM #START
WHERE RECORD_READ = 0
--ORDER BY LOAN_ID,ACTUAL_COMPLETION_DATE,STEP_CODE
FOR UPDATE OF RECORD_READ
June 14, 2011 at 2:10 pm
My question is what would cause the DYNAMIC cursor to continue to act as a READ ONLY cursor and continue to read the tempdb resultset? I didn't see a DYNAMIC declaration in your example or how the updated underlying table would return fewer records with each fetch next statement.
June 14, 2011 at 2:23 pm
Phil
My question is what would cause the DYNAMIC cursor to continue to act as a READ ONLY cursor and continue to read the tempdb resultset? I didn't see a DYNAMIC declaration in your example or how the updated underlying table would return fewer records with each fetch next statement.
Phil
If I understand your question right I think that your answer lies in your code below:
DECLARE START_PROCESS CURSOR DYNAMIC FOR
SELECT LOAN_ID, STEP_CODE, ACTUAL_COMPLETION_DATE,RECORD_READ
FROM #START ----------------this is where it is pulling the data
WHERE RECORD_READ = 0
--ORDER BY LOAN_ID,ACTUAL_COMPLETION_DATE,STEP_CODE
FOR UPDATE OF RECORD_READ
I do not see any changes in the code provided that would change the data in the temp table #start in order for the dynamic part of cursor to work.
"There are no problems! Only solutions that have yet to be discovered!":cool:
June 14, 2011 at 2:26 pm
This is the update
IF @END_STEP_CODE= '133'
BEGIN
IF @START_STEP_CODE = '132'
BEGIN
UPDATE #COMPLETE
SET START_ACTUAL_COMPLETION_DATE = CASE
WHEN @START_ACTUAL_COMPLETION_DATE > @PREV_END_ACTUAL_COMPLETION_DATE THEN
@START_ACTUAL_COMPLETION_DATE
ELSE DATEADD(DD,1,@PREV_END_ACTUAL_COMPLETION_DATE)
END
FROM #COMPLETE c
WHERE
c.loan_id= @END_LOAN_ID and
c.step_code = @END_STEP_CODE and
c.end_actual_completion_date = @END_ACTUAL_COMPLETION_DATE
UPDATE #START
SET RECORD_READ = 1
FROM #START c
WHERE
c.loan_id= @START_LOAN_ID and
c.step_code = @START_STEP_CODE and
c.actual_completion_date = @START_ACTUAL_COMPLETION_DATE
SET @PREV_END_ACTUAL_COMPLETION_DATE = @END_ACTUAL_COMPLETION_DATE
END
END
June 14, 2011 at 2:35 pm
Have you checked for duplicate records?
June 15, 2011 at 8:43 am
I agree the problem is in the cursor...in fact i would suggest that the problem is using a cursor in the first place. If you can post some sample data (in the form of inserts) and describe what you are trying to do this can be done in set based and forgo the cursor entirely. This does not look like anything that has to be done row by agonizing row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply