Search code examples
sql-serverperformancecursorupdating

SQL Server with bulk update avoiding Cursor

I have a performance problem with cursor which I would like to avoid. The problem I am trying to solve is: To update a column (VARCHAR ) on each record (about 6000000) (by using one update statement) in the Main table by using a result calculated by a UDF which itself should update a counter in another Counter table.
- The problem is that the UDF can not update Countertable because it is a side effect. - It also can not update a temp or variable table because of READONLY flag. - I used an update trigger on the main table, but the trigger fired once by a single update statement which stored in a table called inserted.

The solution I have opted to is to use the Cursor as follows.

  1. Create a cursor from the Main table.
  2. fetch the first row
  3. get the counter from the Counter table
  4. counter++
  5. call a PROC to calculate the result , counter, etc
  6. update the Main table with the result from the PROC
  7. update the Counter table
  8. fetch another row

But believe me it is too slow about 125 hours (no way).

Can any one out there help me?

Solution

  • I assume you have another column in your main table which identifies the row, or else the counter wouldn't make much sense.

    Can you try with this? If I understood your problem right, neither a function nor a cursor is needed. Just a temporary table, cause you can't read and update a table at the same time.

    INSERT INTO tmpTable
SELECT
yourPrimaryKey, sex, YEAR(dob) AS dobyear, ROW_NUMBER() OVER (PARTITION BY YEAR(dob) ORDER BY dob) AS counter
FROM
mainTable;

UPDATE mainTable
SET code = CONCAT(tmpTable.sex, tmpTable.dobyear, counter)
FROM
mainTable
INNER JOIN tmpTable
ON mainTable.yourPrimaryKey = tmpTable.yourPrimaryKey;

    It's not tested, excuse any syntax errors or whatever.