Hi
I am working in weekly Newspaper company. There are 15000 subscriber, subscribed for next 3 to 4 years.Per year 52 weeks and I have to generate issue number based on week(1001......3000) for each subscriber and store into data table. I am using mssql 2012. I have written the Store Procedure (I have given below) working fine. But it takes long time more then half an hour when execute into server. If using aspx pages, it stop working. I have also increased time out. No Use. Pls check my store procedure and advise me how to do

thank you

Maideen

My Store procedure

`

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[WR_SUB_usp_Reports_Audit] 
As
Begin 
TRUNCATE TABLE [dbo].[SUB_tbl_Reports_Audit]


    Declare @code varchar(50), @start int , @end int, @start1 int, @end1 int, @str varchar(3000),@Name varchar(150), @PKcode varchar(20)
    Declare @CurrCode varchar(5), @PaidAmt decimal, @PKAmount decimal , @PStart date, @PEnd date, @PExt date 
    Declare @DStart date,@DEnd date, @DExt date, @ACRefNo varchar(10), @NoIssue int, @AmtCharged decimal, @AmtFull decimal
    Declare @TEMPAmt decimal, @TEMDAmt decimal, @TESDAmt decimal, @ERDAmt decimal, @TEMPRate decimal, @TEMDRate decimal
    Declare @TEMPCopy decimal, @TEMDCopy Decimal, @TESDCopy int,@ERDCopy  int, @PStartIssueNo int, @PEndIssueNo int 
    Declare @DStartIssueNo int,@DEndIssueNo int

    DECLARE num_cursor CURSOR FOR 

    Select Code, Name,CurrCode,PaidAmt,PKAMOUNT, PStart, PEnd, PExt, DStart,   DEnd, DExt, ACRefNo, NoIssue, PStartIssueNo,PendIssueNo,DStartIssueNo,DEndIssueNo from [dbo].[SUB_tbl_Reports] 

    OPEN num_cursor

    FETCH NEXT FROM num_cursor 
    INTO @code,@Name,@PKCode,@CurrCode,@PaidAmt, @PKAmount, @PStart, @PEnd, @PExt, @DStart,  @DEnd, @DExt, @ACRefNo, @NoIssue, @start,@end,@start1,@end1
    WHILE @@FETCH_STATUS = 0
    BEGIN

        While @start <= @end  or @start1 <= @end1
        Begin
            Insert into SUB_tbl_Reports_audit (code,NAME,PKCode,Currcode,PaidAmt, PKAmount, PStart, PEnd, PExt, DStart,  DEnd, DExt, ACRefNo, NoIssue, pstartissueno,DStartIssueNo) 
            Values (@code,@nAME,@PKCode,@CurrCode,@PaidAmt, @PKAmount, @PStart, @PEnd, @PExt, @DStart,  @DEnd, @DExt, @ACRefNo, @NoIssue,@start,@start1)
            Set @start= @start + 1
            Set @start1 = @start1+1
        End 

       FETCH NEXT FROM num_cursor 
       INTO @code,@name,@pkcode,@CurrCode,@PaidAmt, @PKAmount, @PStart, @PEnd, @PExt, @DStart,   @DEnd, @DExt, @ACRefNo, @NoIssue, @start,@end, @start1,@end1

    END

    CLOSE num_cursor
    DEALLOCATE num_cursor

End 

`

Recommended Answers

All 2 Replies

Cursors are slow. Avoid them if you can, it will be much faster.

@Dave I also notice that in his code. Nice observation.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.