July 8, 2008 at 6:17 am
Hugo
Thanks for the feedback on my query
I have to admit, i haven't worked with SQL 2005 yet, so the fact that the mentioned tables in SQL2005 is being depreciated, was unknown to me :w00t:, noted though...
I'll update the like clause as suggested.
I also noted the use of sp_depends.
July 8, 2008 at 6:32 am
I got this wrong as well... I selected FAST-FORWARD... It you use STATIC this can cause temp tables to be created in tempdb, which can lock out other process and cause other issues. FAST-FORWARD will at least use some internal performance optimizations. In order to assure maximum performance I would think STATIC should be avoided.
July 8, 2008 at 6:38 am
I got this wrong as well. I've seen both STATIC & FAST_FORWARD work better in different circumstances. "It Depends" should have been the right answer on this one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2008 at 6:43 am
Thanks for the answers to my question. Looks like in my db I have 23, among sp's and triggers, using cursors, all written several years ago, which makes about 1/10 of the total. In the recent years I mostly use dynamic SQL strings sent to the db by the client application (mostly ASP.NET), which is also deprecated, but I find that managing the catalog of objects in order was becoming unconfortable as the number of sp's was increasing. Otherwhise today I'd probably have 500 stored procedures in my db.
How I'd like if MS would put a tree structure in the db object names, allowing us to create folders to keep tables, views and stored procedures reasonably ordered instead of having to scroll between hundreds of names when searching for something...
Am I the only one longing for this feature? Sorry for going a bit off-topic...
July 8, 2008 at 6:49 am
With 2005, you can use "Schemas" to achieve a little bit of that.
To a large extent, working with flat files for each object also helps a lot.
You could put these scripts in folders and use any text based tool to search, which is a lot easier (no offence...) than TSql.
I have done that for years... (please, don't ask) and was extremely pleased to see that Microsoft finaly decided to do that sort of thing too: Visual Studio Team Edition for Database Developer... which is unfortunately far too expensive :crying:
July 8, 2008 at 7:13 am
In general an interesting question, but it is simplified a little too much. Cursors are such a complicated construct with so many nested options and dependencies on the requirements, so that general statements about optimal keywords can not be made in my opinion.
As an example, the static keyword implies that you dont want to be able to see changes made after the cursor has been opened. fast_forward cursors are dynamic per default, which means that you can see changes made to the data during cursor operations.
This makes these two options not really comparable.
Best Regards,
Chris Büttner
July 8, 2008 at 7:36 am
Craig, John, Grant - thanks for the feedback.
As I've already indicated elsewhere in this discussion, I was wrong, due to incomplete testing. Both STATIC and FAST_FORWARD should be considered correct answers. I have already sent a mail to Steve, asking him to correct the question and answers, and to correct points.
July 8, 2008 at 7:41 am
The fact that BOL states "All requests to the cursor are answered from this temporary table in tempdb" for STATIC cursors led me to believe FAST_FORWARD is better, especially for large data sets, as has been noted already. Even though these type of questions are tough to answer since they depend on specific situations, it's great to see the discussions that are created from them.
July 8, 2008 at 7:42 am
davidthegray (7/8/2008)
In the recent years I mostly use dynamic SQL strings sent to the db by the client application (mostly ASP.NET), which is also deprecated
Hi David,
My main gripe with dynamic SQL is not it's deprecated (is it? I wasn't even aware of that!), but that, unless you exercisse extreme care, you run the risk of SQL injection. Please tell me that you at least do build a parametrised SQL statement and that all user input is only sent as paramaters, not as part of the query string!
Also, read http://www.sommarskog.se/dynamic_sql.html to find out more about what is and what is not wise when using dynamic SQL.
but I find that managing the catalog of objects in order was becoming unconfortable as the number of sp's was increasing. Otherwhise today I'd probably have 500 stored procedures in my db.
With proper naming conventions, and by using schemas as Emamet suggests, managing 500 stored procedures doesn't sound too hard to me... YMMV
July 8, 2008 at 7:47 am
Christian Buettner (7/8/2008)
In general an interesting question, but it is simplified a little too much. Cursors are such a complicated construct with so many nested options and dependencies on the requirements, so that general statements about optimal keywords can not be made in my opinion.
Hi Christian,
That is why I specifically asked what option is required for maximum performance. Of course, in real life you are usually confronted with lots of other requirements, making it a balancing act instead of a simple yes/no question. That's why real life isn't appropriate for something such as the QotD, and simplified situations are 🙂
As an example, the static keyword implies that you dont want to be able to see changes made after the cursor has been opened. fast_forward cursors are dynamic per default, which means that you can see changes made to the data during cursor operations.
A FAST_FORWARD cursus is dynamic? Wow, I never knew that. Thanks!!!
July 8, 2008 at 7:49 am
In all of the read-only cursors that I have coded I always specify FAST_FORWARD.
I have never used STATIC as the overhead to copy the data into tempdb can be prohibitive -- never mind the system load on tempdb.
I'm not so sure that the "independent" testing is accurate. Therefore, for purposes of this question, the time it takes to create a copy of all of the data into tempdb must be included in the total cost of the cursor operation.
July 8, 2008 at 7:56 am
Just to test this, I tried the following on a table with just over 1-million rows of data:
declare @Start datetime
select @start = getdate()
declare Test cursor local static for
select col1
from dbo.table1
declare @Var int
open Test
fetch next from Test
into @Var
while @@fetch_status = 0
fetch next from Test
into @Var
close Test
deallocate Test
select datediff(ms, @start, getdate())
/*
results:
fast_forward:
24110
24203
static:
20893
20906
*/
I had used fast_forward in the past, based on what BOL says, but I'm changing that now. Thanks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 8, 2008 at 8:07 am
JohnG (7/8/2008)
I'm not so sure that the "independent" testing is accurate. Therefore, for purposes of this question, the time it takes to create a copy of all of the data into tempdb must be included in the total cost of the cursor operation.
Hi John,
Very good - one should never rely on anything found on internet, whoever the author it. (And some would probably add, especially if it's Hugo Kornelis :D)
But the blog post that the explanation links to includes full disclosure of how the tests were done. As you can see, the total running time, from declaring and opening the cursor up to and including closing and deallocating it is included. And you can of course easily copy and paste the code to double-check, or create your own test as GSquared has done.
I'm confident that if you do your own testing, you'll find the same I did - that STATIC beats FAST_FORWARD as long as all data fits in cache, but (see earlier in this thread) that FAST_FORWARD has the edge when the table takes more space than cache has available.
July 8, 2008 at 8:09 am
GSquared (7/8/2008)
Just to test this, I tried the following on a table with just over 1-million rows of data:(...)
I had used fast_forward in the past, based on what BOL says, but I'm changing that now. Thanks.
Hi GSquared,
Great. That's what I wanted to achieve with this QotD.
But do please read the rest of the thread as well. Someone pointed out to me that I only tested with tables that can be completely fit in cache. Once that changes, FAST_FORWARD seems to be the better choice (based on my, so far rather limited, tests).
July 8, 2008 at 8:20 am
My main gripe with dynamic SQL is not it's deprecated (is it? I wasn't even aware of that!), but that, unless you exercisse extreme care, you run the risk of SQL injection. Please tell me that you at least do build a parametrised SQL statement and that all user input is only sent as paramaters, not as part of the query string!
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. I'm a bit more relaxed validating in the intranet, where I could easily trace the author of any mischief from the log. And yes, it's not "deprecated", but there are many cons. Losing the caching of the compilation of the query plan, for instance, is my biggest concern.
With proper naming conventions, and by using schemas as Emamet suggests, managing 500 stored procedures doesn't sound too hard to me
As of today, my db is made of 186 tables, and is growing since 1995 (originally it was an Access db). Looking for a specific table takes me some time, as I have to recall which criteria I used years ago to name it (and I'm lucky because I am the only one working on that db). I should investigate using schemas, but 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 (in my opinion the folders should not become really part of the object name, but just be a way to keep things organized into mental drawers).
Viewing 15 posts - 16 through 30 (of 65 total)
You must be logged in to reply to this topic. Login to reply