CODEWITHSUNDEEP
sqlsql-serverstored-procedurescursor
Michał Turczyn
Michał Turczyn

Reputation: 37377

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?

Upvotes: 0

Views: 651

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I assume, that the insert operations on the table and your query, which you are calling over and over and the cursor, which relys on a given state of the table (indexes change on inserts!) are blocking each other.

The whole approach smells...

Do not use a CURSOR if you can avoid it! SQL is a set-based language and CURSORs represent a procedural concept. Sometimes a CURSOR can be the right choice, but much more often one should solve a problem differently.

On the first sight the MERGE statement might be perfect for you...

UPDATE

One idea might be, to use a temp table variable to collect all needed data in a staging table and do the real inserts in one go at the end.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294307

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.

Upvotes: 2

Related Questions