Hi Guys,

 I am working on a performance tuning project  but was not able to rewrite the below given procedure with out using cursors  could any body advise , bcoz this is urgent.

Thanks in advance

CREATE  PROCEDURE [dbo].S_LPM_SUPPORT_DATA
AS

BEGIN

DECLARE  @promotion_id INT
DECLARE @STATUS VARCHAR(32)
DECLARE @upcadded INT
DECLARE @upcdeleted AS INT
DECLARE  @totalupcs as int
DECLARE  @TODAY AS SMALLDATETIME
DECLARE  @EXPORT_DATE AS SMALLDATETIME

SELECT @TODAY = CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), GETDATE()), 103)

DELETE T_LPM_SUPPORT_UPC_LIST WHERE PROCESSDATE= @TODAY
DELETE T_LPM_support_upc_count WHERE STATUS = 'EXPIRED YESTERDAY'

declare c1 cursor for

select  promotion_id, date_to_export
from  t_lpm_promotion (nolock)
WHERE dbo.F_PROMOTION_STATUS(promotion_id, @TODAY) IN ('LIVE', 'PENDING_START')
order by promotion_id

open c1

fetch next from c1
into @promotion_id, @EXPORT_DATE

while (@@fetch_status= 0)
begin

 select  @upcadded = count(*)
 from  T_LPM_support_upc_count (NOLOCK)
 where  promotion_id = @promotion_id
 and  processdate = @TODAY
 and  run_status = 2
 and  upc not in
   (select upc from T_LPM_support_upc_count (NOLOCK)
    where  promotion_id = @promotion_id
    and  run_status = 1)
     and  processdate = @TODAY

 select  @upcdeleted = count(*)
 from  T_LPM_support_upc_count (NOLOCK)
 where  promotion_id = @promotion_id
 and  processdate = @TODAY
 and  run_status = 1
 and  upc not in
   (select upc from T_LPM_support_upc_count (NOLOCK)
    where  promotion_id = @promotion_id
    and  run_status = 2)
     and  processdate = @TODAY

 select  @totalupcs =  COUNT(UPC)
 from  T_LPM_support_upc_count (NOLOCK)
 where  promotion_id = @promotion_id
 and  processdate = @TODAY
 and  run_status = 2

 select  @STATUS = STATUS
 from  T_LPM_support_upc_count (NOLOCK)
 where  promotion_id = @promotion_id
 and  processdate = @TODAY
 and  run_status = 2

 IF @UPCADDED > 0
  SELECT @EXPORT_DATE = @TODAY

 insert into T_LPM_SUPPORT_UPC_LIST
 values (@promotion_id, @upcadded, @upcdeleted, @TODAY, @STATUS, @EXPORT_DATE, @totalupcs)

 fetch next from c1
 into @promotion_id, @EXPORT_DATE

end

close c1
deallocate c1

end

GO

You could turn the four queries inside the loop into four aggregate queries (adding the "cursor table" to each FROM clause, with a JOIN) storing the results of each into separate temp tables (SQL 7.0) or table variables (SQL 2000). This way you should have totals GROUPed BY promotion_id and run_status.

You should look at the logic from the F_PROMOTION_STATUS udf, and try to implement that logic in the main query statements, not by calling the udf.

Next, you can join those temp tables and insert the combined results into the final table, using the NOT IN subqueries to suppress the ones you don't want.

Finally (SQL 7.0) you should explicitly drop the temp tables.

Hi Adriaan,

Thanks for the quick reply.  It is good option to do that way but will the table variables or the temp tables degrade the performance ...

I am using sql server 2000.

There's only one way to find out - create another sproc that uses the temp tables/table variables, and test them both.

If the number of iterations for the cursor is low, the solution with the cursor might be quicker than the set-based version. Difficult to say at what point the set-based version would be quicker.

So you need to test with large amounts of data - but realistic for what you expect to be processing in production.

Hi Adriaan ,

I will try to test the data with both the options . I have one more issue Do you have any idea regarding splitting the database files so that the application can use multithreadinig capability of the server . My sql procces now is taking around 8 hr over night  batch and is a squential process.

The sql server is a 4 processor machine and it is utilising only one cpu.

Since your cursor-based proc is doing everything one step at a time, there is no point for SQL to use more than one cpu ...