Slow procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Slow procedure

Author

Topic

javelin
Starting Member

1 Post

Posted - 2006-02-24 : 23:42:57

I am running the following stored procedure to
set values in the same tables and also insert the current fetch in another table.

I am running this across 5 million rows approx (10 million on two tables when joined).

I am also using clustered index on the two set of tables but the performance is still slow.

CREATE procedure dbo.reconcilemod2 as
DECLARE @subno varchar(20), @b_subno varchar(20), @transdatec datetime, @durp varchar(10), @durc varchar(10),@transdatep datetime

DECLARE C1 cursor FORWARD_ONLY for

select warid.subno , warid.b_subno , warid.transdate, tel.transdate ,warid.act_duration , tel.act_duration
from iaa_histcalls_cur warid ,iaa_histcalls_prov tel where warid.subno=tel.subno
and warid.b_subno=tel.b_subno
and warid.flex_fld1 is null and tel.flex_fld1 is null
and abs(datediff( mi,tel.transdate , warid.transdate))<7

FOR UPDATE OF tel.flex_fld1,warid.flex_fld1

WHILE @@FETCH_STATUS = 0
BEGIN

open c1
fetch NEXT FROM c1 into @subno,@b_subno,@transdatec,@transdatep,@durc,@durp

begin
update iaa_histcalls_prov set flex_fld1 = 'P' where current of c1
update iaa_histcalls_cur set flex_fld1 = 'P' where current of c1
insert into dbo.recons_duration_exc (subno, b_subno,transdatep,durp, durc, transdatec ) values (@subno,@b_subno,@transdatep,@durp,@durc,@transdatec)
end
end
close c1
deallocate c1
GO

Any help Please

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-02-25 : 01:25:34

i have read an article about using a cursor...

http://www.extremeexperts.com/sql/articles/BestPractices.aspx
2. Avoid using cursors

Use cursors wisely. Cursors are fundamentally evil. They force the database engine to repeatedly fetch rows, negotiate blocking, manage locks, and transmit results. They consume network bandwidth as the results are transmitted back to the client, where they consume RAM, disk space, and screen real estate. Consider the resources consumed by each cursor you build and multiply this demand by the number of simultaneous users.

http://www.sql-server-performance.com/vk_sql_best_practices.asp
Try to avoid server side cursors as much as possible. Always stick to a 'set-based approach' instead of a 'procedural approach' for accessing and manipulating data.

hope it gives information.

Want Philippines to become 1st World COuntry? Go for World War 3...

Kristen
Test

22859 Posts

Posted - 2006-02-25 : 02:12:04

Hi javelin, Welcome to SQL Team!

Can't you just update iaa_histcalls_prov and iaa_histcalls_cur by joining them to the tables in the SELECT you are using in your Cursor definition? and then use a similar join to make the INSERT into recons_duration_exc?

I'll give you good odds that this will be an order of magnitude faster than using a cursor!

You ought to also put a transaction block around this (whether you stick with the cursor or a Set Based method) so that it is rolled back when it fails.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources