July 8, 2008 at 10:46 am
bitbucket (7/8/2008)
Considering the above I would still say that BOL statement concerning Fast-Forward should have been an acceptable answer or the question should have had as a choice "It Depends"
Hi Bitbucket,
Especially after now having tested cursor performance with a table that didn't fit into cache and seeing how FAST_FORWARD suddenly gained a huge advantage there, I can only agree to that. I've mailed Steve on this a couple of hours ago, but I have no reply yet. I expect that he'll agree with me and change the question.
July 8, 2008 at 11:05 am
Yelena Varshal (7/8/2008)
What about the other BOL article that says: "Dynamic cursors open faster than static or keyset-driven cursors" . All other recommendations in this article are to use the default settings for performance
Hi Yelena,
That BOL article definitely caught me off guard. I was already ready to start submitting lots of documentation bugs, since so much in this article appears to make no sense at all...
However, I discovered just in time that this article appears to be about using cursors on the client to consume result sets, rather than using the T-SQL DECLARE CURSOR syntax. It's just too bad that the article doesn't state that more clearly, since it gets quite confusing this way. Here are some parts that give it away:
- The remark about MARS makes no sense at all for T-SQL cursors.
- "Default result sets must be used for any Transact-SQL statement or batch of Transact-SQL statements that will generate multiple result sets" - T-SQL cursors can only embody a single SELECT statement and can therefore never generate multiple result sets.
- "Each call to an API fetch function or method causes a roundtrip to the server" - T-SQL cursors do not use API calls, nor do they require roundtrips to the server since they are entirely executed in the server.
I certainly can't fault you for thinking that this advise is for T-SQL cursors, since there is no indication at all in the BOL article that it's not.
July 8, 2008 at 11:08 am
GSquared (7/8/2008)
If I ever run into a server that can't fit all the database names into RAM, I think I'll have bigger worries than a few seconds plus or minus on a cursor in a maintenance script. 🙂
:laugh:
So true.
July 8, 2008 at 1:25 pm
Hi Hugo,
To tell the truth, I was surprised too. The info in this article that I references is to the contrary of what we always say: Read Only Forward Only. The reference to MARS did make me think for a second, it should have made me think for 2 seconds 🙂 But I was completely sure without checking for Declare Cursor statement that Read Only Forward Only was a default. Also I read this article first in the installed copy of BOL and the article is under the path:
Accessing and Changing Database Data > Cursors (Database Engine) > Cursor Programming Details
so even I was puzzled by references to MARS, I thought maybe I missed something in Cursors for 2005
Yelena
Regards,Yelena Varsha
July 8, 2008 at 1:57 pm
So, I got this wrong also - was going to go with STATIC, but noooooooooo - I read BOL and changed my answer :w00t:
Anyways, I really can't test this myself but I am wondering whether or not using INSENSITIVE is any different than using STATIC? It appears to me that they do the same thing.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 8, 2008 at 4:16 pm
Jeffrey Williams (7/8/2008)
Anyways, I really can't test this myself but I am wondering whether or not using INSENSITIVE is any different than using STATIC? It appears to me that they do the same thing.
Hi Jeffrey,
Indeed, STATIC and INSENSITIVE mean the same. The diifference is that the former is part of the T-SQL extended syntax, whereas the latter is part of the SQL-92 syntax. These two syntaxes can't be mixed, so you have to use one of them for the complete DECLARE CURSOR statement. (Note that you have much more control with T-SQL extended syntax; the only reason you'd ever to use SQL-92 syntax is if you're coding for portability).
An easy way to check this, and similar questions about a cursor's properties, is by using sp_describe_cursor. See BOL for a complete example.
July 8, 2008 at 5:25 pm
Hugo,
That is what I thought it meant, thanks for the confirmation.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 9, 2008 at 1:54 am
.... in the end what was the 'right' anwer, given the original question?
July 9, 2008 at 2:19 am
Craig (7/9/2008)
.... in the end what was the 'right' anwer, given the original question?
Hi Craig,
The 'right' answer would have been "it depends". FAST_FORWARD for tables that are too large to fit into cache. STATIC for tables that are small enough to fit into cache. Unless, perhaps, this causes too much contention in tempdb on a very busy system, as suggested by someone earlier in this thread; I am not able to test this.
For the purpose of the game, I've asked Steve to reaward points to everyone who chose FAST_FORWARD, so that both FAST_FORWARD and STATIC are considered correct. I haven't seen any compelling arguments for any of the other options, so they should all be considered incorrect.
I have also asked Steve to change the question going forward, either to mark these two answers as correct, or to change the question to explicitly make it clear that the table fits in cache. (I sent this mail before I read the argument about possible tempdb contention; I now believe that the best option is to mark two answers as correct).
July 9, 2008 at 2:33 am
george sibbald (7/8/2008)
One question - on a production server surely you cannot know how much cache is available to a query and anyway it is a variable, so whether static or fast_forward is best could alter? Would a good rule of thumb be if the columns used in the cursor are small, use static, if large use fast_forward, or is that too simplistic?
Hi George,
I just saw that I forgot to reply to your post yesterday. My apologies for that. :blush:
This is indeed a very good question. Looking at just the columns would be too simplistic, I think, though it definitely is a relevant part of the equation. The other relevant part would be the number of rows. If you query 10,000 rows of 8,000 bytes, the total size is less than 80 MB, which should easily fit in the cache of your server. If you query 100 million rows of 50 bytes, the total size is 5 GB, which will probably be too much (unless you have a very beefy server and little other users).
I think that in practice, there'll be three possibilities. Two of them are simple: either the size of the result set is way below the server memory, in which case you can be sure that it fits in cache, or the result set can be expected to be much bigger than the server memory, in which case you'll certainly not expect it to fit into cache. The third category is of course the problem area 🙂
I guess only way if in doubt is to test under production conditions.
Agreed (of course - since this is the second most common truth about SQL Server, right after "it depends" :D). Especially when you consider that other people have commented in this thread that things can also change if you base a cursor on a join rather than on a single table. :unsure:
July 9, 2008 at 2:35 am
Hugo Kornelis (7/8/2008)
davidthegray (7/8/2008)
Hugo, yes sure, I'm well aware about the risks of SQL injection. I always validate the input coming from the public web site, and I'm used to use parameters in most of the cases.Hi David,
If this were my database, this would bother me a lot. Using parameters "most of the cases" means exposing yourself to injection attempts "in some of the cases". And validating helps prevent the more common and well-known injection attempts, but there will always be inventive hackers who think up new ways to circumvene the validation. Like the recent wave of injection attacks where the malicious code is injected in a binary encoded form.
Really, AFAIC there is no reason at all not to use parameters for ALL user-supplied input.
I'm wondering since several years why MS does not offer the possibility to group db object in folders, as it does with the other development tools
Part of me likes the idea, part of me doesn't. The latter part is the part that reminds me that SQL Server is a server product, and that neatly organized folders is a client function. You could of course add some grouping functionality to Management Studio, but since it'd have to be stored on the client, you'd lose it when accessing the DB from another computer. Not sure if customers would appreciate such an implementation.
However, if you think Microsoft should implement this, why not tell them so? https://connect.microsoft.com/SQLServer.
Hugo, there is one reason not to change that code to use parameters. It takes time. I'm aware of the injections attacks, I have also written here some example code that I got from my web server logs. And I have to confess, although I was proud of my old coding habits which resulted bullet proof for all these years, a couple of weeks ago an attack succeded and I had to revert to a backup of the db. (The damages were limited and I could have cleaned up everything, but it happened during the weekend so reverting to the backup only required 30 minutes of downtime. Still the wound burns in my heart!) That happened on an old .asp page which I had rechecked one week before, but I overlooked one line! What I did where I didn't want to spend time converting the code to use parameters, is to do things like: if I expect integer numbers I do a CInt() on the input string; if I expect 8 characters I do a rtrim (I defy anybody to write a SQL injection code in 8 or even 20 characters); I always do checks on the ' characters; overall, things for cheap like that.
As concerns the folders suggestion, yes of course it should be something only used for managing. I'd say it should be implemented like the db diagrams, which have no impact on the server side but are just a viewing feature. Of course I would store the folder structures in a system table, not in some Management Studio related file. I do most of my db development inside Visual Studio 2008, not in Management Studio (as you have probably understood I'm mostly what you call a 'developer', and I'm also dba as a secondary part of my job). And there are also portability reasons for including the folder structure in the system tables. I'm temped to file the suggestion to MS as you indicate, but I think this is such a basic suggestion, one of the first things that come to mind when you start dealing with a db just a little complex, that if they haven't done that yet they must have some good reasons not to do it. I'm just curious to know which ones.
July 9, 2008 at 2:48 am
davidthegray (7/9/2008)
I'm temped to file the suggestion to MS as you indicate, but I think this is such a basic suggestion, one of the first things that come to mind when you start dealing with a db just a little complex,
Hi David,
You overloook the human trait that, when working on something complex, one often tends to overlook the basic. Just today, for instance, I noticed a suggestion on Connect where Microsoft's comment includes the phrase "It's surprising how long we've gone without such a basic feature!" (See http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354766).
that if they haven't done that yet they must have some good reasons not to do it. I'm just curious to know which ones.
You might learn about them, too, by posting to Connect. Usually (though not always, unfortunately), reasons are given when a suggestion is closed as "Won't fix".
July 9, 2008 at 6:12 am
Hugo, thanks for the detailed reply, appreciated.
I think we can also say that if the result set of the cursor won't fit into cache, we shouldn't be using a cursor!
---------------------------------------------------------------------
July 9, 2008 at 6:52 am
if the result set of the cursor won't fit into cache, we shouldn't be using a cursor!
The concept isn't wrong, but a bit too broad. Cursors being cursors, I would agree that it is wise to consider alternatives but to generalize and say that it is not a proper to call a large dataset with a cursor that won't fit into cache ... did I miss a thread?
So as it stands, [I'm just making random suggestions here to make a point] we could say instead of using a large cursor, re-write the cursor to use a temp table (view - to assure caching) if processing with an complex conditional update statement... don't use a large cursor to clean up data based on random conditions... for example, a late night early morning scrub... instead, create another column to categorize the shuffle, ... never use a large cursor where a trigger can substitute ...
I think it better said - "generally" if a cursor doesn't fit in cache, look for an alternative.
Jamie
July 9, 2008 at 8:00 am
Hugo Kornelis (7/9/2008)
Hi David,You overloook the human trait that, when working on something complex, one often tends to overlook the basic. Just today, for instance, I noticed a suggestion on Connect where Microsoft's comment includes the phrase "It's surprising how long we've gone without such a basic feature!" (See http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354766).
Ok, Hugo, I followed your suggestion.
If anybody think this feature would be useful, you can vote here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355519
Viewing 15 posts - 46 through 60 (of 65 total)
You must be logged in to reply to this topic. Login to reply