May 19, 2009 07:19 PM

Set-Based vs. Cursor-Based Solutions for Running Aggregates

Use cursors for large partitions
Rating: (0)
SQL Server Magazine
InstantDoc ID #101736
Executive Summary:
SQL Server’s optimizer handles set-based solutions to running aggregates with quadratic complexity (n2)with respect to partition size, whereas it handles cursor-based solutions with linear complexity. So in terms of performance, when dealing with small partition sizes—as many as a few hundred rows per partition—you’re better off with a set-based solution. However, when dealing with large partitions—more than 500 rows—you’re better off with a cursor-based solution.

...

You must be a paid Professional Member to access this entire article.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Monthly or Annual

Professional Membership

VIP Membership

Compare Member Benefits

Add a Comment

This set based method is using an inefficient triangular-join. There are other set-based methods that will blow this (and, obviously, the cursor) away.
See http://www.sqlservercentral.com/Forums/FindPost728664.aspx for a set-based example of a one million row running update that runs in 35 seconds, and a corresponding cursor-based solution that runs in 144 seconds.

[Reply:
Hi wgshef,

Thanks for your comment. The technique that you refer to as a more efficient set-based solution is one that I wouldn’t recommend using. I don’t have enough room to elaborate here; please see my response in the following blog entry: http://www.sqlmag.com/Article/ArticleID/102251/sql_server_102251.html.

Cheers,
BG]

WAYNE6/4/2009 4:16:27 PM


You must log on before posting a comment.

Are you a new visitor? Register Here

Windows Event 333->

We have a VM that has 8GB memory and is running Windows 2003 Enterprise Server 32bit and SQL Server 2000 SP4.Making no changes the system runs fine an...222-96226

GOOGLE LINKS
SPONSORED LINKS
FEATURED LINKS