Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Indexed Views Expand / Collapse
Author
Message
Posted Thursday, November 18, 2010 9:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 2:13 PM
Points: 9, Visits: 66
@Hugo:
I'm not an expert in SQL. I was also under the impression that Cursors are a big NO and hence should be avoided at all costs. I'm only saying that after reading numerous articles which states that. Now I'm really curious to see your explanation and to learn why Cursors may be better in some cases.

The situation that you've mentioned here is quite interesting. Would you also be posting the cursor solution for this?
Post #1023292
Posted Friday, November 19, 2010 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 6,098, Visits: 8,367
puja63 (11/18/2010)
@Hugo:
I'm not an expert in SQL. I was also under the impression that Cursors are a big NO and hence should be avoided at all costs. I'm only saying that after reading numerous articles which states that. Now I'm really curious to see your explanation and to learn why Cursors may be better in some cases.

The situation that you've mentioned here is quite interesting. Would you also be posting the cursor solution for this?

Hi Puja63,

First, let me reinforce that "avoid cursors" is a good rule of thumb. This is not true of all databases, but SQL Server is heavily optimized for set-based processing. In 99.9% (estimation backed only by personal observation) of all cases, a set-based solution runs rings around any row-based attempt. (To avoid misunderstanding - set-based means that one [or possibly a few] queries are used that process all qualifying rows at once; row-based means that a cursor or other technique is used that to process qualifying rows one by one - on this site, the term RBAR [Row By Agoniziing Row], coined and popularized by Jeff Moden, is often used for row-based).

In practice, most cursors are not implemented after considering possible set-based alternatives, but because a developer with a 3GL backgrund found that instrument to fit the mindset he/she is accustomed to. Not newbie developers, but newbie SQL developers. I'd say that a cursor is actually an instrument that should be reserved for the experts. Not because coding a cursor is hard, but because deciding when this is indeed the best alternative is hard.

The problem is that the "avoid cursors" advise is often misunderstoood and misreprersented in two ways, And both have occured in this discussion.

1) Some people extend the advise to "avoid row-based processing always and in all circumstances". That does no justice to the 0.1% of cases where the cursor is indeed the best alternative.

2) And some people take the "avoid cursors" too literally, without realising that it actually means to "avoid all types of row-based processing". They replace a cursor by a WHILE loop that basically does the same, thinking they have followed best practice. And if they actually do performance measurements (most probably don't), they might even feel reinforced. However, that is not because a WHILE loop performs better than a cursor, but only because a WHILE loop (if coded with care) performs better than a cursor with default cursor options.

A few years back, I have compared all possible cursor options to find the performance differences between the various possiblities. If you are interested, then please read these articles:
* Curious cursor optimization options
* Poor men see sharp - more cursor optimization
Note however that I later found that my research was incomplete. My conclusion that FAST_FORWARD is never faster than a STATIC FORWARD_ONLY READ_ONLY cursor is only correct for cursors that process an amount of data that fits entirely in the available cache. As soon as the amount of data processed by the query exceeds available cache, FAST_FORWARD starts to shine. I guess I should take the time to do an update of that article.

I'd say that the cursor code for the challenge posted should be pretty straight-forward. But since you ask, I'll post the code. Just not now, I'm a bit pressed for time. Feel free to remind me if you think I've forgotten my promise.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1023399
Posted Friday, November 19, 2010 9:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
I guess I will concede to your view of cursors because I don't have time to take your challenge. I have read many articles on cursor vs set vs while loop. In each article, the cursor loses. I would say I can't conclude that in every case a while loop is better than a cursor, but I would say that there is always a way to turn something into a set based solution, even if you have to use # tables to do it. I would imagine this depends on how many rows you need to process. There's a great set of articles on cursor vs set (and ... maybe while loop, can't remember) done by Itzik Ben-Gan from SQL Server Mag a few months back. He basically finds a way to turn the schema into something that works with set.

I was trying to find articles on how a cursor actually works as far as memory allocation and other junk a cursor does, and I couldn't find any with the 10 or so minutes I have to reply. I did find an article that states a cursor locks the entire set of data it's set up to work with. A while loop does not do this. There's one benefit a while loop has. The article, although possibly not an authority on SQL - I have no idea, is here: http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx.

Hugo if you could supply some links on how cursors work behind the scenes I would much appreciate it. It should be easy to judge if the cursor is more heavy handed than the while loop which is better to use. Currently I have other fish to fry such as implementing the MERGE with single row processing on error found in this previous article: http://www.sqlservercentral.com/articles/Database+Design/70796/, which I plan on using a while loop to accomplish. Also I need to tackle data collector, RML utilities, buffing up on SSAS...basically studying cursors is really at the bottom of my priority list, fortunately or unfortunately, depeding on your perception of cursors.

EDIT: had to fix my links


-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1023638
Posted Friday, November 19, 2010 12:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 6,098, Visits: 8,367
amenjonathan (11/19/2010)
I guess I will concede to your view of cursors because I don't have time to take your challenge. I have read many articles on cursor vs set vs while loop. In each article, the cursor loses. I would say I can't conclude that in every case a while loop is better than a cursor, but I would say that there is always a way to turn something into a set based solution, even if you have to use # tables to do it. I would imagine this depends on how many rows you need to process. There's a great set of articles on cursor vs set (and ... maybe while loop, can't remember) done by Itzik Ben-Gan from SQL Server Mag a few months back. He basically finds a way to turn the schema into something that works with set.

If you can turn a row-based solution in a set-based solution, you (nearly) always gain performance. That is absolutely true. And Itzik is a true genius in finding creative ways to combine SQL Server's features to let it do amazing things. But I am sure that in some cases, an efficient set-based solution is simply impossible, even for the likes of Itzik.


I was trying to find articles on how a cursor actually works as far as memory allocation and other junk a cursor does, and I couldn't find any with the 10 or so minutes I have to reply. I did find an article that states a cursor locks the entire set of data it's set up to work with. A while loop does not do this. There's one benefit a while loop has. The article, although possibly not an authority on SQL - I have no idea, is here: http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx.


I have just finished reading the article you mention. I have no idea who wrote it either, but I can tell you that it is seriously flawed.

1) Cursors blocking tables? My foot!
Since two of the three major cursor types are designed to be able to respond to changes made to the table while processing the cursor, and the third one works under the cover very similar to the temp table/WHILE approach, I can assure you that a cursor should not lock the entire table, and definitely not during the entire processing. If you, or everyone else, ever saw that happening, the cause must have been either bad code, or a bug in the product. It just happens that I was involved in another discussion earlier this month, where someone else ("ramireddy") and I both posted some sample code to show the effect of cursor options on concurrent modifications. None of these involve blocking. The link to this discussion is below.

2) Efficient code? Nope.
The sample code presented is awful. The cursor code uses default options - I have already told you that those are bad news. Yes, you can beat performance of THAT cursor with a WHILE. Just like you can also go faster in a 2CV than in a Lamborghini - if you step out of the Lamborghini and push, but use the engine of the 2CV.
And the WHILE code is equally awful. No PRIMARY KEY on the table. This will result in a full table scan for each iteration of the loop. Try this with a ten-million row result set, and weep as your server grinds to a halt.

3) User-defined functions? Please not.
The "alternative" to use user-defined functions is almost worse. The optimizer has no choice but to call the UDF once for each qualifying row. And then access the table used in the UDF for that row. Performance will seriously suffer. For the case presented, the better alternative would have been to join the Customer table with a derived table or CTE that aggregates the Sales, and then use a CASE expression to find the discount.

I tried to check the forum discussion, but found the link to be broken. But please, don't take any of the presented advise serious. Not everything in that article is incorrect, but enough is to warrant discarding it as a whole.


Hugo if you could supply some links on how cursors work behind the scenes I would much appreciate it. It should be easy to judge if the cursor is more heavy handed than the while loop which is better to use.


In my previous post, I already gave the links to my two blog posts on cursors - again, they should be corrected, as the results presented are only accurate for queries that process less data than fits in cache.

The discussion I mentioned before, on Microsoft's MSDN forums, includes some background on the internals of query processing, and links to other articles with more background. (I don't agree for 100% with those articles, which is why I contributed to the discussion as well).
You'll find it at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/13a38f34-ec01-48c5-928a-24d95bdd1fb4/.
Note that some of the posts made at the start of the discussion are not entirely accurate, so please read the entire discussion.


Currently I have other fish to fry such as implementing the MERGE with single row processing on error found in this previous article: http://www.sqlservercentral.com/articles/Database+Design/70796/, which I plan on using a while loop to accomplish.


That link takes me to an article about compression. But I guess that it would be off-topic for this discussion anyway.
Good luck with all your other tasks. I can't really find fault with your priorities - but do save the links I provided, in case you ever find yourself in a sitution where you do need to implement row-based processing.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1023772
Posted Friday, November 19, 2010 2:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
Ah here's the appropriate MERGE link. I've just built my version of the sproc, but am still testing it.

http://www.sqlservercentral.com/articles/MERGE/71396/

Thanks for the links. I'll definitely check them out.

Personally I still don't like cursors. Most of the time I can find a set-based solution. If not, a while loop just seems easier. Maybe if one of my while loops comes up as a hog during resource monitoring I'll check cursors out more. Until then (and other fish are fried and eaten), no cursors for me and hopefully not from my co-workers.


-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1023817
Posted Friday, November 19, 2010 3:44 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 2,916, Visits: 1,852
In the rare cases when I've felt the need for RBAR I've found that in some cases cursors are faster than WHILE loops but only when I don't have to worry about concurrency.

LinkedIn Profile
Newbie on www.simple-talk.com
Post #1023857
Posted Tuesday, December 21, 2010 8:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 820, Visits: 2,117
I'll offer:
* Indexed views, when used with care, are an invaluable performance tool.
* Triggers are great to guard consistency, but only when standard constraints fall short.
* Cursors, though heavily overused by new users, certainly have their place in the expert's tool box.


Well stated. I have yet to need a proper cursor (some consider the WHILE loop a cursor, and I use it to populate my date dimensions) but I have it in the back of my mind. I've seen too many use triggers and cursors w/o regard for other possibilities, ultimately to the detriment of the system.



Post #1037770
Posted Friday, May 10, 2013 1:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 3,084, Visits: 3,195
Hi

Indexed views can improve performance significantly if you have queries that combine large volumes of data with aggregates. It should not be created against tables where lot of modifications or changes are happening. This is because indexed views have to be maintained when there are changes happening to base data.


The large volumes of data is not the only precondition of using the indexed views. I met a case where a view didn't have a large volume of data, but was created on many tables (many joins) and in that case Indexed views show a very good performance too.

Thanks in any case for your article.
Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1451457
Posted Friday, May 10, 2013 12:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:28 AM
Points: 34, Visits: 183
Thank You so much for this article.
It indirectly solved a long standing performance problem I have with a third party application.

There are 2 queries with useless Where clauses with likes and <> ''
and an order by clause on LEN(fieldname) Desc.
The queries have no chance of using any index at all. The table has grown to over 80,000 records. The procedure executes around 20,000 each day.

I had suggested adding a computed column to the table many months ago but was told we are not allowed to change the source.

So after reading your article I created a view with schemabinding on the table in question only on the required fields for the queries and added the computed column LEN(FieldName)
Then created an unique clustered index on the computed column of the view.

Overall cost of the query has dropped from 3.5998 to 0.00315. Wow it has saved us 30% to 50% CPU since implementing the view. And best of all I have not touched their code!

Now do I bother telling the Software vendor???

Regards
Post #1451713
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse