none
Performance comparison between cursor and CTE

    Question

  • I have a stored procedure which contains cursor to update a column. But i have implemented the same logic using CTE without cursor. Will it improve performance.

    Query with CTE.

    WITH

     

    SQ AS

    (

    SELECT

    Column1

     

    ,Column2,

    RANK

    () OVER

     

    (PARTITION BY Column1 ORDER BY Column1,Column2) AS rankcolumn

     

    FROM Table1

    )

    UPDATE

     

    Table1 SET column3 = SQ.rankcolumn

    FROM

     

    Table1 MQ

    INNER

     

    JOIN SQ

    ON

     

    MQ.Column1 = SQ.Column1

    and

     

    MQ.Column2 = SQ.Column2

    I also tried without CTE.

    UPDATE

     

    Table1 SET Column3 = SQ.rankcolumn

    FROM

     

    Table1 MQ

    INNER

     

    JOIN

     

    (SELECT

    Column1

     

    ,Column2

     

    ,RANK() OVER

     

    (PARTITION BY Column1 ORDER BY Column1,Column2) AS rankcolumn

     

    FROM Table1) SQ

    ON

     

    MQ.Column1 = SQ.Column1

    and

     

    MQ.Column2 = SQ.Column2

    Please tell me which one will improve performance.

    Thanks.

    Tuesday, May 04, 2010 7:43 AM

Answers

  • In this particular case I think there should be 0 difference in performance since you used CTE as a derived table. Also, in your first case you don't need to use UPDATE FROM syntax, UPDATE CTE will update the original table. Try switching to UPDATE CTE and tell, if there is any difference in execution plan.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Edited by Naomi NModerator Tuesday, May 04, 2010 7:04 PM
    • Marked as answer by KJian_ Monday, May 10, 2010 6:01 AM
    Tuesday, May 04, 2010 3:01 PM
    Moderator

All replies

  • The obvious question is , why can you not tell us which is the faster performing ? You have executed both sets of code.
    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/
    Tuesday, May 04, 2010 8:01 AM
  • Hii..

    Both were same Statements. Considering the New Concept of CTE in SQL, it has slight edge in Performance point of view.

    Thanks


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Tuesday, May 04, 2010 9:00 AM
  • I can see both the queries have same execution plan. I want to know whether these two queries are better in performance when compared with cursor.

    Tuesday, May 04, 2010 9:23 AM
  • Cursor is a database object used to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In other words, we can say its like record set in the visual basic and ASP. By using cursor, we can perform detailed data manipulation on a row-by-row basis. It is very flexibility and easy to use and understand.

    However, there are so many drawbacks of Cursor. One of the major downside of cursor is performance. Because of row-by-row fetching of records, it will take more time. Find drawbacks of Cursor with more details below:

    1.       Resources consumed by cursors

    A cursor is a memory resident set of pointers - meaning it occupies memory from your system that may be available for other processes. Poorly written cursors can completely deplete available memory.

    2.       Speed and performance issues

    Cursors can be faster than a while loop but they do have more overhead. The problem with cursor speed is that, in many cases, the operation can be more efficiently written as a set operation or perhaps in a while loop. It's these cursor rewrites that lead to the impression that cursors are evil – or cursed.

    3.       Wrong tool for the wrong task

    Cursors are frequently the wrong tool for the wrong task. They're used for quick-and-dirty programming when a developer does not have a good understanding of set operations  or they're used for the wrong task entirely.

    4.       Subtle errors

    Cursors sometimes introduce subtle errors.

    We know cursor is one of the important elements in the Sql server but because of their drawbacks many developer choosing alternative of cursor like set based query. Go to here for alternative of cursor.

    Thanks,


    Kapil Khalas
    Tuesday, May 04, 2010 9:24 AM
  • In this particular case I think there should be 0 difference in performance since you used CTE as a derived table. Also, in your first case you don't need to use UPDATE FROM syntax, UPDATE CTE will update the original table. Try switching to UPDATE CTE and tell, if there is any difference in execution plan.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Edited by Naomi NModerator Tuesday, May 04, 2010 7:04 PM
    • Marked as answer by KJian_ Monday, May 10, 2010 6:01 AM
    Tuesday, May 04, 2010 3:01 PM
    Moderator
  • UPDATE CTE will update the original table, I believe.

     

    That's correct.  No reason to JOIN to the original table.  You can UPDATE the cte directly.


    --Brad (My Blog)
    Tuesday, May 04, 2010 5:18 PM
    Moderator