Problem with Cursor Loop - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Cursor Loop

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-05 : 11:42:12

Hello,
Below is the cursor loop which is taking 5hours to process 1 lakh records. I even tried taking out curosr with different approach but could not resolve it.. I donno where the code went wrong.
Need you help folks,
thanks.

----------DECLARE CURSOR-----------
DECLARE CR1 CURSOR FOR
SELECT JOB_INST_ID , ADV_NUM , SORT_CODE , SYS ,
LOC , num , F_NAME , LNAME ,
INIT , MISC_ADD , ADDRESSX , CTY_ST ,
ZIPCD , TMP_PRNJ250M2_ADV_TRAN_ID
FROM MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN
WHERE JOB_INST_ID = @JOB_INST_ID

OPEN CR1

FETCH NEXT FROM CR1 INTO @JOB_INST_ID , @ADV_NUM , @SORT_CODE ,
@SYS , @LOC , @num ,
@F_NAME , @LNAME , @INIT ,
@MISC_ADD , @ADDRESSX , @CTY_ST ,
@ZIPCD , @INTRAN_ID

WHILE( @@FETCH_STATUS =0)
BEGIN------------------CURSOR

-------SET THE VALUE OF TMP_ADV_NUM----------------
SET @TMP_ADV_NUM = @ADV_NUM

----------SETTING THE VALUES OF TMP_TOT_ADVS AND TOT_ADVS------
print 'INSIDE CORSOR TEMP ADV NUM ' + @TMP_ADV_NUM + ' , num: ' + @num

IF ((upper(@CODEX)!='R' )OR
(upper(@CODEX) ='R' AND CONVERT(INT,@RES_ADV_NUM) < CONVERT(INT,@TMP_ADV_NUM)) OR
(upper(@CODEX) ='R' AND CONVERT(INT,@END_ADV_NUM) > CONVERT(INT,@TMP_ADV_NUM)) )
BEGIN

if LTRIM(RTRIM(@SORT_CODE)) NOT IN ('07','08','7','8')
begin

SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1
SET @TMP_END_ADV_NUM = @TMP_END_ADV_NUM + 1
end

END
ELSE IF(upper(@CODEX)='R')
BEGIN --------------------7

SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1

IF LTRIM(RTRIM(@SORT_CODE)) NOT IN ('07','08','7','8')
BEGIN
SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1
SET @TMP_END_ADV_NUM = @TMP_END_ADV_NUM + 1
END

END --------------------7
-----------UPDATE THE VALUE OF NEW_ADV_NUM IN TMP_PRNJ250M_ADV_INTRAN ---------
---creating index on 'TMP_PRNJ250M_ADV_INTRAN' table changed by Diya

UPDATE MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN
SET NEW_ADV_NUM = @TMP_END_ADV_NUM
WHERE JOB_INST_ID = @JOB_INST_ID
AND TMP_PRNJ250M2_ADV_TRAN_ID = @INTRAN_ID

PRINT 'END PROCESS TIME FOR NEW ADV NUM INTO TABLE'+ CONVERT(VARCHAR(32), CURRENT_TIMESTAMP, 109)
------INSERT RECORD IN ZIP REPORT WHEN num>0 AND ZIP_COD=''------
-- IF(CONVERT(BIGINT,@num) > 0
-- AND LTRIM(RTRIM(@ZIPCD)) = '' )
-- BEGIN
-- INSERT INTO MPAS_TMP.dbo.TMP_PRNJ250M_ZIPRPT
-- SELECT @JOB_INST_ID , @SYS , @num , @LOC ,
-- @LNAME , @F_NAME , @INIT , @MISC_ADD ,
-- @ADDRESSX , @CTY_ST , @ZIPCD
-- END

FETCH NEXT FROM CR1 INTO @JOB_INST_ID , @ADV_NUM , @SORT_CODE ,
@SYS , @LOC , @num ,
@F_NAME , @LNAME , @INIT ,
@MISC_ADD , @ADDRESSX , @CTY_ST ,
@ZIPCD , @INTRAN_ID
END---------------------CURSOR

CLOSE CR1
DEALLOCATE CR1

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 11:58:45

Is this code complete? You never declare @TMP_END_ADV_NUM and it is never assigned a value.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-05 : 13:00:41

I have just sent the logic part, i left the declarations part..

quote:
Originally posted by jdaman

Is this code complete? You never declare @TMP_END_ADV_NUM and it is never assigned a value.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 13:45:54

Are the values assigned to @CODEX, @RES_ADV_NUM, @TMP_ADV_NUM, @END_ADV_NUM, @SORT_CODE from MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN? If not where are they coming from?

It may be possible to do away with the cursor entirely and replace it with a complex case statement.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-05 : 13:54:14

I am trying using Case statement...and even need ur help regardin case.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 14:04:23

We may be able to get there but I need to know how the above variables are instantiated.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-05 : 14:20:12

The variables mentioned are called in a SP. I mean, they are the Input's given to SP

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 14:23:23

Then this solution might work. You should test it thoroughly of course.

