April 28, 2010 at 11:10 am
I am executing a cursor that is taking about 40 seconds to insert into a temp table. When I execute the sql as a new query, the results are displayed within 3 seconds. Any idea's how I can speed up the cursor? I am using the same exact programming for the query and cursor. See Below
Query----------------------------------------------------------------------------------------------
declare @startdate datetime
declare @enddate datetime
set @startdate = '4/1/2010'
set @enddate = '4/30/2010'
select
s.fullname,s.staffkey,cs.chargekey,c.fullname,cs.clientkey, cs.units,cs.datesvc
,ctl.datetrans,cs.svcstatuslu,f.facilityname,cs.facilitylu,ctl.transtypelu,p.cptcode,
p.descr
from bil_chargeslips cs inner join bil_procedures p
on cs.prockey = p.prockey
inner join fd__facilities f
on cs.facilitylu = f.facilitykey
inner join fd__clients c
on cs.clientkey=c.clientkey
inner join fd__staff s
on cs.staffkey=s.staffkey
inner join bil_chargetranslog ctl
on cs.chargekey=ctl.chargekey
where ctl.datetrans between @startdate and @enddate
order by cs.chargekey
Cursor-----------------------------------------------------------------------------
declare staff_fgp_cursor Cursor
for select
s.fullname,s.staffkey,cs.chargekey,c.fullname,cs.clientkey, cs.units,cs.datesvc
,ctl.datetrans,cs.svcstatuslu,f.facilityname,cs.facilitylu,ctl.transtypelu,p.cptcode,
p.descr
from bil_chargeslips cs inner join bil_procedures p
on cs.prockey = p.prockey
inner join fd__facilities f
on cs.facilitylu = f.facilitykey
inner join fd__clients c
on cs.clientkey=c.clientkey
inner join fd__staff s
on cs.staffkey=s.staffkey
inner join bil_chargetranslog ctl
on cs.chargekey=ctl.chargekey
where ctl.datetrans between @startdate and @enddate
open staff_fgp_cursor
fetch next from staff_fgp_cursor into
@clinician,@staffkey,@chargekey,@clientname,@clientkey
,@units,@datesvc,@dot,@svcstatus,@loc,
@facilitylu,@transtypelu,@cpt,@descr
while @@fetch_status=0
begin
April 28, 2010 at 11:22 am
What is the logic inside the cursor?
Also do this as a rbar action is going to cost significant performance. Your question is why does the query only take 3 seconds and the cursor 40. The query doesn't have to do anything but pull back a resultset. The cursor has to do something, row-by-row. By guess is that the cursor query probably only takes 3-4 seconds to run but the logic inside the loop is where the other 37 seconds are coming from.
You want to know how do you make it go faster. Tell us what is happening in the loop and consider whether there is any way to do it as a set operation and not rbar.
CEWII
April 28, 2010 at 1:27 pm
I am not joking when I say, this looks like the example code I use to demonstrate why RBAR (row-by-agonizing-row pronounced rebar) queries are slow.
Yes, walking through a set of data one row at a time is slow. More often than not, by a very large margin, anything you're doing in the cursor can be better done using some variation of set-based programming. And no, WHILE loops are not better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2010 at 2:18 pm
Marv-1058651 (4/28/2010)
I am executing a cursor that is taking about 40 seconds to insert into a temp table. When I execute the sql as a new query, the results are displayed within 3 seconds. Any idea's how I can speed up the cursor? I am using the same exact programming for the query and cursor.
Tell you what... if you provide us with some CREATE TABLE statements for all of the affected tables, CREATE INDEX statements for the indexes on those tables, INSERT statements to populate all of those tables with some test data, and the results you are expecting to get BASED UPON the test data provided, I (and probably others) will help you get rid of that cursor, and replace it with some good set-based code. It might be helpful to include all of the code in your procedure that is between the "while @@fetch_status" and "end" statements.
BTW, how fast does the query run by itself if you remove the ORDER BY clause?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 28, 2010 at 6:07 pm
WayneS (4/28/2010)
Marv-1058651 (4/28/2010)
I am executing a cursor that is taking about 40 seconds to insert into a temp table. When I execute the sql as a new query, the results are displayed within 3 seconds. Any idea's how I can speed up the cursor? I am using the same exact programming for the query and cursor.Tell you what... if you provide us with some CREATE TABLE statements for all of the affected tables, CREATE INDEX statements for the indexes on those tables, INSERT statements to populate all of those tables with some test data, and the results you are expecting to get BASED UPON the test data provided, I (and probably others) will help you get rid of that cursor, and replace it with some good set-based code. It might be helpful to include all of the code in your procedure that is between the "while @@fetch_status" and "end" statements.
BTW, how fast does the query run by itself if you remove the ORDER BY clause?
I agree with Wayne. Providing DDL, index statements and execution plan will go a long way in helping to make this query better.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply