June 14, 2008 at 9:26 am
All right, all right, no need to cry... You wanted all of the tests centralized - you got them. Keeping in mind that there are some very specific reasons why somes thing might be running quite a bit faster than others (such as returning a scalar value rather than a data set), and there's no effort to try to add any helper indexes, here would be averaged results over 10 runs of each method....
testType AvgRun fastestRunMaxRun Tot
Cursor Reg 00:00:36:49300:00:35:42300:00:37:51749999014.17
while loop with temp00:00:21:48000:00:18:31000:00:23:23349999014.17
Cursor Fast_FW 00:00:20:70000:00:19:68700:00:21:58049999014.17
Cursor FWO_RO_S_L00:00:15:72300:00:15:25000:00:16:64049999014.17
top 1 00:00:15:21000:00:14:72000:00:15:64349999014.17
while loop - no temp00:00:13:95700:00:13:35700:00:14:90749999014.17
Set-based aggregate00:00:05:21000:00:05:12300:00:05:34749999014.17
set-based loop 00:00:05:16300:00:04:42000:00:05:56349999014.17
CLR cursor 00:00:04:96300:00:04:39000:00:05:45049999014.17
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2008 at 9:35 am
The test scheme is here:
[font="Courier New"]DROP TABLE testresult
CREATE TABLE TestResult(
testID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
testType VARCHAR(50),
total money,
runtime DATETIME)
GO
DBCC freeproccache
DBCC dropcleanbuffers
--===== Common
DECLARE @TimeStart DATETIME
--===== Cursor
SET @TimeStart = GETDATE()
DECLARE @SomeMoney MONEY,
@SumSomeMoney MONEY
DECLARE curTest CURSOR
FOR
SELECT --TOP 100000
SomeMoney
FROM dbo.JbmTest
OPEN curTest
FETCH NEXT
FROM curTest
INTO @SomeMoney
SELECT @SumSomeMoney = 0
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SumSomeMoney = @SumSomeMoney + ISNULL(@SomeMoney,0)
FETCH NEXT
FROM curTest
INTO @SomeMoney
END
CLOSE curTest
DEALLOCATE curTest
INSERT TestResult(testtype,total,runtime)
SELECT 'Cursor Reg',@SumSomeMoney AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
--===== Common
DECLARE @TimeStart DATETIME
SET NOCOUNT ON
--===== Cursor
SET @TimeStart = GETDATE()
DECLARE @SomeMoney MONEY,
@SumSomeMoney MONEY
DECLARE curTest CURSOR FAST_FORWARD --"Firehose" cursor
FOR
SELECT SomeMoney
FROM dbo.JbmTest
OPEN curTest
FETCH NEXT
FROM curTest
INTO @SomeMoney
SELECT @SumSomeMoney = 0
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SumSomeMoney = @SumSomeMoney + ISNULL(@SomeMoney,0)
FETCH NEXT
FROM curTest
INTO @SomeMoney
END
CLOSE curTest
DEALLOCATE curTest
INSERT TestResult(testtype,total,runtime)
SELECT 'cursor fast_FW',@SumSomeMoney AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
--===== Common
DECLARE @TimeStart DATETIME
SET NOCOUNT ON
--===== Cursor
SET @TimeStart = GETDATE()
DECLARE @SomeMoney MONEY,
@SumSomeMoney MONEY
DECLARE curTest CURSOR FORWARD_ONLY READ_ONLY STATIC LOCAL --"Firehose" cursor
FOR
SELECT SomeMoney
FROM dbo.JbmTest
OPEN curTest
FETCH NEXT
FROM curTest
INTO @SomeMoney
SELECT @SumSomeMoney = 0
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SumSomeMoney = @SumSomeMoney + ISNULL(@SomeMoney,0)
FETCH NEXT
FROM curTest
INTO @SomeMoney
END
CLOSE curTest
DEALLOCATE curTest
INSERT TestResult(testtype,total,runtime)
SELECT 'Cursor FWO_RO_S_L',@SumSomeMoney AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
--===== Common
DECLARE @TimeStart DATETIME
SET NOCOUNT ON
--===== While Loop
SET @TimeStart = GETDATE()
DECLARE @SumSomeMoney MONEY,
@MyCount INT,
@Counter INT
SELECT IDENTITY(INT,1,1) AS RowNum,
SomeMoney
INTO #MyHead
FROM dbo.JbmTest
SELECT @MyCount = @@ROWCOUNT
ALTER TABLE #MyHead
ADD PRIMARY KEY CLUSTERED (RowNUM) WITH FILLFACTOR = 100
SELECT @SumSomeMoney = 0,
@Counter = 1
WHILE @Counter <= @MyCount
BEGIN
SELECT @SumSomeMoney = @SumSomeMoney + ISNULL(SomeMoney,0)
FROM #MyHead
WHERE RowNum = @Counter
SELECT @Counter = @Counter + 1
END
DROP TABLE #MyHead
INSERT TestResult(testtype,total,runtime)
SELECT 'while loop with temp', @SumSomeMoney AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
--===== While Loop no temp
SET NOCOUNT ON
DECLARE @TimeStart DATETIME
SET @TimeStart = GETDATE()
DECLARE @SumSomeMoney MONEY,
@MyCount INT,
@Counter INT
SELECT @MyCount = COUNT(*)
FROM dbo.JbmTest
SELECT @SumSomeMoney = 0,
@Counter = 1
WHILE @Counter <<= @MyCount
BEGIN
SELECT @SumSomeMoney = @SumSomeMoney + ISNULL(SomeMoney,0)
FROM dbo.JbmTest
WHERE SomeID = @Counter
SELECT @Counter = @Counter + 1
END
INSERT TestResult(testtype,total,runtime)
SELECT 'while loop - no temp',
@SumSomeMoney AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
--===== Top 1
DECLARE @TimeStart DATETIME
SET @TimeStart = GETDATE()
DECLARE @SumSomeMoney MONEY, @currentmoney money,
@currentrow INT
SELECT @SumSomeMoney = 0
SELECT TOP 1 @currentrow = someid,
@currentmoney = somemoney
FROM dbo.JbmTest
ORDER BY someid
WHILE @@ROWCOUNT > 0
BEGIN
SET @SumSomeMoney = @SumSomeMoney + ISNULL(@currentmoney,0)
SELECT TOP 1 @currentrow = someid,
@currentmoney = somemoney
FROM dbo.JbmTest
WHERE SomeID > @currentrow
ORDER BY someid
END
INSERT TestResult(testtype,total,runtime)
SELECT 'top 1',@SumSomeMoney AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
--===== Set Based "Loop"
DECLARE @TimeStart DATETIME
SET @TimeStart = GETDATE()
DECLARE @SomeMoney MONEY,
@SumSomeMoney MONEY
SELECT @SumSomeMoney = 0
SELECT @SumSomeMoney = @SumSomeMoney + ISNULL(SomeMoney,0)
FROM dbo.jbmTest
INSERT TestResult(testtype,total,runtime)
SELECT 'set-based loop',@SumSomeMoney AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
SET NOCOUNT ON
--===== CLR Cursor
DECLARE @TimeStart DATETIME
SET @TimeStart = GETDATE()
DECLARE @total money
SET @total=0;
EXEC CLRCursorSP @total=@total output
INSERT TestResult(testtype,total,runtime)
SELECT 'CLR cursor',@total AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
DBCC freeproccache
DBCC dropcleanbuffers
SET NOCOUNT ON
--===== Set-based aggregate
DECLARE @TimeStart DATETIME
SET @TimeStart = GETDATE()
DECLARE @total money
SELECT @total= SUM(SomeMoney)
FROM jbmtest
INSERT TestResult(testtype,total,runtime)
SELECT 'Set-based aggregate',@total AS Total,
CONVERT(VARCHAR(26),GETDATE()-@TimeStart,114) AS Duration
GO 10
[/font]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2008 at 9:46 am
Aw crud... I was testing Kevin Boles code from his response above, and I found an error in my own code... I'd left a SELECT TOP in some of the test code which short circuits some of the tests. I'll fix that, republish the code as an attachment, and republish the results.
My most humble apologies for the error. :blush:
--Jeff Moden
June 14, 2008 at 9:53 am
(Jeff - already found it...:). It's not in play in my tests)
update. Like I mentioned earlier, no specific helper index was in place on that last round. With two helper indexes (different algorithms use distinct ones):
This is five runs each
testType AvgRun fastestRunMaxRun Tot
Cursor Reg 00:00:35:73700:00:34:83000:00:36:61349999014.17
while loop - no temp00:00:19:15300:00:15:09700:00:24:23749999014.17
cursor fast_FW 00:00:17:32700:00:16:94000:00:17:70349999014.17
while loop with temp00:00:15:97000:00:15:09700:00:17:21749999014.17
Cursor FWO_RO_S_L00:00:13:91700:00:13:73300:00:14:19049999014.17
top 1 00:00:11:62000:00:11:31300:00:11:89349999014.17
CLR cursor 00:00:03:39700:00:03:28000:00:03:48349999014.17
Set-based aggregate00:00:00:69300:00:00:62700:00:00:78049999014.17
set-based loop 00:00:00:69000:00:00:62700:00:00:79749999014.17
I tried the following helpers:
create index ix_jbm on jbmtest(someID) include (somemoney)
create index ix_jbm2 on jbmtest(somemoney)
they each took about 400ms to build on the 1M test.
And - I know that it's not always appropriate to carry specific helper indexes. In these cases though, it would have been well worth it to just simply build into the script to build and tear down a temp index. In every case except for regular cursors (which just goes to confirm that "regular" cause are essentially resistant to external optimization), there would still have been a net gain in perf even with including the index build.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2008 at 10:02 am
Thanks, Matt... you saved me a wad of time!
--Jeff Moden
June 14, 2008 at 11:49 am
One parting gift....
Wrapping the "CLR Cursor" in a function rather than a SP shaves some minor amount off as well....Still no match for the set-based stuff WITH the helper indexes:
testType AvgRun fastestRunMaxRun Tot
CLR cursor function00:00:03:14300:00:03:09300:00:03:23349999014.17
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2008 at 8:48 pm
I apply this sample into my existing cursor, but unfortunately it slows down my speed from 15 seconds to 19 seconds. it doesn't work as metioned.
June 14, 2008 at 9:26 pm
yolip (6/14/2008)
I apply this sample into my existing cursor, but unfortunately it slows down my speed from 15 seconds to 19 seconds. it doesn't work as metioned.
That, my friend, is exactly what all the discussion on this thread is about. It's not worth doing. What IS worth doing, is finding the set based equivalent of whatever the code is doing... 🙂
--Jeff Moden
June 15, 2008 at 11:03 am
Matt: I cannot replicate your results because I cannot find the CLR cursor SP?
[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]
June 15, 2008 at 11:14 am
oops - I knew I forgot to post something....K - I will go dig it up tomorrow....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 15, 2008 at 11:32 am
thanks
[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]
June 16, 2008 at 3:48 am
Using this method, the SELECT query is executed 'n' number of times, where n is total number of records in the table. I dont think this is optimized solution. Instead we can use co-related queries or construct dynamic strings using single SELECT statement or a CROSS APPLY to avoid the use of cursors.
June 16, 2008 at 6:13 am
As requested - here's the CLR code I used. First the function:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=True, IsPrecise:=True)> _
Public Shared Function CLRCursorfn() As SqlMoney
Dim tot As Decimal = 0
Using conn As New SqlConnection("context connection=true")
Dim spipe As SqlPipe = SqlContext.Pipe
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
conn.Open()
cmd = New SqlCommand("Select SomeMoney from jbmtest", conn)
rdr = cmd.ExecuteReader()
While (rdr.Read())
tot += CType(rdr(0), Decimal)
End While
conn.Dispose()
End Using
CLRCursorfn = tot
End Function
End Class
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 6:14 am
Here's the CLR stored proc:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub CLRCursorSP(ByRef Total As SqlMoney)
Dim tot As Decimal = 0
Using conn As New SqlConnection("context connection=true")
Dim spipe As SqlPipe = SqlContext.Pipe
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
conn.Open()
cmd = New SqlCommand("Select SomeMoney from jbmtest", conn)
rdr = cmd.ExecuteReader()
While (rdr.Read())
tot += CType(rdr(0), Decimal)
End While
conn.Dispose()
End Using
Total = tot
End Sub
End Class
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 6:15 am
Oh no... Correlated sub-queries and their equivelant of Cross Apply are nothing more than a form of hidden RBAR which is usually as slow as a cursor and sometimes slower if poorly written.
--Jeff Moden
Viewing 15 posts - 181 through 195 (of 296 total)
You must be logged in to reply to this topic. Login to reply