May 27, 2009 at 7:20 am
Dear Sir,
I have creating on stored procedure in sql server 2005. that strored procedure i m using cursor. when executing this stored procedure he take lot of time that why i give time expire in my application . so tell how to solve this problem i m creating index in my db table ..waiting u r reply
Thanks..
May 27, 2009 at 7:25 am
Post the script of your stored procedure and attach the execution plan to start with.
May 27, 2009 at 7:49 am
I have sending My Stored Procedure Plz give a proper solution how to solve this problem..
create PROCEDURE [dbo].[SP_StockReport]
(
@fromdate varchar(10),
@todate varchar(10),
@divcode varchar(5),
@loccode varchar(5),
@clicode varchar(5),
@fiyear varchar(4),
@where1 varchar(200),
@datediff varchar(10),
@userid varchar(200)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--declare variables...................................
declare @partcode varchar(35)
declare @pdesc varchar(50)
declare @catcode varchar(2)
declare @opqty decimal
declare @s_opqty varchar(50)
declare @opqty1 numeric(18,3)
declare @type varchar(3)
declare @recqty decimal
declare @returnqty decimal
declare @issqty decimal
declare @closeqty decimal
declare @debit1 decimal
declare @credit1 decimal
declare @weight decimal
declare @opweight decimal
declare @vno varchar(10)
declare @doctype varchar(5)
declare @date varchar(10)
declare @matcode varchar(200)
declare @bal decimal
declare @sgcode varchar(2)
declare @refdocno varchar(10)
declare @refdocdate datetime
declare @uom varchar(5)
declare @slcode varchar(10)
DECLARE @SelectStatement Nvarchar(max)
declare @C int
SET @divcode=upper(@divcode)
set @clicode=upper(@clicode)
set @loccode=upper(@loccode)
declare @opflag int
BEGIN TRY
--BEGIN TRANSACTION
delete from FI_StockTemp where divcode=@divcode and clicode=@clicode and userid=@userid
--declare c1 CURSOR FOR
--Where clause problme in this Query
-----------------------------------------------------------------------------------------------
SET @SelectStatement=N'set @cursor = CURSOR FOR SELECT DISTINCT dbo.Stock.partcode, dbo.Stock.catcode, dbo.MPart.partdesc, dbo.Stock.loccode, dbo.MPart.matcode, dbo.MPart.sgcode FROM dbo.Stock INNER JOIN
dbo.MPart ON dbo.Stock.catcode = dbo.MPart.catcode AND dbo.Stock.partcode = dbo.MPart.partcode AND dbo.Stock.divcode = dbo.MPart.divcode AND
dbo.Stock.loccode = dbo.MPart.loccode INNER JOIN
MStoragelocation ON dbo.Stock.slcode = dbo.MStoragelocation.slcode AND dbo.Stock.divcode = dbo.MStoragelocation.divcode AND
dbo.Stock.loccode = dbo.MStoragelocation.loccode AND dbo.Stock.clicode = dbo.MStoragelocation.clicode INNER JOIN
dbo.MLocation ON dbo.Stock.loccode = dbo.MLocation.loccode AND dbo.Stock.loccode = dbo.MLocation.loccode AND
dbo.Stock.divcode = dbo.MLocation.divcode AND dbo.Stock.divcode = dbo.MLocation.divcode AND dbo.Stock.clicode = dbo.MLocation.clicode AND
dbo.Stock.clicode = dbo.MLocation.clicode AND dbo.MPart.loccode = dbo.MLocation.loccode AND dbo.MPart.divcode = dbo.MLocation.divcode AND
dbo.MPart.clicode = dbo.MLocation.clicode AND dbo.MStoragelocation.loccode = dbo.MLocation.loccode AND
dbo.MStoragelocation.divcode = dbo.MLocation.divcode AND dbo.MStoragelocation.clicode = dbo.MLocation.clicode
WHERE stock.divcode='''+@divcode+''' and stock.clicode='''+@clicode+''''+@where1+' OPEN @cursor'
/*SET @SelectStatement=N'set @cursor = CURSOR FOR select partcode,catcode,partdesc,loccode,matcode,sgcode from mpart OPEN @cursor'*/
DECLARE @mainCursor1 cursor
--declare c1 CURSOR FOR select distinct macc.accode,accdesc from fi_gl inner join macc on macc.accode=fi_gl.accode and macc.divcode=fi_gl.divcode and macc.loccode=fi_gl.loccode and macc.clicode=fi_gl.clicode where fi_gl.date>=@fromdate and fi_gl.date='04/01/'+@fiyear and docdate=@fromdate and docdate<=@todate and divcode=@divcode and loccode=@loccode and clicode=@clicode and partcode=@partcode and catcode=@catcode and ind!='OB' order by docdate,docno
open c2
FETCH NEXT FROM c2
--Fetch Row
into @doctype, @vno,@recqty,@returnqty,@issqty,@date,@weight,@slcode,@refdocno,@refdocdate,@uom
WHILE @@FETCH_STATUS = 0
BEGIN
--find credit
set @closeqty=@opqty+@recqty+@returnqty-@issqty
/*if(@c=0)
begin
INSERT INTO [FI_StockTemp]
([partcode],[partdesc],[stlocode],[catcode],[matcode],[sgcode]
,[doctype],[vno],[date],[opqty],[recqty],[issqty],[returnqty]
,[closeqty],[weight],[divcode],[loccode],[clicode],[userid])
VALUES
(@partcode,@pdesc,@slcode,@catcode,@matcode,@sgcode
,'' ,'',@date,@opqty,0,0,0
,0,@opweight,@divcode,@loccode,@clicode,@userid)
set @C=1
end */
INSERT INTO [FI_StockTemp]
([partcode],[partdesc],[stlocode],[catcode],[matcode],[sgcode]
,[doctype],[vno],[date],[opqty],[recqty],[issqty],[returnqty]
,[closeqty],[weight],[divcode],[loccode],[clicode],[userid],refdocno,refdate,[uom])
VALUES
(@partcode,@pdesc,@slcode,@catcode,@matcode,@sgcode
,@doctype,@vno,@date,@opqty1,@recqty,@issqty,@returnqty
,@closeqty,@weight,@divcode,upper(@loccode),@clicode,@userid,@refdocno,@refdocdate,@uom)
set @recqty=0 set @returnqty=0 set @issqty=0 set @opqty1=0
FETCH NEXT FROM c2
into @doctype, @vno,@recqty,@returnqty,@issqty,@date,@weight,@slcode,@refdocno,@refdocdate,@uom
set @opqty=@closeqty
end --//vno
close c2
DEALLOCATE c2
--
if(@opflag=1)
begin
select @vno=docno ,@date=convert(char,docdate,101),@slcode=slcode,@doctype=doctype ,@uom=uom from stock where docdate='04/01/'+@fiyear and fiyear=@fiyear and divcode=@divcode and loccode=@loccode and clicode=@clicode and partcode=@partcode and catcode=@catcode
if(@vno is not null)
INSERT INTO [FI_StockTemp]
([partcode],[partdesc],[stlocode],[catcode],[matcode],[sgcode]
,[doctype],[vno],[date],[opqty],[recqty],[issqty],[returnqty]
,[closeqty],[weight],[divcode],[loccode],[clicode],[userid],refdocno,refdate,[uom])
VALUES
(@partcode,@pdesc,@slcode,@catcode,@matcode,''
,@doctype,@vno,@date,@opqty1,0,0,0
,@opqty1,0,@divcode,upper(@loccode),@clicode,@userid,'',getdate(),@uom)
end
FETCH NEXT FROM @mainCursor1 --Fetch Row
into @partcode,@catcode,@pdesc,@loccode,@matcode,@sgcode
END--End of main cursor
close @mainCursor1
DEALLOCATE @mainCursor1
--COMMIT TRANSACTION
END TRY
BEGIN CATCH
--Rollback transaction
close @mainCursor1
DEALLOCATE @mainCursor1
print ERROR_MESSAGE()
END CATCH
--if(@@TRANCOUNT!=0)
--BEGIN
--goto clean
--END
/*clean:
Rollback transaction
close c1
DEALLOCATE c1
close c2
DEALLOCATE c2*/
--INSERT INTO cs_57f4receiptupdate (BOMSRNO,RECEIPTNO,RECEIPTDATE,INVCATCODE,INVPARTCODE,IPCATCODE,IPPARTCODE,IPQTY,INVVNO,INVDATE,INVQTY,INVUOM,DIVCODE,LOcCODE,CLICODE,FIYEAR)
--VALUES(@bomsrno,@receiptno,@receiptdate,@catcode,@partcode,@ipcatcode,@ippartcode,@ipqty,@invno,@invvdate,@invqty,@uom,@divcode,@loccode,@clicode,@fiyear)
--select @msg
return
END
Thanks Lav
May 27, 2009 at 7:52 am
Please attach the execution plan as well..
May 27, 2009 at 7:58 am
I have using 3 No. table in this stored procedure.
Mpart
Stock
storage location
but mpart and stock table more important .
//Paramenter Value
exec SP_StockReport @fromdate='04/01/2009',@todate='03/31/2010',@divcode='DV003',@loccode='Am003',@clicode='neel',@fiyear='2009',@where1='
and stock.docdate Between ''04/01/2009'' And ''03/31/2010'' And MLocation.locname like
''%SATPUR%''',@datediff='03/31/2009',@userid='6FFA5BF1-4B37-49C4-B786-0D792E14D8D6'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply