Cursors

[nickdel]

I'm looking to see if anyone can suggest an alternative to Cursors? The scenario: each day we perform a table transfer from our production DB to another SQL Server. This backup will then get transferred to a SAS server, however before I make it available to the SAS users I have to perform a fairly large calculation on each row within the table.
The table has about 100k rows so to run a sp against each row I'm having to use a cursor which you can imagine is taking a ridiculous amount of time.

All suggestions welcome! I've posted my cursor code however it's pretty standard.

SET NOCOUNT ON

DECLARE @SecurityID objectId

DECLARE Security CURSOR FOR

SELECT ID
FROM SecurityIncGrossValue

OPEN Security

FETCH Security into @SecurityID

WHILE @@fetch_status = 0

BEGIN

	EXEC sp_UpdateSecurityGrossValue @SecurityID
	FETCH Security into @SecurityID

END

CLOSE Security

DEALLOCATE Security

RETURN

Many thanks

Nick

 

[bborissov]

SET NOCOUNT ON
DECLARE @SecurityID objectId
SELECT @SecurityID = MIN(ID)
       FROM SecurityIncGrossValue

WHILE @SecurityID IS NOT NULL
BEGIN
    EXEC sp_UpdateSecurityGrossValue @SecurityID
    SELECT @SecurityID = MIN(ID)
           FROM SecurityIncGrossValue
    WHERE Id > @SecurityID
END
RETURN

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

Don't use a separate sp to do the calculation.

Example
suppose the sp does this (@Id being the input variable in the sp)

update table1
set field1 = field2-270/field3
where idfield = @id

This is great for single record updates. For mass updates it is a disaster.

The best way to handle if this is a calculated field in the database. That way each individual record changes automatically every time one of the fields in formula changes. If you are using very complicated rules or accessing fields outside the table, you may need to use a trigger instead.

Alternatively you can write a stored proc that updates all at once. If possible write it so that only the records which need to be updated are changed. In general though the format would be

update table1
set field1 = field2-270/field3

This would update all the records. To do only records where a change has happened you would need to have some way to structurally tell a change has occurred. It might be worth figuring this out as an update to 10 records which actually changed is far faster than an update to a million records when only ten have changed.

Without more detail as to what your proc actually does and how your database is structured, it is hard to give more specific advice.

"NOTHING is more important in a database than integrity." ESquared

 

[Crowley16]

your best bet would be to post the code of the sp to see if anyone can suggest a way of combining it all into set statements.

--------------------
Procrastinate Now!