October 17, 2008 at 8:45 am
Hi,
I just want to to find a way to increase the performance of the stored procedure.
History: I am trying send a mail when my SSIS package execution finishes successfully. That mail needs to be prepared and send to a user.
I have create package and I have created stored procedure as well.
Mail preparation is done as follows in SP__
declare @msg varchar(1000)
declare @ui varchar(500)
declare @in varchar(500)
declare @name varchar(500)
declare @crdate varchar(500)
declare @tab cursor
declare @rc int
set @msg = 'The Package was successful with the following duplicates'
set @msg= @msg + char(13) + char(13)
set @tab= CURSOR FOR select UserInput, EmpNumber,Emp_Name,JoinDate
from Test_Emp_Dup where Flag = 'false'
open @tab
Fetch NEXT from @tab into @ui,@in,@name,@crdate
While @@FETCH_STATUS=0
BEGIN
set @msg=@msg + ' ' + @ui + ' ' + @in + ' ' + @name + ' ' + @crdate + char(13)
Fetch NEXT from @tab into @ui,@in,@name,@crdate
END
EXEC @rc=[dbo].[sp_send_cdosysmail] 'group_ID@server.com', 'group_ID@server.com',
'Package running status',@msg
-- Here am updating the Flag as True after send the mails in Test_Emp_Dup table
I am using a cursor for this message preparation as I need to take the values from a table and appened.
Hence it is recommended that not to use the cursor, the cursor consumes the lot of memory.
Is there any other way to do this task with out using the cursors?
or
can we minimize this code to improve the performance?
Thanks in advance,
Venki
Thank You
October 17, 2008 at 8:54 am
Check out this article, http://www.sqlservercentral.com/articles/Test+Data/61572/, and the discussion that goes with it as there are some other ideas in there as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 10:22 am
Other than Jack's suggestion, I'd be sure you have things indexed to pull the data. If there is a lot of data, you might cover the query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply