September 10, 2008 at 6:00 am
Hi All
I have a simple stored procedure where i am going to insert details from one table to two different tables
begin tran
select @empno=empno from emp_details_history where emp_no=@empNo and end_date is null
insert into vts1.[dbo].emp_history ---some insertion to emp_history from emp_check
select @error=@@error
if(@error<>0)
RAISERROR ('Unable to Insert - Error while Inserting in Main Table' , 16, 1)
if(@maxemptime<@daily_datetime)
BEGIN
if(@count>0)
Update empdata--table
else
insert empdata --table
END
select @error=@@error
if(@error<>0)
begin
rollback tran
RAISERROR ('Unable to Update' , 16, 1)
end
else
begin
delete from emp_check where SlNo=@Slno
select @error=@@error
if(@error<>0)
begin
rollback tran
RAISERROR ('Unable to Make Process' , 16, 1)
end
else
commit tran
end
end
The above query runs forever, tat is being called in an exe from asp.net, for every second it will be called.
We ran the profiler and saw that it consumes lot amount of time.
Kindly suggest the way i should modify this query that will enable me to execute faster.
Will implementation of sp_executesql after every insertion or updation will optimise the query
regards
cmrhema
September 10, 2008 at 6:30 am
There is so much code missing from this query, it's hard to see where you're going wrong. First, using sp_executesql is for working with dynamic TSQL. Nothing in this query looks like you need it. Generally, it won't increase performance over a well-written query (although there are some exceptions). Second, you're using old-style error trapping. Look into TRY... CATCH. That will help to clean up your code tremendously. Finally, to get some help you either have to post a significant portion of the code and/or an execution plan from the query. This vague, INSERT table..., just isn't enough information to figure out what's happening in order to help you out.
"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
September 10, 2008 at 6:37 am
Thanks Grant for the reply.
Actually the reason I did not put the whole code was the number of fields in each table.
Yes as you rightly said, there is no need for sp_executesql over here.
And as you said I will implement the try-catch and let you know
Regards
cmrhema
September 10, 2008 at 6:56 am
TRY/CATCH won't help performance, but it will make your code easier to write & maintain.
Can you post an execution plan? If possible, the actual execution plan not the estimated one.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply