Fetching rows in cursors

mgr_2k7

Can anybody guide me as to how do we call a cursor having rows more than 10000? :( Manish Ganguly

Joe 2

I am afraid that you might be running under performance issues when you are working with cursors on a table with more than 10000 rows. Best bet will be to try populating your records onto a temporary table or table variable.

Once you have mastered the ability to turn yourself invisible at will, and to deflect bullets with your hair, you will be ready to come back here to the lounge and post programming questions.Only at this point will you be able to dodge the fireballs that are going to come your way. - Pete O'Hanlon

mgr_2k7

HI CS, I GOT WHAT U SAID. THIS IS MY SCENARIO: 1. PICK UP DATA FROM A TEMPORARY TABLE ROW BY ROW......VALIDATE WHETHER THEY FOLLOW BUSINESS LOGIC OR NOT. 2. IF THE DATA IS ERRONEOUS UPDATE A FLAG IN THE SAME TABLE TO F, ELSE UPDATE IT TO P. Manish Ganguly

Pete OHanlon

Please don't shout. First of all, can you perform your business logic in SQL? If you can, you can possibly do this without having to use a cursor at all. It's always best to use SET based updates wherever possible, so you could use something like:

INSERT INTO myTable (myCondition1, myValue1, myOtherValue, myValue) VALUES ('...', 1, 2, 'F')

UPDATE myTable mt
SET myValue = 'P'
WHERE myCondition1 = 'Hello' AND myValue1 > 30 AND myOtherValue >
(Select SUM(oValue) FROM otherTable ot WHERE mt.PrimaryKey = ot.ForeignKey)

As you can see, the initial insert creates the record and sets it's validity to false. The update is then used later on in your processing to update the records to P that have passed the test.

Deja View - the feeling that you've seen this post before.

My blog | My articles

andyharman

Hi Manish What database product are you using? Note that some databases (particularly SQL-Server), cursors are considerably slower set-based operations. Cursors would only normally be considered for reeeeeeally complex business rules). Under SQL-Server, if you really have to use a cursor, it would be something along the lines of:

declare c1 cursor for
  select field1, field2, etc
  from YourTable
  for update of MyFlag
open c1
fetch c1 into @Field1, @Field2, @Etc
while (@@FETCH_STATUS <> 0) begin
  set @MyFlag = 'P'

  --Do business logic checks here - if error then set @MyFlag to 'F'.

  update YourTable set MyFlag = @MyFlag
    where current of c1

  fetch c1 into @Field1, @Field2, @Etc
end
close c1
deallocate c1

The code is similar in other types of databases too. Regards Andy

Vasudevan Deepak Kumar

You were actually given some valuable help by some one. So why are you angry at the person who has helped you by expressing yourself in 'All Capitals'.

Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson

mgr_2k7

I am extremely sorry if u have mistook my All Caps as being angry. I was coding the Stored Proc at that time......and as per company standards I had to keep it in All Caps. I am sorry , I forgot to switch it off while typing my problem. Anyways Guys, Cheerz. We have found a roundabout way of handling it. While checking the "select" query was imposing a big chunk, so we have decided to design a tertiary table and use it directly to fetch the records from them. This improved my time around 45%. What intially took us 2:30 hrs to implement 10 lakh records, is now taking us 1:20 hrs. Tx guys for all the help u have provided. :-D ;) Manish Ganguly.