May 16, 2014 at 7:52 am
Jeff Moden (5/15/2014)
Stefan Krzywicki (5/15/2014)
Absolutely Jeff. My problem is that we're a SQL shop and he admits SQL cursors are horrible. I don't have access to other systems to show they're horrible there too. I'll probably just have to ignore it like when your drunk uncle starts going on about how much better cars were in the 70s.I don't have any white paper references but the internet is loaded with actual code examples (which, as previously stated, I think are much better than theoretical white papers). Here's one of my favorites because it's so simple.
http://it.toolbox.com/blogs/data-ruminations/the-curse-of-the-cursor-31851
Thanks, that's a great example.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 18, 2014 at 5:49 am
If a person insist in something it's always hard to change their mind.
What might be a slightly different strategy though:
Write a c.u.r.s.o.r *cough* in SQL Server and show that person, why it is so slow and how it scales (using execution plan, for instance). Ask that person to show you why the same (or similar) c.u.r.s.o.r *cough* performs much better with Oracle and ask for the execution plan - or whatever it's called for Oracle.
That way you can show that you're able to analyze and understand how a query is processed in detail when using SQL Server.
You're able to explain why it is slow on SQL Server. Is that person able to explain why it is fast on Oracle?
This might open a completely different discussion with a learning opportunity for both "sides".
May 19, 2014 at 7:50 am
One reason why developers write cursor based SQL is because they're attempting to literally re-create the step by step looping and decision based logic of a workflow diagram. Often times the business analysts are confused when they ask to see the actual SQL I'm coding and it doesn't appear to corelate to the Visio diagram from the requirements document. They want to verify individual steps and branching logic, but all they can really do is verify the inputs dataset and end result.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 20, 2014 at 8:21 am
Eric M Russell (5/16/2014)
Database engines don't execute T-SQL or PL/SQL; they compile to an execution plan and execute that. For example, in SQL Server two very different looking sql selects, one that references a view versus one that references a CTE, may actually compile to an equivalent execution plan. If you have coded a relatively simple cursor block, like a cursor that just iterates over a rowset aggregating sums, some database engines may actually be smart enough to compile this to the equivalent to a set based execution plan.I don't know this for sure, so I'm just theorizing here, but if that's not the way it works, then that's the way it could conceivably work, and that would account for why one database engine outperforms another engine by an order of magnitude when given an equivalent batch of SQL code.
I tend to agree with this direction of thought, I've always thought that it was the compilation process that led to the slow cursor performance, obviously there are advantages to set based coding (leaving the execution plan to the engine, taking advantage of implicit parallelism opportunities), but set based coding does not seem to be "turing complete", and I've always been interested in where SQL falls down when you need to specify operations in an explicit order.
I think if the entire body of a cursor procedure were compiled once into even minimally decent executable form, it would run much faster than SQL Server seems to manage.
I have a bit of history with older non relational database technology, and there was much less of a loop/language penalty, and its an interesting topic to me why with the implementation of set oriented database languages seemed to have started incurring this penalty, and I'm interested in seeing if SQL 2014's compiled procedures help in this area (I don't really know, just curious).
July 3, 2014 at 5:22 pm
One interesting illustration you can do against SQL Server and Oracle is compare the results of a cursor to a triangular join, on each system. I think we all agree triangular joins can be slow. See how much slower the cursor is on SQL to the one on Oracle, and if the performance of the triangular joins are similar. I post here a script that does running totals using both methods. Maybe triangular joins are faster on SQL Server than Oracle? Is there a trade-off there (who knows) ?
Create table #temp(rowNum int, placeholder char(1));
create table #outputA(rowNum int, sumSoFar bigint);
create table #outputB(rowNum int, sumSoFar bigint);
insert into #temp
SELECT top 20000 ROW_NUMBER() over (order by l.c1) , l.c1
from
(SELECT 'a' as c1 from sys.columns t1, sys.columns t2) as L
select * from #temp
insert into #outputA
SELECT lt.rownum , sum(rt.rownum) as sumSoFar
FROM #temp as lt inner join #temp as rt
on lt.rownum>=rt.rowNum
where lt.rownum<=20000
Group by lt.rowNum
ORDER BY lt.Rownum
/* ---------------------- */
declare @row int
Set @row=1;
declare MyCur CURSOR
for
SELECT rownum from #temp where rowNum<=20000 order by rowNum asc;
open myCur
FETCH next from myCur into @row
WHILE (@@FETCH_STATUS=0) and @row<=20000
BEGIN
insert into #outputB
select @row, sum(rowNum)
from #temp
where rowNum<=@row
Set @row=@row +1;
END
Close myCur
deallocate myCur
Select count(*) from #outputA;--delete from #output
Select count(*) from #outputB;--delete from #output
/* no rows ==> both results are the same, since counts are the same */
select * from #outputA
EXCEPT
select * from #outputB
select top 50 * from #outputA order by 1;
select top 50 * from #outputb order by 1;
----------------------------------------------------
July 12, 2014 at 5:01 pm
Just throwing in my 2 cents here, some different looping and different type of cursors. Note that the STATIC cursor is the fastest of the lot.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 5000000;
DECLARE @ITER INT = 0;
DECLARE @TVAL INT = 0;
DECLARE @INT_BUCKET INT = 0;
DECLARE @test-2 TABLE (TEST_ID INT PRIMARY KEY CLUSTERED NOT NULL);
DECLARE @timer TABLE
(
TIMER_ID SMALLINT IDENTITY(1,1) NOT NULL
,TIMER_STAMP DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME())
,TIMER_TEXT VARCHAR(128) NOT NULL
);
INSERT INTO @timer(TIMER_TEXT) VALUES('Create test set of ' + CAST(@SAMPLE_SIZE AS VARCHAR(12)) + ' entries')
INSERT INTO @test-2(TEST_ID)
SELECT TOP (@SAMPLE_SIZE)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.objects O1,sys.objects O2
INSERT INTO @timer(TIMER_TEXT) VALUES('Create test set of ' + CAST(@SAMPLE_SIZE AS VARCHAR(12)) + ' entries')
INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #1')
SELECT
@INT_BUCKET = TEST_ID
FROM @test-2
INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #1')
INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #2')
SELECT
@INT_BUCKET = TEST_ID
FROM @test-2
INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #2')
INSERT INTO @timer(TIMER_TEXT) VALUES('FAST FORWARD CURSOR ITERATIONS')
DECLARE R_SET CURSOR FAST_FORWARD FOR
SELECT TEST_ID FROM @test-2;
OPEN R_SET;
FETCH NEXT FROM R_SET INTO @TVAL
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM R_SET INTO @TVAL;
END
CLOSE R_SET;
DEALLOCATE R_SET;
INSERT INTO @timer(TIMER_TEXT) VALUES('FAST FORWARD CURSOR ITERATIONS')
INSERT INTO @timer(TIMER_TEXT) VALUES('DYNAMIC CURSOR ITERATIONS')
DECLARE R_SET CURSOR DYNAMIC FOR
SELECT TEST_ID FROM @test-2;
OPEN R_SET;
FETCH NEXT FROM R_SET INTO @TVAL
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM R_SET INTO @TVAL;
END
CLOSE R_SET;
DEALLOCATE R_SET;
INSERT INTO @timer(TIMER_TEXT) VALUES('DYNAMIC CURSOR ITERATIONS')
INSERT INTO @timer(TIMER_TEXT) VALUES('SCROLL CURSOR ITERATIONS')
DECLARE R_SET CURSOR SCROLL FOR
SELECT TEST_ID FROM @test-2;
OPEN R_SET;
FETCH NEXT FROM R_SET INTO @TVAL
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM R_SET INTO @TVAL;
END
CLOSE R_SET;
DEALLOCATE R_SET;
INSERT INTO @timer(TIMER_TEXT) VALUES('SCROLL CURSOR ITERATIONS')
INSERT INTO @timer(TIMER_TEXT) VALUES('STATIC CURSOR ITERATIONS')
DECLARE R_SET CURSOR STATIC FOR
SELECT TEST_ID FROM @test-2;
OPEN R_SET;
FETCH NEXT FROM R_SET INTO @TVAL
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM R_SET INTO @TVAL;
END
CLOSE R_SET;
DEALLOCATE R_SET;
INSERT INTO @timer(TIMER_TEXT) VALUES('STATIC CURSOR ITERATIONS')
SELECT @ITER = 0;
INSERT INTO @timer(TIMER_TEXT) VALUES('WHILE ITERATIONS')
WHILE @ITER < @SAMPLE_SIZE
BEGIN
SELECT @ITER = @ITER + 1;
END
INSERT INTO @timer(TIMER_TEXT) VALUES('WHILE ITERATIONS')
SELECT @ITER = 0;
INSERT INTO @timer(TIMER_TEXT) VALUES('WHILE += ITERATIONS')
WHILE @ITER < @SAMPLE_SIZE
BEGIN
SELECT @ITER += 1;
END
INSERT INTO @timer(TIMER_TEXT) VALUES('WHILE += ITERATIONS')
SELECT @ITER = 0;
INSERT INTO @timer(TIMER_TEXT) VALUES('GOTO ITERATIONS')
ITER_START:
SELECT @ITER = @ITER + 1;
IF @ITER < @SAMPLE_SIZE
GOTO ITER_START;
INSERT INTO @timer(TIMER_TEXT) VALUES('GOTO ITERATIONS')
SELECT
X.TIMER_STAMP
,X.EXEC_TIME
,CAST(X.EXEC_TIME AS FLOAT) / @SAMPLE_SIZE AS T_ROW
,X.TIMER_TEXT
FROM
(
SELECT
T1.TIMER_ID
,ROW_NUMBER() OVER (PARTITION BY T1.TIMER_TEXT ORDER BY T1.TIMER_ID DESC) AS TT_RID
,T1.TIMER_STAMP
,DATEDIFF(MICROSECOND,ISNULL(T2.TIMER_STAMP,T1.TIMER_STAMP),T1.TIMER_STAMP) AS EXEC_TIME
,T1.TIMER_TEXT
FROM @timer T1
LEFT OUTER JOIN @timer T2
ON T1.TIMER_ID = T2.TIMER_ID + 1
) AS X
WHERE X.TT_RID = 1
ORDER BY X.TIMER_ID;
Results
TIMER_STAMP EXEC_TIME T_ROW TIMER_TEXT
--------------------------- ----------- ---------------------- ------------------------------------------
2014-07-12 23:55:52.4190701 22002 0.0044004 Create test set of 5000000 entries
2014-07-12 23:55:52.4200702 1000 0.0002 Run full sample select into a bucket, #1
2014-07-12 23:55:52.4210702 1000 0.0002 Run full sample select into a bucket, #2
2014-07-12 23:55:52.4910742 70004 0.0140008 FAST FORWARD CURSOR ITERATIONS
2014-07-12 23:55:52.6480832 157009 0.0314018 DYNAMIC CURSOR ITERATIONS
2014-07-12 23:55:52.7650899 117006 0.0234012 SCROLL CURSOR ITERATIONS
2014-07-12 23:55:52.8290936 64004 0.0128008 STATIC CURSOR ITERATIONS
2014-07-12 23:55:54.9232133 2093120 0.418624 WHILE ITERATIONS
2014-07-12 23:55:56.9823311 2059118 0.4118236 WHILE += ITERATIONS
2014-07-12 23:55:59.9244994 2942168 0.5884336 GOTO ITERATIONS
Edit: Typo
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply