sqlsql-serverstored-procedurescursor

Query porformance vs performance of the same query in cursor


I have a query, which uses subqueries and joins. I checked that query, it runs very well (fast). But then, I created cursor, where I placed exactle the same query, and then used that cursor in a procedure (which takes no parameters). In that procedure I check rows, if the fetched row is the same as previous (I determine that using one variable), if record in some column is equal, then omit this row, else insert into my table. That is all it does. And the procedure runs forever... Can anyone tell me why it takes so long?


Solution

  • You have a performance problem, and as such you need to investigate it as a performance problem. Please read How to analyse SQL Server performance.

    Now, you are comparing a SELECT with a stored procedure that does INSERT for one or more rows in that SELECT. To expect them to be similar time is naive, at best. You are comparing reads with writes. Think: reads come from cache, writes go to disk.

    You did not post any performance investigation data, so I'll use my magic 8-ball roll, which tells me you are issuing each INSERT in a standalone transaction and thus waiting for commit to flush for every INSERT. You cannot expect more than ~100 commits (rows) per second like this. You need to batch commit. Or, if on SQL Server 2014 or later, use delayed durability.

    Shnugo also gives good advice, is (almost) always better to use one set operation instead of a cursor, when possible.