Performance issue - 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

SQL Server Development (2000)

Performance issue

Author

Topic

anuradhay
Starting Member

41 Posts

Posted - 2004-10-21 : 00:02:27

Hi,

I have a procedure which has a while loop.

while (1=1)
begin
set rowcount 1

select @ent_id = ent_id
from history..frankfurt_report ( index = frankfurt_report_ix NOLOCK )
where status is null
select 'test',@count
if @@rowcount != 0
select @found = 1
else
select @found = 0

set rowcount 0

if (@found=1)
begin
--exec frp_frankfurt_file_transfer @ent_id
update history..frankfurt_report
set status=1,process_date=getdate()
where ent_id = @ent_id
end
else
break
select @count = @count + 1
end

frankfurt_report table is having about 10 million records.. This procedure is getting executed for a long time ( 1hr). It is taking 5 mins to process 5000 records.. How can i optimize the query

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-21 : 00:08:58

Well, don't use a loop to process one row at a time, if you can update them all in one operation:

UPDATE history..frankfurt_report SET status=1, process_date=getdate()
WHERE Status IS NULL

Since you commented out the stored procedure I assume you're not using it anymore, and the above will accomplish the same as the code you posted. If that's not the case then you'll need to post the code for that procedure in order for us to provide a better solution.

anuradhay
Starting Member

41 Posts

Posted - 2004-10-21 : 00:10:36

that is not commented

anuradhay
Starting Member

41 Posts

Posted - 2004-10-21 : 00:17:50

the proc which is called inside is like this..

if (select count(*) from history..talble1 (NOLOCK) where ent_id=@ent_id) > 0
begin
select "*************************************************"
select "************ XXX *************"
select "*************************************************"
select * from history..history..talble1(NOLOCK) where ent_id=@ent_id
end

It has five diff if blocks like this.. and volume of data in these tables are about 1 lakh.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-21 : 00:41:52

Try


select @ent_id = top 1 ent_id
from history..frankfurt_report ( index = frankfurt_report_ix NOLOCK )
where status is null

That should perform orders of magnitude faster and do the same thing if I read the code right.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

anuradhay
Starting Member

41 Posts

Posted - 2004-10-21 : 00:55:06

It is throwing syntax error.. If i say
select top 1 ent_id then it is working....i cannot assign the value to a variable when i use top.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-21 : 01:05:53

quote:
Originally posted by anuradhay

It is throwing syntax error.. If i say
select top 1 ent_id then it is working....i cannot assign the value to a variable when i use top.

then just try:
set @ent_id= (select top 1 ent_id
from history..frankfurt_report ( index = frankfurt_report_ix NOLOCK )
where status is null)

--not sure, but do you need the @ent_id to be set coz you'll be needing it somewhere? if not...

update history..frankfurt_report
set status=1,process_date=getdate()
where ent_id in (select ent_id
from history..frankfurt_report ( index = frankfurt_report_ix NOLOCK )
where status is null)

--------------------
keeping it simple...

anuradhay
Starting Member

41 Posts

Posted - 2004-10-21 : 01:20:27

top 1 is working now.. but there is not improvement in the performance. It takes 3 minute to process 1200 records.. the table has 10 million records..

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-21 : 01:47:09

quote:
Originally posted by anuradhay

top 1 is working now.. but there is not improvement in the performance. It takes 3 minute to process 1200 records.. the table has 10 million records..

what solution did you try?

--------------------
keeping it simple...

anuradhay
Starting Member

41 Posts

Posted - 2004-10-21 : 01:53:58

the sp which i am executing is taking 35 mins to process 13000 records.. i just want to know why it is taking so much of time

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-21 : 02:00:14

produce the execution plan with statistics on, this will provide you the info you need...

--------------------
keeping it simple...

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-21 : 04:51:58

Try removing the index hint and let SQL decide. With top it will do the right thing.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-21 : 08:23:56

Change this:

if (select count(*) from history..talble1 (NOLOCK) where ent_id=@ent_id) > 0

To this:

if exists (select from history..talble1 (NOLOCK) where ent_id=@ent_id)

You're not using the actual count, you're just checking for the existence of at least one row with that value. There's no point in counting the entire table for just one row. That should help out quite a bit.

And I second the point about removing the index hints. Do not use hints unless you've measured their performance against unhinted queries.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-21 : 09:38:00

What Rob suggested is probably even more efficient but just to clarify, my suggestion of top should have been written

select top 1 @ent_id = ent_id ...

not

select @ent_id = top 1 ent_id ...

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources