InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:11:30 GMT20http://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]Miles Neale (8/14/2008)[/b][hr]The mode closed doors to certain technology the more limited the toolset to solve problems. I know there are at least three ways to do most everything but a few nanoseconds slower for 1000 executions is far less cost then extra days learning and then doing it in a way that is "perfect". As I have said before, [b][u]find a way to do it and do it[/u].[/b] Something that does it right, getting the right answer a little slower still is [b]right[/b]. With internal speeds running in the server running well past the speed of thought it is time to realize that some little technique does not matter.And 'views' being a problem? If you have even the most simplistic view of BI you have to laugh at people who think views are "wrong". Miles...[/quote]The problem with the philosophy espoused here is that you usually end up paying for the shortcuts later. Writing a query for maximum performance, even if it takes 30 seconds longer to write, means you won't have to spend a year rewriting the database later, when all of the slow, unstable, deadlockly code in it is causing the company to lose money.The speed issues with cursors are the least of the problems they cause. Lock contention is much more important. That's one example of why "good enough" sometimes isn't.The other fallacy here is that writing slow code is somehow faster. Which one of these takes longer to type:[code]update dbo.Table1set Col1 = table2.Col2from dbo.Table2where table1.col3 = table2.col1[/code]or[code]declare CurUpd cursor local fast_forward forselect Col2, Col1from Table2declare @Col2 int, @Col1 intopen CurUpdfetch next from CurUpdinto @Col2, @Col1while @@fetch_status = 0begin update dbo.Table1 set Col1 = @Col2 where Col3 = @Col1 fetch next from CurUpd into @Col2, @Col1endclose CurUpddeallocate CurUpd[/code]On the point on Views: The problem with many views is that they overdo what's needed. They are misused heavily by lazy database devs who don't understand what they are doing to the server.Here's an example:[code]create view Tables1and2and3asselect * -- Assume a dozen or more columns from each table.from Table1full outer join Table2 on Table1.Col3 = Table2.ColAfull outer join Table3 on Table2.ColX1 = Table3.ColB;gocreate proc MyProc(@Parameter_in int)asselect Col1, ColAfrom Tables1and2and3where Col2 = @Parameter_in[/code]The view selects every column from all three tables, and does nothing to them. The proc then uses the view to select two of those columns, based on a Where criterion on one other column. In the proc, Table3 isn't needed, but the join to it is still going to be done because of the view.I've seen this, and much worse, hundreds of times. Views that select from a dozen tables, including hundreds of columns, all to make writing four or five procs a little easier. But the procs will only include two or three of the columns, from two of the tables, that have a direct PK-FK relationship.It's a shortcut for the developer, at the cost of killing performance.I've taken dozens of procs, removed the view from them, written in the actual tables needed, and reduced run times from multiple seconds to fractions of a second.Can views be correct? Definitely yes. The problem is not the few views that are used correctly. The problem is the multitudes of views that break things.You mention saving a few nanoseconds per thousand runs. That's [i]not[/i] the situation anyone here is dealing with. I'm having to fix procs that take minutes to run, and by applying a few standard practices, getting rid of cursors and views, fixing Where clauses to not have inline UDFs in them, etc., taking them down to sub-second run times.If we were obsessing over a few thousandths of a nanosecond, that would be different. But what we're talking about here is crashed servers, applications that time out, deadlocks losing valuable data, etc.Claiming otherwise is just an attempt to set up a straw man.Thu, 14 Aug 2008 13:02:25 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxThe mode closed doors to certain technology the more limited the toolset to solve problems. I know there are at least three ways to do most everything but a few nanoseconds slower for 1000 executions is far less cost then extra days learning and then doing it in a way that is "perfect". As I have said before, [b][u]find a way to do it and do it[/u].[/b] Something that does it right, getting the right answer a little slower still is [b]right[/b]. With internal speeds running in the server running well past the speed of thought it is time to realize that some little technique does not matter.And 'views' being a problem? If you have even the most simplistic view of BI you have to laugh at people who think views are "wrong". Miles...Thu, 14 Aug 2008 09:32:10 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]riix (8/12/2008)[/b][hr]Grant you seem more stressed than Gail; the point of discussion was Gail earlier stmt:"Just watch the performance implications. A single statement tvf is usually fine, the multi-statement ones temd not to perform well if they are returning lots of rows and are part of other queries"and this was in answer to my prior-prior that a tvf is preferable to using the combination of a view and a controlling sp to "dynamically filter" the view.this was the context of the discussion. [b]Now you really feel running a view that returns zillions of huge rows into a stored procedure that then just selects a few from that lot (and then only just a few fields from these) - this is more effective than writing a tvf that returns just what is needed?[/b][/quote] (Emphasis added)Actually, both do the same thing, just in different ways.When a proc calls a view, it doesn't run as a nested sequence. It doesn't run the whole view, then run the proc on top of it. It just runs the proc as if the view's select were written into the proc.To test this, create a view that selects the complete contents of a table. Wrap it in a proc that selects just one row, based on the clustered index (PK if possible). Run the proc. Then run the select directly. Make sure to check stats while you do both.Wed, 13 Aug 2008 09:57:57 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]rbarryyoung (8/12/2008)[/b][hr]Wow. This obtuse defense of cursors and RBAR has more hand-waving than a Michael Jackson concert. Practically every other post here has statements to the the effect that "sometimes cursors are the best solution", starting with the original article itself, and yet none of these claims has been backed up with an example, or a pointer of a cursor-based SQL procedure that is the best solution for SQL Server in its current release. Not one.[/quote]Example I: I had a database at a prior job where daily notifications needed to be e-mailed to clients based on data in certain tables. Each client would get customized, individual data, based on what they had requested. After reviewing and testing a number of potential solutions, I decided on using SQL 2005 Database Mail. Since sp_send_dbmail can't be called on a table all at once, I had a cursor step through the query and execute that proc one at a time.I tested inserting data into the queue tables directly, without calling the proc, but that caused more problems than it solved in this case.Example II: The IS dept here regularly needs up-to-the-minute copies of production databases copied to a testing server, to test new code on current data. I wrote a proc that takes a source database name and a target database name as input parameters. It copies the most recent full backup to the test server, from the production server, restores it and renames it as the target database. Then it checks to see if there's been a diff backup since the last full backup, and does the same thing with that. Then it checks for log backups since the last diff backup, and has to restore those one at a time, and follow that with a final restore to bring the database into a usable state. I use a cursor to step through the log backups, since the Restore command doesn't work on a set of backup files.(The whole thing also uses a large chunk of dynamic SQL.)In both of those examples, a cursor seems to me to be the best solution. Do you disagree? What would you use to accomplish those goals?Wed, 13 Aug 2008 09:49:41 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]Tom Garth (8/12/2008)[/b][hr]GSquared - You are right about not leaving loose ends. I was referring to scripts that were on their way to being procs. I use the GOTOs to skip already tested parts, or not yet ready for prime-time pieces. But along with various print and select statements that I may be using, they have to come out before release to production.[/quote]I use block comments markers for that. It's much easier for me to visual notice that half the code is green than for me to notice a goto burried in the midst of paragraphs of code.Wed, 13 Aug 2008 09:38:25 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxGood debate and glad to see it's not degenerating.I have used cursors and temp tables for one-off stuff. Honestly it's been faster to build for me in some fairly small, one-time tasks to loop through things. I hate ms_foreach, so I usually roll my own for small stuff like that.However, I'd tend to agree with Barry that you don't need to do this most of the time. I don't know I'd go as far as to say never, which was the point of the editorial, but you can get around it if you look and work at it.My last comment would be to say that I've been putting together solutions for well over a decade with SQL Server and never had issues putting my objects in source control.Wed, 13 Aug 2008 07:22:48 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]GilaMonster (8/12/2008)[/b][hr][quote][b]Grant Fritchey (8/12/2008)[/b][hr]I'm on the way home, I'll try to set up a test tomorrow. [/quote]I saved you the trouble.[url]http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/[/url][/quote]Too late. I ran up a silly example and posted it above.Wed, 13 Aug 2008 06:22:16 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]riix (8/12/2008)[/b][hr]Grant you seem more stressed than Gail; the point of discussion was Gail earlier stmt:"Just watch the performance implications. A single statement tvf is usually fine, the multi-statement ones temd not to perform well if they are returning lots of rows and are part of other queries"and this was in answer to my prior-prior that a tvf is preferable to using the combination of a view and a controlling sp to "dynamically filter" the view.this was the context of the discussion. Now you really feel running a view that returns zillions of huge rows into a stored procedure that then just selects a few from that lot (and then only just a few fields from these) - this is more effective than writing a tvf that returns just what is needed?[/quote]Yeah, I am a bit more exercised than Gail. Your statement that people are just, and I'm sure I'm not quoting correctly, spreading fear uncertainty and doubt on stuff that isn't true irked me. I'm sure you're a good programmer. No one has suggested that you're being less than honest. However, you've suggested that not only do we not know what we're doing, but that we're just making crap up. Absolutely, demonstrably false.Below is a stupid simple example with very small data sets. Run it and check the times. I don't clear the cache or anything. Everything is allowed to run and can be run multiple times. In all my local tests on this tiny little data set, the multi-valued UDF ran twice as slow as the view or the single statement UDF. This is simple data, not FUD. Multi-valued UDF's are inherently slower and can lead to very serious performance problems if they have to deal with larger data sets, or, worse still, if they have to be used in joins. Now, I'm not saying NEVER use them. I'm saying you need to be extremely cautious using them and, usually, a different approach will work better. FUD that.Script:CREATE TABLE dbo.Parent(ParentId int identity(1,1),ParentDate datetime)CREATE TABLE dbo.Child(ChildId int identity(1,1),ParentId int,ChildDate datetime)DECLARE @i intDECLARE @j intSET @i = 1SET @j = 1WHILE @i < 100BEGIN INSERT INTO dbo.Parent (ParentDate) SELECT GETDATE() WHILE @j < 100 BEGIN INSERT INTO dbo.Child (ParentId ,ChildDate) SELECT @i ,GETDATE() SET @j = @j + 1 END SET @i = @i + 1ENDCREATE VIEW dbo.vJoinAS SELECT p.ParentId ,p.ParentDate ,c.ChildId ,C.ChildDateFROM dbo.Parent pJOIN dbo.Child cON p.ParentId = c.ParentIdCREATE FUNCTION dbo.SingleUDF ()RETURNS TABLEASRETURN ( SELECT p.ParentId ,p.ParentDate ,c.ChildId ,C.ChildDateFROM dbo.Parent pJOIN dbo.Child cON p.ParentId = c.ParentId)CREATE Function dbo.MultiUDF ()RETURNS @Multi TABLE(ParentId int,ParentDate datetime,ChildId int,ChildDate datetime)ASBEGININSERT INTO @Multi(ParentId,ParentDate,ChildId,ChildDate)SELECT p.ParentId ,p.ParentDate ,c.ChildId ,C.ChildDateFROM dbo.Parent pJOIN dbo.Child cON p.ParentId = c.ParentIdRETURNENDset statistics time onselect * from vJoinselect * from SingleUDF()select * from MultiUDF()set statistics time offWed, 13 Aug 2008 06:11:05 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]Ken Simmons (8/12/2008)[/b][hr]Here is an example of a cursor outperfoming a set based solution. I am not saying it can be done often, but I never say something can't be done.http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.programming/2004-09/4385.html[/quote]That's pre-2005.Tue, 12 Aug 2008 16:30:20 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]chrisleonard (8/12/2008)[/b][hr]Well, I'm not hand-waving; just type "TPC cursor" into Google and you will immediately find, as I did, a post by the wonderful Linchi Shea that announced to the world a while back that *all* of the TPC-E benchmarks on SQL Server up to that point had relied heavily on cursors. And then if you go further (as in, click one link) you will find an extended discussion between the likes of Adam Machanic and Greg Linwood. It is significant to see that two guys of this stature had the guts to admit in public that the topic is nuanced. It is also significant to understand commands such as Linwood's: "I agree that cursors are measurably slower than set based processing for simple statements such as the example you've provided, the game changes as the complexity increases."[/quote]Several points here, first Adam Machanic never says anything here that could be taken as meaning that he is affirming or validating what Linchi Shea claims. Quite the opposite, in fact, Adam is trying to get Linchi to explain how what he(?) said was possible, which Linchi never does.Secondly, Linchi never provides a verifiable example. Sure, there is an example procedure, that's step one. But there are two more steps to providing verifiable examples: table definitions and data. We still don't have those, all we have is an off-hand statement that cursors were the best choice in a certain case, which we cannot confirm and which Adam expresses frank doubts about in his own Blog.[quote]You are following the party line, but you are also simply hand-waving. We all know that there are cases, lots of them, where eliminating cursors is helpful. To claim that this proves, somehow, that cursors are always bad is simply a logical error: "if X is sometimes an improvement over Y, then Y is always wrong." That's the FUD-mongering that has become part of SQL groupthink, and to be honest it's embarrassing since database professionals are, first and foremost, logic professionals.[/quote]Please. Although I do not know you, you seem pretty smart to me and I am pretty sure that you know that sloganeering does not constitute logical or legitimate argument. "following the party line", "FUD-mongering" and "SQL groupthink" are all attempts to tar my arguments by association with negative connotations based on assumptions about both my thought processes and my affiliations which you do not know, are not in evidence here and are mostly incorrect.My position is not the "party line" as what both Microsoft and most people practice (and then preach, in order to be self-consistent) is exactly what is stated herein: that no hard and fast rules should ever apply. However, while I am no fan of arbitrary rules, a few rules make darn good sense and "Don't Use Cursors" is one of them. Why? Well, here's the thought process that has led me to this over the last 10 years:1. 99+% of all instances of Cursor usage are bad. Many are so bad that companies have to pay people like me a lot of money to remedy the problems that they cause.2. Since SQL Server 2005, I have been unable to find a single instance where Cursor usage was [i]necessary[/i], despite numerous claims to the contrary. Also despite public challenges.3. Since SQL Server 2005, I have also been unable to find a single instance where Cursors were the clear [i]best[/i] solution. Also despite public requests and challenges.4. Long experience teaching both customers and employees has taught me one thing over and over again: If I give them ANY indication that Cursors are in ANY sense acceptable, they [i]will[/i] use them. See #1 above for results. The pull to revert from set-based to procedural-based programming is like the gravity well of a black hole. Once they see it as possible, they cannot resist resorting to it.5. Although I can conceive of abstract and theoretical cases where a Cursor might be the best solution for a problem, they are all arbitrary and either purposeless or just plain stupid on a database (ex: implement a Hailstone function trace). Conclusion: Until someone can demonstrate either a case with a genuine need for cursors or where cursors are clearly the best answer, they should not be used. Even if it is "as good as" some other solution and it was easier to write, because doing so legitamizes its use for all of those 99.9% instances where it is bad and causes real harm.I do not think that that is either an unreasonable or an unfounded thought process: If it really is the best solution for some cases or necessary for some cases then Please: supply us with a verifiable example. Until then, saying that "Cursors are OK" just spreads harm throughout the SQL Server community.[quote]I know you have made lots of great contributions here, and I fully respect the time and effort you've put into doing so. [/quote]Thanks, I really do appreciate that. But I also believe that who we are shouldn't really matter. I would rather that our ideas and reasoning stand on their own, with or without us.[quote]However, I would humbly suggest that you may be assuming that a certain set of use cases are all there ever are, or that we can always drive business requirements back to a simplified set of SQL use cases, and that you may need to look beyond that limited set of use cases. Surely you don't believe that the best way to post required file IOs, send emails, trigger job executions, or do other intrinsically row-by-row operations that are not directly supported by T-SQL DML would always, in every case, preclude the use of cursors?[/quote]What I would suggest is that there are other ways to do those things and that instead of validating something that [i]invariably[/i] gets mis-used and abused, we should use those other means. We should also put all the pressure on Microsoft that we can to provide set-based means to do those things.[quote]My comment about concurrency and your comments about cursors using temp tables are actually in agreement in a way. I think we all know that cursors may use temp tables or worktables - no news there. But there are some business cases where we know that we need to do "something" to a whole bunch (millions, at least) of our "entities." In those cases, one of the first choices I have to make is do I (a) iterate over the production data, possibly inducing contention in the production table, or (b) iterate over a temporary data store of some sort. If I go for (b) I can have better control over concurrency issues without having to use NOLOCK hints, etc. So then it's a question of whether I (a) dump the data into a temp table and WHILE loop over it; (b) dump the data into a table and CURSOR over it; or (c) cursor (fast_forward, thanks) over the production table. You may say that "WHILE loops are the preferred solution" as you have before, but that's begging the original question, which was: are they always? [/quote]I think that my comments above cover WHILE vs Cursors. As for the locking issues, I really think that more sites should seriously consider using snapshot isolation. It is a much better solution for most of the cases like that.[quote]As a sidebar, this reminds me of another debate that goes on sometimes (and another item on some people's "SQL Profanities" list) : using XML to pass in rowsets. In the past we have frequently used XML to pass in bundles of rows of data, and eaten the parsing cost. We have definitely proven repeatedly that sending in bundles of rows in one call is more efficient for us in our overall workload than calling onesy procs, even though that approach would entirely eliminate XML parsing of any kind. Still, there are people that say "that's always bad!" and I always wonder why. So there's another one for the discussion.[/quote]Well I am no fan of XML or its frequent abuses. However, given that we are stuck with it (and we are not going to get anything better) this seems to me exactly the kind of use that it was designed for and an excellent solution for a persistent problem in the Application-Database interfacing (that is how to make the Business-Entity-Transaction a single transactional call to the database, instead of a drunken-walk zig-zag of calls, states, branches and id keys).[quote]And I'm not making up the legal bit. I've been prohibited from posting articles on places like SSC and SSWUG without vetting them through our legal department first, but I have been cleared to work on various projects for Microsoft. So, just because it's easier, I sanitize what I say here and stick with the Microsoft projects. We may well wind up seeing each other in Redmond some day, and we can still disagree there. :)[/quote]That's a shame, but I understand. I'll be at PASS this year if you want to buttonhole me there. :)Tue, 12 Aug 2008 16:29:10 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]jpowers (8/12/2008)[/b][hr][quote][b]riix (8/12/2008)[/b][hr]Cuz I can even use cobol.net to create store procs?:D[/quote]Ew - I never thought I'd see cobol mentioned again. Of all the languages I've had to code in(Ada, APL, Assembly, COBOL, L6, LISP, Fortran [IV, V, VII, 90], Pascal, PL1), COBOL was the most tedious.[/quote]Sometimes I think COBOL should be a four-letter word. Yes, tedious, wordy, bloated. It is amazing how long a simple COBOL program can be when compared to the same program written in almost any other language.BUT, it does a job, and does it well in many cases.:cool:Tue, 12 Aug 2008 16:08:20 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]Grant Fritchey (8/12/2008)[/b][hr]I'm on the way home, I'll try to set up a test tomorrow. [/quote]I saved you the trouble.[url]http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/[/url]Tue, 12 Aug 2008 15:14:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]riix (8/12/2008)[/b][hr]Cuz I can even use cobol.net to create store procs?:D[/quote]Ew - I never thought I'd see cobol mentioned again. Of all the languages I've had to code in(Ada, APL, Assembly, COBOL, L6, LISP, Fortran [IV, V, VII, 90], Pascal, PL1), COBOL was the most tedious.Tue, 12 Aug 2008 14:55:02 GMTnotquitexenahttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]rbarryyoung (8/12/2008)[/b][hr]Heh. You know, if you actually type "TPC Cursor" (with the quotes) into Google, the result is pretty funny.[/quote]That is pretty funny. (1 result to this thread)Tue, 12 Aug 2008 14:54:54 GMTKenSimmonshttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxHeh. You know, if you actually type "TPC Cursor" (with the quotes) into Google, the result is pretty funny.Tue, 12 Aug 2008 14:47:13 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]riix (8/12/2008)[/b][hr]Now you really feel running a view that returns zillions of huge rows into a stored procedure that then just selects a few from that lot (and then only just a few fields from these) - this is more effective than writing a tvf that returns just what is needed?[/quote]This thing is, the entire view won't be evaluated if only a few rows are required. Since a view is just a saved select statement, part of the process of parsing and compiling a query before it is passed to the query execution engine is replacing the view names with their definitionsSo, say you have a view defined as:[code]CREATE VIEW MyView1 ASSELECT Col1, Col2 FROM MyTable[/code]then you run a query that references that view, say as follows:[code]SELECT Col2 FROM MyView1 WHERE Col1 = 20[/code]The parser, as it's parsing and binding the query replaces the view name with the definition, resulting in something like this[code]SELECT Col2 FROM (SELECT Col1, Col2 FROM MyTable) AS MyView1 WHERE Col1 = 20[/code]and the view becomes internally a derived table. The optimiser will always try to push predicates down as close to the tables as possible in order to return the smallest number of rows possible, and so the predicate will very likely get pushed inside the subquery (as the two forms are equivalent), resulting in a structure something like this:[code]SELECT Col2 FROM (SELECT Col1, Col2 FROM MyTable WHERE Col1 = 20) AS MyView1 [/code]And hence only the qualifying rows (col1=20) will be fetched, and not the entire view.Now, there are times when that can't happen, normally when there are aggregates within the view.Make sense?Tue, 12 Aug 2008 14:40:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxHere is an example of a cursor outperfoming a set based solution. I am not saying it can be done often, but I never say something can't be done.http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.programming/2004-09/4385.htmlTue, 12 Aug 2008 14:33:40 GMTKenSimmonshttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxWell, I'm not hand-waving; just type "TPC cursor" into Google and you will immediately find, as I did, a post by the wonderful Linchi Shea that announced to the world a while back that *all* of the TPC-E benchmarks on SQL Server up to that point had relied heavily on cursors. And then if you go further (as in, click one link) you will find an extended discussion between the likes of Adam Machanic and Greg Linwood. It is significant to see that two guys of this stature had the guts to admit in public that the topic is nuanced. It is also significant to understand commands such as Linwood's: "I agree that cursors are measurably slower than set based processing for simple statements such as the example you've provided, the game changes as the complexity increases."You are following the party line, but you are also simply hand-waving. We all know that there are cases, lots of them, where eliminating cursors is helpful. To claim that this proves, somehow, that cursors are always bad is simply a logical error: "if X is sometimes an improvement over Y, then Y is always wrong." That's the FUD-mongering that has become part of SQL groupthink, and to be honest it's embarrassing since database professionals are, first and foremost, logic professionals.I know you have made lots of great contributions here, and I fully respect the time and effort you've put into doing so. However, I would humbly suggest that you may be assuming that a certain set of use cases are all there ever are, or that we can always drive business requirements back to a simplified set of SQL use cases, and that you may need to look beyond that limited set of use cases. Surely you don't believe that the best way to post required file IOs, send emails, trigger job executions, or do other intrinsically row-by-row operations that are not directly supported by T-SQL DML would always, in every case, preclude the use of cursors?My comment about concurrency and your comments about cursors using temp tables are actually in agreement in a way. I think we all know that cursors may use temp tables or worktables - no news there. But there are some business cases where we know that we need to do "something" to a whole bunch (millions, at least) of our "entities." In those cases, one of the first choices I have to make is do I (a) iterate over the production data, possibly inducing contention in the production table, or (b) iterate over a temporary data store of some sort. If I go for (b) I can have better control over concurrency issues without having to use NOLOCK hints, etc. So then it's a question of whether I (a) dump the data into a temp table and WHILE loop over it; (b) dump the data into a table and CURSOR over it; or (c) cursor (fast_forward, thanks) over the production table. You may say that "WHILE loops are the preferred solution" as you have before, but that's begging the original question, which was: are they always? As a sidebar, this reminds me of another debate that goes on sometimes (and another item on some people's "SQL Profanities" list) : using XML to pass in rowsets. In the past we have frequently used XML to pass in bundles of rows of data, and eaten the parsing cost. We have definitely proven repeatedly that sending in bundles of rows in one call is more efficient for us in our overall workload than calling onesy procs, even though that approach would entirely eliminate XML parsing of any kind. Still, there are people that say "that's always bad!" and I always wonder why. So there's another one for the discussion.And I'm not making up the legal bit. I've been prohibited from posting articles on places like SSC and SSWUG without vetting them through our legal department first, but I have been cleared to work on various projects for Microsoft. So, just because it's easier, I sanitize what I say here and stick with the Microsoft projects. We may well wind up seeing each other in Redmond some day, and we can still disagree there. :)Cheers,ChrisTue, 12 Aug 2008 14:08:40 GMTchrisleonardhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxI was going to throw a rock at the hornets nest and run, but I will chime in. I seem to write code a lot more "loosely" when I am writing it to use as a utility script. For example the following script is full of things would generally try to avoid in production code. It brings back the inputbuffer for the spid when run in 2000. When I shared the script, one of the questions was can it be rewritten without a cursor. I am just of the mentality that if I can execute a script in a second or so that is only used every so often, I don't really worry about it. I am including the script as a "concrete" example of where I use a cursor.CREATE TABLE #sp_who2 ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL, DBName SYSNAME NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT ) Create Table #SqlStatement(spid int,statement varchar(8000))create table #temp (x varchar(100), y int, s varchar(1000), id intidentity (1,1))INSERT #sp_who2 EXEC sp_who2 Declare @spid varchar(10)Declare @Statement varchar(8000)declare @sql varchar(1000)DECLARE SpidCursor Cursor FOR Select spid from #sp_who2OPEN SpidCursorFETCH NEXT FROM SpidCursorINTO @spidWHILE @@FETCH_STATUS = 0BEGIN SET @sql = 'dbcc inputbuffer (' + @spid + ')' insert #temp exec (@sql) Insert Into #SqlStatement Select @spid, s From #Temp where id = (Select max(id) from #Temp) FETCH NEXT FROM SpidCursor INTO @spidENDClose SpidCursorDeallocate SpidCursorSelect B.Statement, A.* from #sp_who2 A Left JOIN #SqlStatement B ON A.spid = B.spidDrop Table #TempDrop Table #SqlStatementDrop Table #sp_who2Tue, 12 Aug 2008 14:03:47 GMTKenSimmonshttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxGrant you seem more stressed than Gail; the point of discussion was Gail earlier stmt:"Just watch the performance implications. A single statement tvf is usually fine, the multi-statement ones temd not to perform well if they are returning lots of rows and are part of other queries"and this was in answer to my prior-prior that a tvf is preferable to using the combination of a view and a controlling sp to "dynamically filter" the view.this was the context of the discussion. Now you really feel running a view that returns zillions of huge rows into a stored procedure that then just selects a few from that lot (and then only just a few fields from these) - this is more effective than writing a tvf that returns just what is needed?Tue, 12 Aug 2008 14:00:19 GMTriixhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxI'm on the way home, I'll try to set up a test tomorrow. You can see a huge difference in only a few hundred rows. Multi-statement UDF's are just a bit problematic.Tue, 12 Aug 2008 13:48:38 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]Grant Fritchey (8/12/2008)[/b][hr] riix, try this, execute a query involving a JOIN to a view, a single-statement UDF and a multi-statement UDF where each returns a couple of hundred rows. Measure the responses and look at the execution plans. Then tell me how much FUD is being generated around the multi-statement UDF. Jeez.[/quote]Make that a couple hundred [b]thousand[/b]. To make it a valid test. Anything's fast with a hundred rowsTue, 12 Aug 2008 13:46:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxGail, I can tell you do softer style martial arts because your response is much more a redirect of his energies than mine would have been. Very nice.riix, try this, execute a query involving a JOIN to a view, a single-statement UDF and a multi-statement UDF where each returns a couple of hundred rows. Measure the responses and look at the execution plans. Then tell me how much FUD is being generated around the multi-statement UDF. Jeez.Tue, 12 Aug 2008 13:43:03 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]riix (8/12/2008)[/b][hr]Hmm .. if I have a situation where a multi-statement tvf is *required* then its most likely not something that I could've done in a view. [/quote]Indeed. I have, however seen in the past, people creating a 'multi statement' tvf with only a single statement in. They are two different forms of functions, but sometimes they get confused. [quote]Also, anything could be "part of other queries" so this is noise. [/quote]Not really. What I meant is that a query of the form[code]SELECT <column list> FROM dbo.fn_ComplexMultiStatementFunction(@SomeParameters)[/code]isn't an issue or a concern. When said function gets included in a more complex query and joined in to other tables, performance problems sometimes show. This is because the multi-statement tvf doesn't have statistics available for the data returned. Hence the optimiser doesn't know how many rows will be returned or what the distribution of values in those rows are. That cardinality inaccuracy can result in non-optimal query plans been created and slower-than-expected queries.Does that explain some of the reason behind the statement?[quote]Above quote is kinda what Ryan and I discussed earlier - sage-based FUD (fear, uncertainty, doubt) generation.[/quote]I'm sorry the statement didn't meet your standards of approval. What would you prefer in the future?Tue, 12 Aug 2008 13:31:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote]Just watch the performance implications. A single statement tvf is usually fine, the multi-statement ones temd not to perform well if they are returning lots of rows and are part of other queries[/quote]Hmm .. if I have a situation where a multi-statement tvf is *required* then its most likely not something that I could've done in a view. Also, anything could be "part of other queries" so this is noise. Finally, the entire idea of having a stored proc around just to "dynamically filter" the output of a view is as silly as cobol.Above quote is kinda what Ryan and I discussed earlier - sage-based FUD (fear, uncertainty, doubt) generation.(Ryan - close your eyes!).And why SQLCLR? Cuz it works? Cuz in our db's it performs more than adequate for our business needs? Cuz its easier to create/manage? Cuz I put everything (sp, trigs, funcs, etc.) into source-code repository? Cuz I can version everything along with my apps? Cuz I can even use cobol.net to create store procs?:DTue, 12 Aug 2008 13:18:03 GMTriixhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]chrisleonard (8/12/2008)[/b][hr]In asking for an example of someplace where cursors were a preferred solution, did the referenct to TPC benchmarks not count? [/quote]No, because you did not (and still have not) provided any actual examples. Just waving your hand in that general direction is not a demonstration of your point.[quote]But the classic use case where a cursor is defensible is the long-running, massive update that needs to (for any number of legitimate reasons) run in the background as unobtrusively as possible. You can use a T-SQL server side cursor which amounts to building a record set and then POPping through it iteratively; or you can build a WHILE loop that does a series of singleton FETCHes instead of popping results off of an opened cursor[/quote]WHILE loop is the preferred way to do this, not Cursors. Both because cursors want to make temp tables behind your back and hold onto them and because WHILE loops offer the possibility of controlling the chunk size in ways that cursors do not. Not that I normally recommend WHILE loops either, normally they are just as bad as cursors, but in this case there is a difference and WHILE is preferred.[quote]; or (in really simple cases) you can do your DML repetitively with a ROWCOUNT or TOP limit in effect.[/quote]Sure, but there is nothing about doing DML repetitively in SQL 2005 that requires a cursor.[quote] In this kind of use case, the cursor's locking can be made to be totally independent of the table data. That in and of itself can have huge, beneficial, concurrency implications.[/quote]Sorry, I do not follow you on this. Sure would be easier with an example.[quote]The reason I'm not going to give you specific examples is that I work for a great and very well-known Internet-centric company, and I am legally prohibited from doing so. [/quote]I work for hundreds of companies and I am restrained by so many NDA's that I could wallpaper my house in them. Nonetheless, I still find that when I offer opinions, I can create examples to support them without violating any of my other obligations. The key is to create definitions and data that have nothing to do with specific instances so much as general principles.Tue, 12 Aug 2008 13:10:30 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxthat's pretty much my point and the editor's. they are not no-no's.Tue, 12 Aug 2008 13:05:52 GMTryan.leutyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxThe thing is, most people on the thread, just about everyone on the thread, that suggested using TSQL cursors was a bad thing didn't say "THOU SHALT NEVER USE A CURSOR" They just said, it really depends on the use and most uses to which they are applied currently are inappropriate. I think that's true. How can that be read as a "no-no." It's a "possibly-possibly"Tue, 12 Aug 2008 13:01:03 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxthis thread is great because it proves the editor's point exactly!there are times that you should use the 'no-no's!Tue, 12 Aug 2008 12:40:52 GMTryan.leutyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxIn asking for an example of someplace where cursors were a preferred solution, did the referenct to TPC benchmarks not count? They don't get those programmers from the local staffing agency, you know ... those folks know their stuff.But the classic use case where a cursor is defensible is the long-running, massive update that needs to (for any number of legitimate reasons) run in the background as unobtrusively as possible. You can use a T-SQL server side cursor which amounts to building a record set and then POPping through it iteratively; or you can build a WHILE loop that does a series of singleton FETCHes instead of popping results off of an opened cursor; or (in really simple cases) you can do your DML repetitively with a ROWCOUNT or TOP limit in effect. In this kind of use case, the cursor's locking can be made to be totally independent of the table data. That in and of itself can have huge, beneficial, concurrency implications.The reason I'm not going to give you specific examples is that I work for a great and very well-known Internet-centric company, and I am legally prohibited from doing so. But I assure you that use cases such as the one I'm describing above do exist for us, and that cursors are sometimes an admirable solution for them. As others have noted, you can't draw any conclusions just from the fact that you can rewrite something one way or the other and see performance gains. For very hard problems involving lots of data and different concurrency requirements, I frequently code, optimize, and test both with and without cursors. I am pretty sure I can optimize the code either way reasonably well, and the DML "inside the loop" is usually the same anyway. Based on our results, I'd have to say that people who say wear the "cursors are bad" bumper sticker are just assuming a particular class of use cases, because our testing shows otherwise. Like it or not, that's the way I see it.Cheers,ChrisTue, 12 Aug 2008 12:36:54 GMTchrisleonardhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]riix (8/12/2008)[/b][hr]regarding comment:"view vs stored proc....you still need to wrap the view up in a stored proc in order use dynamic selection criteria."wrong - use a table-valued function. Much tidier and as MS says "a tvf is essentially a parameterized view".[/quote]Just watch the performance implications. A single statement tvf is usually fine, the multi-statement ones temd not to perform well if they are returning lots of rows and are part of other queries[quote]I also use SQLCLR as much as possible -:w00t: [/quote]Why?Tue, 12 Aug 2008 11:54:25 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]ryan.leuty (8/12/2008)[/b][hr]Is it better [faster, more reliable, more secure] to send a complex statement to SQL via the web app, or use a sproc?[/quote]Faster, maybe. It depends. SQL caches execution plans of adhoc SQL as well as stored procs, but not as efficiently and with less chance of reuse, unless you take pains to parametrise those queries properly.More reliable, not sure what you're getting at here.More secure. For sure. Google SQL injection and take a look. Admittedly, you can get much of the same benefit by parametrising the SQL statements rather than concatenating them together and Stored procs don't help at all if you're concatenating queries together inside.The other advantage of stored procs is modularity. It means that your DBAs can optimise the stored procs, or even change the underlying table definitions and the procs, and they don't have to touch your web code at all.Tue, 12 Aug 2008 11:49:13 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]riix (8/12/2008)[/b][hr]why not add in diagrams and foreign key constraints? surely there's a perf penalty to consider when every fk is constantly yet needlessly checked by the dbms when the CUD (who cares about R) is done by stored procs and the db is secure against external changes.[/quote]The optimiser can use the foreign keys to generate more efficient execution plans in some cases. It's more information about the data for the optimiser and often more information means more optimal queries.More on that: [url]http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/07/foreign-keys-are-our-friends.aspx[/url]Tue, 12 Aug 2008 11:40:49 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxregarding comment:"view vs stored proc....you still need to wrap the view up in a stored proc in order use dynamic selection criteria."wrong - use a table-valued function. Much tidier and as MS says "a tvf is essentially a parameterized view".and just in case you think this to be sage advice, I also use SQLCLR as much as possible -:w00t: - much to the shock and horror of almost every dba I ever meet.Tue, 12 Aug 2008 09:59:53 GMTriixhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxthanks folks :) glad to hear i'm not way off-base!sages be damned.Tue, 12 Aug 2008 09:37:48 GMTryan.leutyhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxOops, almost forgot: Dynamic execution also provides Turing-completeness.Tue, 12 Aug 2008 09:36:01 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspxhey Ryan I know what u mean - stepping into dba shoes seems often like moving into the Dark Arts or something - tons of sage knowledge from all directions by persons smarter that me that totally defeat any attempt I attempt to move in any direction except lost. Now toss into that the tons of books these sages have penned in their valiant efforts to confuse me further (and possibly also to generate generous income) .. well daunting to say the least. Good luck, don't fix it if it ain't broke, and set yourself up a bunch of test databases and experiment like crazy on weekends and when the sages are otherwise asleep .. :)Tue, 12 Aug 2008 09:34:53 GMTriixhttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]magarity kerns (8/12/2008)[/b][hr][quote][b]gserdijn (8/12/2008)[/b][hr]But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...[/quote]Whoa there! SQL would not be Turing complete without GOTO - don't be so quick to toss it![/quote]This is NOT true. Both iteration and recursion also provide Turing-completeness.Tue, 12 Aug 2008 09:34:27 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]gserdijn (8/12/2008)[/b][hr]But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...[/quote]Whoa there! SQL would not be Turing complete without GOTO - don't be so quick to toss it!But why the heck are views in the list of no-no's? Forget the hand wringing about cursors; views are wonderful things especially when you have to present base data differently on the DB side to make something like Business Objects run well.Tue, 12 Aug 2008 09:31:53 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic550718-263-1.aspx[quote][b]ryan.leuty (8/12/2008)[/b][hr]I don't know:hehe:......Is it better [faster, more reliable, more secure] to send a complex statement to SQL via the web app, or use a sproc?Should a sproc handle the joins of 6 or 7 tables, or should a view be used if I only need 1 or 2 columns from each table?...[/quote]It is more manageable, more reliable and more secure to use stored procs rather than SQL from the web app. (in my opinion)view vs stored proc....you still need to wrap the view up in a stored proc in order use dynamic selection criteria.Tue, 12 Aug 2008 09:31:18 GMTShaun McGuile