SELECT  @TMP_TOT_ADVS = CASE WHEN ( UPPER(@CODEX) != 'R' )
                                  OR ( UPPER(@CODEX) = 'R'
                                       AND CONVERT(INT, @RES_ADV_NUM) < CONVERT(INT, @TMP_ADV_NUM)
                                     )
                                  OR ( UPPER(@CODEX) = 'R'
                                       AND CONVERT(INT, @END_ADV_NUM) > CONVERT(INT, @TMP_ADV_NUM)
                                     )
                             THEN CASE WHEN LTRIM(RTRIM(@SORT_CODE)) NOT IN (
                                            '07', '08', '7', '8' )
                                       THEN CONVERT(BIGINT, @TMP_TOT_ADVS) + 1
                                       ELSE @TMP_TOT_ADVS
                                  END
                             WHEN ( UPPER(@CODEX) = 'R' )
                             THEN CONVERT(BIGINT, @TMP_TOT_ADVS) + 1
                             ELSE @TMP_TOT_ADVS
                        END

UPDATE  MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN
SET     NEW_ADV_NUM = CASE WHEN ( UPPER(@CODEX) != 'R' )
                                OR ( UPPER(@CODEX) = 'R'
                                     AND CONVERT(INT, @RES_ADV_NUM) < CONVERT(INT, @TMP_ADV_NUM)
                                   )
                                OR ( UPPER(@CODEX) = 'R'
                                     AND CONVERT(INT, @END_ADV_NUM) > CONVERT(INT, @TMP_ADV_NUM)
                                   )
                           THEN CASE WHEN LTRIM(RTRIM(@SORT_CODE)) NOT IN (
                                          '07', '08', '7', '8' )
                                     THEN @TMP_END_ADV_NUM + 1
                                     ELSE @TMP_END_ADV_NUM
                                END
                           WHEN ( UPPER(@CODEX) = 'R' )
                           THEN CASE WHEN LTRIM(RTRIM(@SORT_CODE)) NOT IN (
                                          '07', '08', '7', '8' )
                                     THEN @TMP_END_ADV_NUM + 1
                                     ELSE @TMP_END_ADV_NUM
                                END
                           ELSE @TMP_END_ADV_NUM
                      END
WHERE   JOB_INST_ID = @JOB_INST_ID
        AND TMP_PRNJ250M2_ADV_TRAN_ID = @INTRAN_ID

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-06 : 08:18:31

thnks a lot, it worked

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-06 : 10:32:05

I have a doubt, will this Case..When statement the exact requirement like cursor do?
I mean, we look(loop) for every record in a table and keep updating regards with it, so the same thing is done by this case statement?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 10:54:16

As long as the criteria is the same it will test every record in MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN and update it according to the case statement. The difference is that with the cursor you were doing that one record at a time where now using the case statement you do the full table at once.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-06 : 12:00:01

How much time it might take to update 1 lakh rows..?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 12:18:15

It all depends on the table layout and indexing involved although a set based solution should always be dramatically faster than a procedural solution such as a cursor.

If you are not confident that the above solution is performing its intended function I would suggest running the 2 solutions, the cursor based solution and the case update, on a pair of identical (smaller) data sets and compare the results after the scripts are run. (This is what I meant when I suggested that you run some tests)

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-06 : 13:43:34

All the records are updated at once but with same value.
I am looking for an Increment for every row it fetches
but itz not happening.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 14:08:12

Is the case update working correctly or is that having a problem as well? If the cursor solution is not working properly then you will need to manually check the data after running the case update statement verify that it is operating how you intend.

If that is not working correctly we may need to start from the beginning and see some sample data and desired end result and see if we can get a solution that way.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-06 : 15:39:43

Explain you the Requirements:

I have a table named Mpas_tmp.dbo.tmp_prnj250m
there are several records for particular job_inst_id

Now as earlier I have mentioned regarding the increment of Tmp_End_Adv,Tmp_tot_adv for every record related to the job_id

Lets say, for job_inst_id = 12345
there are 10 records

After we implement out logic the variable tmp_end_adv should hold 19,
Tmp_tot also should hold incremented values.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 15:47:05

Could you provide an example of your existing data and then based on that example what you are trying to do with the data?

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-06 : 20:33:30

Letz say,
Table A
---------
Tran_id Job_id Adv_num Sort_code
---- ------- ------ ---------
1 2a null 7
2 2a . 8
3 2a . 8
4 2a . 7
5 2a Null 6

Now after the logic of cursor posted on top
the value of Adv_num should be
100--> given as input for first record
101
102
103
103

I have a flow chart which explains much better but donno how to attach it.
we have used cursor and itz taking 6-7 hours to process 1 lak records
I dont know how to fetch every record w/0 cursor or loops.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-07 : 09:57:36

I will need a little more. Perhaps if you can give the main bullet points of your flow chart I could better understand the logic you are trying to implement in the solution.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-07 : 09:59:59

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96935

Will tell you what exactly I was looking for

Thanks for your patience..

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-07 : 10:06:06

quote:
Originally posted by nvakeel

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96935

Will tell you what exactly I was looking for

Thanks for your patience..

Good. Glad to help.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources