Menu
In SQL Server stored procedures when to use temporary tables and when to use cursors. which is the best option performance wise?
894
Neither is better. If your requirement is simply to compare data between two tables then you can do it as a set based operation without using a Cursor.
Using cursors we can access the records one by one. But the temp table is like a ordinary table, but the scope is limited to the current context.. i.e., if you create a temproray table inside the stored procedure the temproary table will be deleted automatically after the execution of the Stored procedure.
Looking at SQL Profiler results from these queries (each were run 10 times and averages are below) we can see that the CTE just slightly outperforms both the temporary table and table variable queries when it comes to overall duration.
Temporary tables behave just like normal ones; you can sort, filter and join them as if they were permanent tables. Because SQL Server has less logging and locking overheads for temporary tables (after all, you're the only person who can see or use the temporary table you've created), they execute more quickly.
If ever possible avoid cursors like the plague. SQL Server is set-based - anything you need to do in an RBAR (row-by-agonizing-row) fashion will be slow, sluggish and goes against the basic principles of how SQL works.
Your question is very vague - based on that information, we cannot really tell what you're trying to do. But the main recommendation remains: whenever possible (and it's possible in the vast majority of cases), use set-based operations - SELECT, UPDATE, INSERT and joins - don't force your procedural thinking onto SQL Server - that's not the best way to go.
So if you can use set-based operations to fill and use your temporary tables, I would prefer that method over cursors every time.
157
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
