April 21, 2009 at 7:15 am
Brave, very brave article, but well done 🙂 looking forward to the rest 😀
I once used cursors :pinch: before I saw the error of my ways 😉 and changed them to set based with remarkable improvements
I will only use a cursor (and yes I have two in production :blush: ) in extreme circumstances and as a last resort and not before I have exhausted other possibilities. Will be glad when we upgrade to 2005/2008 :hehe:
Maybe someday, I can teach Jeff something.
Me too, I await that day also :w00t:
Far away is close at hand in the images of elsewhere.
Anon.
April 21, 2009 at 8:47 am
Thanks, David.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 21, 2009 at 11:20 am
David Burrows (4/21/2009)
Maybe someday, I can teach Jeff something.
Me too, I await that day also :w00t:
Like I've told others, I learn something new from each thread I read. I've learned a lot from you, Mr. Burrows and not all of it is T-SQL. Good lessons, none the less.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2009 at 11:31 am
RBarryYoung (4/14/2009)
gautamsheth2000 (4/13/2009)
Should use this code 🙂Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2
Yes indeed. Good job!
Using the offending solution took 14 seconds on a MacPro. When I ran essentially the same code as posted by gautamsheth2000, the query analyzer listed the time as " 0 SEC", i.e. less than 1 second.
As for insulting anyone's intelligence, this will come as a shock but there are other people on this earth than gurus who are using T-SQL. These will greatly benefit from such an article.
Now each and every instance where I saw a cursor-based solution, it was indeed inferior to a set-based solution. There is a genuine, widespread need for such articles. On this particulat topic especially.
So, RBarryYoung, damn the torpedoes and go full ahead.
I viewed the example as a brainteaser, not as an insult to my intelligence. I intend to keep reading all the other 14 cases and develop the set-based solution on my own before looking at the solution. I will also compare the time required to run the solutions.
And how about a few real-life cases where cursors ARE justified ? Now that would be a whole new approach, since all discussions on this topic are invariably based on examples where a cursor was a bad idea.
April 21, 2009 at 2:17 pm
j (4/21/2009)
So, RBarryYoung, damn the torpedoes and go full ahead.
Thanks J, I usually do. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 21, 2009 at 10:15 pm
Hi All,
Long time ago (14/10/2004) I asked for help in converting cursor based processing into set based TSQL statement and nobody had an answer.
Here is the chalenge:
I wrote function:
CREATE FUNCTION fnWord ( @SentenceID CHAR( 8 ),
@Sentence VARCHAR ( 100 ) )
RETURNS @TableA TABLE ( SentenceID TEXT, Word TEXT )
AS
BEGIN
SET @Sentence = @Sentence + ' '
DECLARE @WordStart INT
DECLARE @WordEnd INT
SET @WordStart = 1
SET @WordEnd = 1
WHILE @WordStart < LEN( @Sentence )
BEGIN
SET @WordStart = CHARINDEX( ' ', @Sentence, @WordStart )
INSERT @TableA
SELECT @SentenceID, CAST( SUBSTRING( @Sentence, @WordEnd, @WordStart - @WordEnd ) AS VARCHAR )
SET @WordEnd = @WordStart + 1
SET @WordStart = @WordStart + 1
END
RETURN
END
GO
I tested function with:
SELECT *
FROM fnWord('00123483','What is your given name?')
Result is:
SentenceID Word
00123483 What
00123483 is
00123483 your
00123483 given
00123483 name?
(5 row(s) affected)
To be able to apply function on data set (15,000,000 rows/ average 150 words per sentence), I used CURSOR:
SET NOCOUNT ON
DECLARE @part varchar(8)
DECLARE @ID VARCHAR(8000)
DECLARE CTest CURSOR FOR
SELECT part_no, words
FROM #word
OPEN CTest
FETCH NEXT FROM CTest
INTO @PART, @ID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #words
SELECT * FROM fnWord( @part, @ID )
FETCH NEXT FROM CTest
INTO @PART, @id
END
CLOSE CTest
DEALLOCATE CTest
Can I achieve same result WITH NO CURSOR applied???
Regards,
Milovan
April 21, 2009 at 11:12 pm
Milovan Banicevic (4/21/2009)
Can I achieve same result WITH NO CURSOR applied???
Absolutely... see the following article (Look for "One final split trick")...
http://www.sqlservercentral.com/articles/TSQL/62867/
... and the following post where many, many different solutions have been posted and tested. In fact, a new even higher speed Tally solution has come about quite by accident near the end of the post...
http://www.sqlservercentral.com/Forums/Topic695508-338-4.aspx
Your turn and I've got to ask... what is it that you're actually doing? I mean the business reason for splitting 15 million "sentences" down to words? What is the need to do such a thing? The reason I ask is because if we know the true reason for such a request, some alternate solutions bubble to the surface.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2009 at 11:22 pm
RBarryYoung (4/15/2009)
Easy, and I don't bother with anything less than a million rows:
Select TOP 1000000
ROW_NUMBER() over(order by c1.object_id) as N
into Numbers
From master.sys.columns c1, master.sys.columns c2
Ummm Barry.... select @@rowcount = 434281 (on my 2k8)
maybe...
from master.sys.columns c1, master.sys.columns c2, master.sys.columns c3
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2009 at 12:03 am
Try Master.sys.SYScolumns instead... it contains at least 11,000 rows which will produce up to 121 million rows with a single cross-join. And, although it looks fine, double cross joins are kind of tough on the local log. I wouldn't use them.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2009 at 6:44 am
WayneS: yeah, what Jeff said. Sorry 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 22, 2009 at 7:57 am
As Jeff indicated, the whole problem of splitting sentences into words can be done by the method he points to.
However, even if fnWord is treated as a 'black box', the cursor can still be eliminated .
INSERT INTO #words
SELECT f.SentenceID, f.Word
FROM #word w
CROSS APPLY fnWord(w.part_no, w.words) f
Of course, a solution based on Jeff's 'tally table' method is going to be much faster! 🙂
Derek
April 22, 2009 at 9:51 am
RBarryYoung (4/22/2009)
WayneS: yeah, what Jeff said. Sorry 🙂
Heh... no need to be sorry... it's a very common typing mistake that I occasionally make myself. The names are so bloody similar.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2009 at 3:47 pm
This is some examples of a report that we had writing out using a cursor. This is based on a very real world example and I believe the savings in execution are enough to warrant looking for ways to do things without cursors. If they can be done in a manner that is as efficient.
Cursor example:
DECLARE @objectname nvarchar(128)
DECLARE @objecttype nvarchar(60)
DECLARE @theText varchar(max)
SET @theText =''
DECLARE Object_cursor CURSOR FOR
select name,type_desc
from SYS.all_objects
where type = 'U'
union
select name, type_desc
from SYS.all_objects
where type = 'V'
order by name
Open object_cursor
FETCH NEXT FROM Object_cursor INTO @objectname,@objecttype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @theText = @theText + @objectname + @objecttype+ CHAR(13) + CHAR(10)
FETCH NEXT FROM Object_cursor INTO @objectname,@objecttype
END
CLOSE Object_cursor
DEALLOCATE Object_cursor
print @theText
The same statments not using a cursor:
DECLARE @theText nvarchar(max)
SET @theText =''
;
WITH objectcte (name,type_desc)
AS
(
select name,type_desc
from SYS.all_objects
where type = 'U'
union
select name, type_desc
from SYS.all_objects
where type = 'V'
)
SELECT @theText = COALESCE(@theText , '') + name + type_desc + CHAR(13) + CHAR(10)
FROM objectcte
order by name
print @theText
The number of selects for the cursor was 1645 and for the set-based query 2. Both returned 822 rows. Round trips were the same at 1 and packets from client was also the same at 1. The number of packets from the server was 12 for the cursor and 2 for the set-based query. Bytes sent from client for cursor was 1318. The other query 778. Bytes returned from cursor was 48254. Bytes returned from the set-based query 8090. The timing was inconsistent with at times the cursor being faster. However, when the cursor also had the single longest runtime as well. The second way should be much more scalable and as the payload would increase it should still be able to perform quickly from having to move 6 times less data in one sixth the number of packets. The efficiency should be clear. Please test the statments for yourself.
I am looking forward to the next article and wish it were here already.
Conan
April 23, 2009 at 12:21 am
DECLARE @Txt VARCHAR(MAX)
SELECT @Txt = ''
SELECT @Txt = @Txt + ao.name + ao.type_desc + CHAR(13) + CHAR(10)
FROM SYS.all_objects ao WHERE ao.type IN ('U' , 'V') ORDER BY ao.name
PRINT @Txt
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjrdb'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syspalnames'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 97, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
April 23, 2009 at 12:42 am
gserdijn (4/23/2009)
DECLARE @Txt VARCHAR(MAX)SELECT @Txt = ''
SELECT @Txt = @Txt + ao.name + ao.type_desc + CHAR(13) + CHAR(10)
FROM SYS.all_objects ao WHERE ao.type IN ('U' , 'V') ORDER BY ao.name
PRINT @Txt
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjrdb'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syspalnames'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 97, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
{insert sound of applause here} 🙂
Just to drive the point home... compare that with the cursor code for the same thing previously posted and ask yourself which is more intuitive or easy to read. :hehe: The other point is, it really doesn't take much extra knowledge to avoid cursors in situations like this. It sure as heck didn't take any longer to write, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 301 through 315 (of 380 total)
You must be logged in to reply to this topic. Login to reply