Optimizing Store Procedure and Avoiding Cursors....

I'm trying to optimize an ETL batch procedure which takes an extremely long
time to run. I was using cursors for most part of the batch script on
different Store Procedures. Now I'm trying to remove the cursors and replace
them with alternative methods such as using temp tables.
I'm really puzzled as to why the cursor procedure runs much faster than
using temp tables... can anyone point out what is wrong to me?
Below is a snippet of 2 test SPs I've compared against.
TestTable holds 200000 records with 3 columns
1. Using Cursors (This procedure took 4 - 8 secs to run)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare test_cursor cursor for select header, body, footer from testTable
order by header
open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
set @.test = 0
while @.@.fetch_status = 0
begin
set @.test = @.test + 1
fetch next from test_cursor into @.header, @.body, @.footer
end
select @.test
close test_cursor
deallocate test_cursor
2. Using Temp Tables (This procedure took > 1 min)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare @.count int, @.loopcount int
select identity(int,1,1) id, header, body, footer into #temp from testtable
select @.count = @.@.rowcount
set @.loopcount = 1
while @.loopcount <= @.count
begin
select @.header = header, @.body = body, @.footer = footer from
#temp where id = @.loopcount
set @.loopcount = @.loopcount + 1
end
drop table #tempLooking at the code I think it maybe the the where clause in the tempdb,
rember that it has to search through all 200000 records whereas with the
cursor your going through them from start to finish, no searching.
Peter
"Happiness is nothing more than good health and a bad memory."
Albert Schweitzer
"Nestor" wrote:

> I'm trying to optimize an ETL batch procedure which takes an extremely lon
g
> time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and repla
ce
> them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from testtab
le
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>
>|||Both 1 and 2 are cursors! Looping through a temp table is just a cursor
in disguise. Unfortunately your sample code doesn't do anything useful
so it's impossible to suggest an alternative set-based solution without
using cursors. Your goal should be to avoid processing one row at a
time by ANY method.
If you need more help please post DDL, sample data INSERTs and show
your required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello Nestor,
Going through your code, I dont see any reason why the #temp based approach
should be any better
than the Cursor based approach (if it is supposed to be).
The #temp based approach, Loops anyway with a while Loop which is simillar
to Cursor based method.
Normally, when we say we have to get rid of the cursor, we mean to replace
the Cursor based approach
with a SET BASED APPROACH and your #temp based approach still does record by
record. And as Peter says
for each iteration a select is executed against the table.
Hope this helps.
Gopi
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Although I haven't tested it, I'd expect the pseudo-cursor would perform
much better if you add a unique clustered index on the #temp id column.
However, as David pointed out, changing a cursor to another iterative
technique isn't the ideal solution and might not be any faster than the
cursor.
We can't make any specific recommendations because the example you posted
does no productive work. If your actual process does something like execute
another proc for each row returned, the set-based solution would be to use
in-line set-based processing instead. This technique is often orders of
magnitude faster than an iterative approach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Dan,
Could you please share with me what "in-line set-based " means.
I was looking at a Stored Proc which has a nested cursors (One cursor inside
another cursor)
and the While loop of the second a SP is called with the two parameters
(each of them coming
from each of the Cursor code.
Here is the PSeudo-Code :
Declare Cursor Cursor1 Select * from table1
Open Cursor1
Fetch into @.var1
While @.@.FETCH_STATUS <> 1
Declare Cursor Cursor2 Select * from table2
Open Cursor2
Fetch into @.var2
While @.@.FETCH_STATUS <> 1
Exec Spname @.var1, @.var2
........
.....
DEALLOCATE Cursor1
DEALLOCATE Cursor2
I have noticed that the two cursors can be combined into one using
appropriate Joins.
However, I was under the impression I will still have to use a Cursor for
executing the SP
Could you please point me in the right direction if there is a way out.
Thanks,
Gopi
<< If your actual process does something like execute another proc for each
row returned, the set-based
solution would be to use in-line set-based processing instead>>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
> Although I haven't tested it, I'd expect the pseudo-cursor would perform
> much better if you add a unique clustered index on the #temp id column.
> However, as David pointed out, changing a cursor to another iterative
> technique isn't the ideal solution and might not be any faster than the
> cursor.
> We can't make any specific recommendations because the example you posted
> does no productive work. If your actual process does something like
> execute another proc for each row returned, the set-based solution would
> be to use in-line set-based processing instead. This technique is often
> orders of magnitude faster than an iterative approach.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||> However, I was under the impression I will still have to use a Cursor
for
> executing the SP
Yes, using an SP in this way forces you to use the iterative row-by-row
approach. The set-based solution is to perform whatever logic is
contained in SPname using a query against your tables so that the
entire operation is performed against the data set rather than one row
at a time. For data manipulation operations that usually has advantages
over the cursor in terms of performance, scalability and
maintainability.
David Portas
SQL Server MVP
--|||> Here is the PSeudo-Code :
Your pseudo-code effectively calls the proc for each row returned by the
Cartesian product of table1 and table2. Assuming that the executed proc
simply inserts into a third table, a working code example:
CREATE TABLE table1
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table1 VALUES(1)
INSERT INTO table1 VALUES(2)
INSERT INTO table1 VALUES(3)
CREATE TABLE table2
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table2 VALUES(4)
INSERT INTO table2 VALUES(5)
INSERT INTO table2 VALUES(6)
CREATE TABLE table3
(
Col1 int NOT NULL,
Col2 int NOT NULL,
PRIMARY KEY (Col1, Col2)
)
GO
CREATE PROC Spname
@.var1 int,
@.var2 int
AS
SET NOCOUNT ON
INSERT INTO Table3(Col1, Col2)
VALUES(@.var1, @.var2)
GO
DECLARE @.var1 int,
@.var2 int
DECLARE Cursor1 CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM table1
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.var1
WHILE @.@.FETCH_STATUS <> -1
BEGIN
DECLARE Cursor2 CURSOR LOCAL FOR SELECT * FROM table2
OPEN Cursor2
FETCH NEXT FROM Cursor2 INTO @.var2
WHILE @.@.FETCH_STATUS <> -1
BEGIN
EXEC Spname @.var1, @.var2
FETCH NEXT FROM Cursor2 INTO @.var2
END
CLOSE Cursor2
DEALLOCATE Cursor2
FETCH NEXT FROM Cursor1 INTO @.var1
END
CLOSE Cursor1
DEALLOCATE Cursor1
GO

> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
Exactly. That's the first step.

> However, I was under the impression I will still have to use a Cursor for
> executing the SP
Yes you will. This is why I suggested including the equivalent proc code
inline *instead of* executing the proc for each row. The entire cursor
script and proc code in my functional example could be replaced with a
single insert statement:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
CROSS JOIN table2 t2
Of course, a lot depends on exactly what the called proc does. Also, your
actual code probably isn't a CROSS JOIN. A simple INNER JOIN example:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
JOIN table2 t2 ON
t1.Col1 = t2.SomeColumn
If you need more help, you need to provide details on what the proc actually
does. Actual code will help.
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:%23Sx92CLJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Dan,
> Could you please share with me what "in-line set-based " means.
> I was looking at a Stored Proc which has a nested cursors (One cursor
> inside another cursor)
> and the While loop of the second a SP is called with the two parameters
> (each of them coming
> from each of the Cursor code.
> Here is the PSeudo-Code :
> Declare Cursor Cursor1 Select * from table1
> Open Cursor1
> Fetch into @.var1
> While @.@.FETCH_STATUS <> 1
>
> Declare Cursor Cursor2 Select * from table2
> Open Cursor2
> Fetch into @.var2
> While @.@.FETCH_STATUS <> 1
> Exec Spname @.var1, @.var2
> ........
> .....
> DEALLOCATE Cursor1
> DEALLOCATE Cursor2
> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
> However, I was under the impression I will still have to use a Cursor for
> executing the SP
> Could you please point me in the right direction if there is a way out.
> Thanks,
> Gopi
> << If your actual process does something like execute another proc for
> each row returned, the set-based
> solution would be to use in-line set-based processing instead>>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
>|||Thanks for pointing that out. I've got the code base from an article in
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
Am I doing it wrong with instructions from that article? In any case, what
exactly is SET BASED APPROACH? I do realise and know that SQL Server is
meant to be relational and not sequential, but I need to understand more on
how to apply the alternative first before trying to overhaul the scripts
running in the production servers.
Can anyone give me an equivalent of the cursor procedure I posted using SET
BASE APPROACH so that I can better relate?
Thanks.
Regards,
Nestor
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:e$w%23rZJJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hello Nestor,
> Going through your code, I dont see any reason why the #temp based
> approach should be any better
> than the Cursor based approach (if it is supposed to be).
> The #temp based approach, Loops anyway with a while Loop which is simillar
> to Cursor based method.
> Normally, when we say we have to get rid of the cursor, we mean to replace
> the Cursor based approach
> with a SET BASED APPROACH and your #temp based approach still does record
> by record. And as Peter says
> for each iteration a select is executed against the table.
> Hope this helps.
> Gopi
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||The article demonstrates some techniques for iteration but doesn't give
any recommendations as to when to use them. 99.99% of the time
row-by-row processing isn't necessary so those techniques shouldn't be
required. The Set Based approach means that you use SQL DML statements
(basically SELECT, UPDATE, INSERT, DELETE) that operate on a SET of
rows at a time rather than ONE row at a time. That should always be the
standard, preferred way to write code.

> Can anyone give me an equivalent of the cursor procedure I posted
using SET
> BASE APPROACH so that I can better relate?
But your cursor didn't do anything except assign a few variables. How
can we show you how to replace it if we don't know what it's supposed
to do? What we need to know is what you are actually trying to achieve
then we can suggest the right solution. It may even be that there isn't
a set-based solution to your problem - that's rarely the case but we
can't tell you until we understand the problem.
The following article explains the best way to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
I doubt, therefore I might be.
Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message .
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance .
I doubt, therefore I might be.
|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance .
> --
> I doubt, therefore I might be.
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en massé in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
--
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl
> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message :).
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
--
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance :).
--
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
>> I have found in many cases that declaring the cursor as Static can
>> often lead to increased performance but as always it depends<g>.
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance :).
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl
> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
--
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does n
ot
speak one word relational SQL with MSSQL, only cursors. I think it has a fai
r
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus
I
wondered, is there anything I can do to MSSQL that pulls out every bit of cu
rsor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application.
.
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message .
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors apprecia
te
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra
RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance .
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance .
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursor
s
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitorin
g
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a
WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

Optimize function that uses cursors

Hello,
I have a question regarding ways to optimize a function that currently uses
cursors to get some data from an SQL Database table.
I'm looking for others' opinions as we have discussed this internally in our
team but have reached to no viable conclusion yet. We are mostly interested
in ways to use SELECT INTO, UNION, or other constructs that we may have
missed instead of the cursors (but to be able to run them within a function:
SELECT INTO statement isn't allowed within a user defined function).
The function can be made recursive as there are no much recursions (we
expect no more than 10 levels of parent groups), but we have implemented it
using a WHILE look instead of recursion also for performance reasons.
Thank you in advance for your time. Any comments/suggestions will be highly
appreciated.
Basically we have a table called GroupItems with schema and data like below
(each item may have none, one, or more parent groups; the groups are also
considered items):
IDItem, IDGroup
--
3, 1
4, 2
5, 3
5, 4
6, 2
We also have defined a recursive user function that retreives all the parent
groups and ancestor groups (i.e. the parents of the parents and so on) for a
given ID (including that ID itself). The function returns a table data type
and uses a cursor to select next level of parents and their ancestors (using
a recursive call in the select of the cursor), and in the cursor look it use
s
insert into statements to add the data to the table.
For the sample data above, and using @.idItem = 5 as the parameter, the
function would return:
ID
--
1
2
3
4
5
As a picture is like a thousand words, the code for the function is like thi
s:
CREATE function dbo.GetParentItems(@.idItem int)
returns @.t table([ID] int primary key)
begin
-- insert current ID
insert into @.t ([ID]) values (@.idItem)
declare @.cnt int
select @.cnt = count(*) from @.t
declare @.more bit
set @.more = 1
-- the @.more variable will tell us when to stop
while @.more = 1
begin
-- get the direct parents of the current
items from the current @.t variable, except those parents which are already
added to the result; we store the new IDs in a temporary @.v variable
declare @.v table ([ID] int primary key)
declare @.id int
delete from @.v
declare c cursor for
select [GroupItems].[IDGroup]
from [GroupItems]
where
[GroupItems].[IDItem] in (select [ID] from @.t) and
[GroupItems].[IDGroup] not in (select [ID] from @.t)
open c
fetch next from c into @.id
while @.@.fetch_status = 0
begin
insert into @.v ([ID]) values (@.id)
fetch next from c into @.id
end
close c
deallocate c
-- now add the new IDs from @.v to the result
@.t
declare cv cursor for
select [ID] from @.v
open cv
fetch next from cv into @.id
while @.@.fetch_status = 0
begin
insert into @.t ([ID]) values (@.id)
fetch next from cv into @.id
end
close cv
deallocate cv
declare @.cntCur int
select @.cntCur = count(*) from @.t
-- if we have added no new IDs, the counts
will be the same and we stop looping
if @.cnt = @.cntCur
set @.more = 0
set @.cnt = @.cntCur
end
return
end
Thank you very much again.
Sorin DolhaSorin
Look at this example written by Itzik Ben-Gan
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
"Sorin Dolha" <SorinDolha@.discussions.microsoft.com> wrote in message
news:A70E5266-647F-4D49-A162-D608C0A5ABB3@.microsoft.com...
> Hello,
> I have a question regarding ways to optimize a function that currently
uses
> cursors to get some data from an SQL Database table.
> I'm looking for others' opinions as we have discussed this internally in
our
> team but have reached to no viable conclusion yet. We are mostly
interested
> in ways to use SELECT INTO, UNION, or other constructs that we may have
> missed instead of the cursors (but to be able to run them within a
function:
> SELECT INTO statement isn't allowed within a user defined function).
> The function can be made recursive as there are no much recursions (we
> expect no more than 10 levels of parent groups), but we have implemented
it
> using a WHILE look instead of recursion also for performance reasons.
> Thank you in advance for your time. Any comments/suggestions will be
highly
> appreciated.
> Basically we have a table called GroupItems with schema and data like
below
> (each item may have none, one, or more parent groups; the groups are also
> considered items):
> IDItem, IDGroup
> --
> 3, 1
> 4, 2
> 5, 3
> 5, 4
> 6, 2
> We also have defined a recursive user function that retreives all the
parent
> groups and ancestor groups (i.e. the parents of the parents and so on) for
a
> given ID (including that ID itself). The function returns a table data
type
> and uses a cursor to select next level of parents and their ancestors
(using
> a recursive call in the select of the cursor), and in the cursor look it
uses
> insert into statements to add the data to the table.
> For the sample data above, and using @.idItem = 5 as the parameter, the
> function would return:
> ID
> --
> 1
> 2
> 3
> 4
> 5
> As a picture is like a thousand words, the code for the function is like
this:
> CREATE function dbo.GetParentItems(@.idItem int)
> returns @.t table([ID] int primary key)
> begin
> -- insert current ID
> insert into @.t ([ID]) values (@.idItem)
> declare @.cnt int
> select @.cnt = count(*) from @.t
> declare @.more bit
> set @.more = 1
> -- the @.more variable will tell us when to stop
> while @.more = 1
> begin
> -- get the direct parents of the current
> items from the current @.t variable, except those parents which are already
> added to the result; we store the new IDs in a temporary @.v variable
> declare @.v table ([ID] int primary key)
> declare @.id int
> delete from @.v
> declare c cursor for
> select [GroupItems].[IDGroup]
> from [GroupItems]
> where
> [GroupItems].[IDItem] in (select [ID] from @.t) and
> [GroupItems].[IDGroup] not in (select [ID] from @.t)
> open c
> fetch next from c into @.id
> while @.@.fetch_status = 0
> begin
> insert into @.v ([ID]) values (@.id)
> fetch next from c into @.id
> end
> close c
> deallocate c
> -- now add the new IDs from @.v to the
result
> @.t
> declare cv cursor for
> select [ID] from @.v
> open cv
> fetch next from cv into @.id
> while @.@.fetch_status = 0
> begin
> insert into @.t ([ID]) values (@.id)
> fetch next from cv into @.id
> end
> close cv
> deallocate cv
> declare @.cntCur int
> select @.cntCur = count(*) from @.t
> -- if we have added no new IDs, the counts
> will be the same and we stop looping
> if @.cnt = @.cntCur
> set @.more = 0
> set @.cnt = @.cntCur
> end
> return
> end
> Thank you very much again.
> --
> Sorin Dolha|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Look up nested sets model for trees. Get a copy of TREES & HIERARCHIES
IN SQL. There is no need for recursion or any procedural code from the
vague narrative you posted instead of specs.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Look up nested sets model for trees. Get a copy of TREES & HIERARCHIES
IN SQL. There is no need for recursion or any procedural code from the
vague narrative you posted instead of specs.

optimization challenge

Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .

--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)

--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)

--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc

--run code : next 15 line as a batch
declare @.lp tinyint
declare @.col1 tinyint,@.col2 tinyint,@.col3 tinyint
set @.lp=1
while @.lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @.lp=0
else
begin
select top 1 @.col1 = col1,@.col2 = col2,@.col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@.col1 and col2=@.col2 and col3=@.col3
insert into abc values(@.col1,@.col2,@.col3)
end
end

--only distinct values left in trash table
select * from abc

--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tablesI know this is a cheat and I'm not exactly rising to the challenge however there is a pretty good discussion about (and links to) removing dupes here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

HTH|||Thanks:cool:

Had a look at the URL.
another cool way is to use SET ROWCOUNT with DELETE.|||While I realize that this uses a temp table, the usage is quite small and this is pretty efficient:
--trashtable for real data
create table abc
( col1 tinyint
, col2 tinyint
, col3 tinyint)

--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)

--check that there are ten rows
select * from abc

--check that there are only five distinct rows
select distinct * from abc

create table ptp_dupes
( col1 tinyint
, col2 tinyint
, col3 tinyint)

INSERT INTO ptp_dupes (col1, col2, col3)
SELECT col1, col2, col3
FROM abc
GROUP BY col1, col2, col3
HAVING 1 < Count(*)

BEGIN TRANSACTION

DELETE FROM abc
WHERE EXISTS (SELECT *
FROM ptp_dupes
WHERE ptp_dupes.col1 = abc.col1
AND ptp_dupes.col2 = abc.col2
AND ptp_dupes.col3 = abc.col3)

INSERT INTO abc (col1, col2, col3)
SELECT col1, col2, col3
FROM ptp_dupes

COMMIT TRANSACTION

SELECT col1, col2, col3 FROM abc ORDER BY col1, col2, col3

SELECT DISTINCT col1, col2, col3 FROM abc ORDER BY col1, col2, col3Unfortunately, there isn't anything I can recommend as even close to efficient that doesn't use a temp table at all. The cursor solutions are inefficient, and the code that you've shown is interesting, but not very efficient.

-PatP