November 26, 2008 at 2:50 am
i m creating stored procedure i want to fetch data in this stored procedure .i was using cursor but its performance not good so ant alternative solution of this problem plz send me reply
thanks & regards
lav
November 26, 2008 at 2:56 am
lav (11/26/2008)
i m creating stored procedure i want to fetch data in this stored procedure .i was using cursor but its performance not good so ant alternative solution of this problem plz send me replythanks & regards
lav
what is the scenario?
you can use temporary table or table variable
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 26, 2008 at 4:23 am
i m not using tempory table . i am fetch the data and posted another table. using cursor that time some calcution doing in stored procedure and insert into another table
November 26, 2008 at 4:34 am
Please post the full code of the sp... and table definitions and sample data, along with the required results.
The short answer is do insert into () Select Make, Calculations, In , Here FROM whatever.The.QueryIs where...
But that might be more complex than it seems depending on the kind of calculations required.
November 26, 2008 at 5:47 am
i m seding you complete sp . i m using a cursor but execution is very slow then plz send me another option to fetch the record.
ALTER PROCEDURE [dbo].[SP_GLAcoountWise]
(
@fromdate varchar(10),
@todate varchar(10),
@divcode varchar(5),
@loccode varchar(5),
@clicode varchar(5),
@fiyear varchar(4),
@where1 varchar(200),
@partytype varchar(15),
@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 @accode varchar(10)
declare @pdesc varchar(50)
declare @opamt decimal
declare @s_opamt varchar(50)
declare @opamt1 numeric(18,3)
declare @type varchar(3)
declare @debit decimal
declare @credit decimal
declare @debit1 decimal
declare @credit1 decimal
declare @amt decimal
declare @vno varchar(10)
declare @doctype varchar(5)
declare @date varchar(10)
declare @docdesc varchar(200)
declare @bal decimal
declare @type1 varchar(2)
DECLARE @SelectStatement Nvarchar(max)
BEGIN TRY
--BEGIN TRANSACTION
delete from fi_Glreporttemp where divcode=@divcode and clicode=@clicode and loccode=@loccode and userid=@userid
--declare c1 CURSOR FOR
--Where clause problme in this Query
-----------------------------------------------------------------------------------------------
SET @SelectStatement=N'set @cursor = 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<='''+@todate+''' and fi_gl.divcode='''+@divcode+''' and fi_gl.loccode='''+@loccode+''' and fi_gl.clicode='''+@clicode+''''+@where1+' OPEN @cursor'
DECLARE @mainCursor 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<=@todate and fi_gl.divcode=@divcode and fi_gl.loccode=@loccode and fi_gl.clicode=@clicode
print @SelectStatement
--open c1
exec sp_executesql @SelectStatement,N'@cursor cursor output',@mainCursor output
FETCH NEXT FROM @mainCursor
--Fetch Row
into @accode,@pdesc
WHILE @@FETCH_STATUS = 0
BEGIN
--Declare Second cursor RecipetNo
if (@fromdate = '04/01/'+@fiyear)--//Check financial Opening Date ......
begin
select @s_opamt=amt,@type=type from fi_gl where date=@fromdate and fiyear=@fiyear and divcode=@divcode and loccode=@loccode and clicode=clicode and accode=@accode and ind='ob' order by date
if(@s_opamt is null)
set @opamt=0
else
set @opamt=@s_opamt
end
else
begin
--Opening
select @s_opamt= isnull(sum(amt),0) ,@type=type from fi_gl where date='04/01/' + @fiyear and divcode=@divcode and loccode=@loccode and clicode=@clicode and accode=@accode and ind='ob' group by type
--Debit And Credit
select @debit=sum(amt) from fi_gl where accode=@accode and type='d' and date>='04/01/' + @fiyear and date<=@datediff and divcode=@divcode and loccode=@loccode and clicode=@clicode and ind!='OB'
select @credit= sum(amt) from fi_gl where ind!='OB' and accode=@accode and type='C' and date>='04/01/' + fiyear and date<=@datediff and divcode=@divcode and loccode=@loccode and clicode=@clicode
if(@type='C')
set @opamt=@opamt+@credit-@debit
else
set @opamt=@opamt-@credit+@debit
if(@type is null)
set @opamt=@debit-@credit
if(@s_opamt is null)
set @opamt=0
else
set @opamt=@s_opamt
if(@opamt>0)
set @type='D'
else
set @type='C'
end
set @opamt=abs(@opamt)
set @opamt1=abs(@opamt)
declare c2 cursor for select vno,amt,type,left(Convert(Char,date,101),10) as date,mdocument.doctype,docdesc from fi_gl inner join mdocument on fi_gl.doctype= mdocument.doctype and fi_gl.clicode=mdocument.clicode where date>=@fromdate and date<=@todate and fi_gl.divcode=@divcode and fi_gl.loccode=@loccode and fi_gl.clicode=@clicode and accode=@accode and ind!='OB'
open c2
FETCH NEXT FROM c2
--Fetch Row
into @vno,@amt,@type,@date,@doctype,@docdesc
WHILE @@FETCH_STATUS = 0
BEGIN
--find credit
select @credit1=isnull(amt,0) from fi_gl where date>=@fromdate and date<=@todate and divcode=@divcode and loccode=@loccode and clicode=@clicode and accode=@accode and type='C' and vno=@vno and doctype=@doctype and ind!='OB' order by date
--//Find Debit
select @debit1=isnull(amt,0) from fi_gl where date>=@fromdate and date<=@todate and divcode=@divcode and loccode=@loccode and clicode=@clicode and accode=@accode and type='D' and vno=@vno and doctype=@doctype and ind!='OB' order by date
if(@credit1 is null)
set @credit1=0
if(@debit1 is null)
set @debit1=0
if(@type='C')
begin
set @bal=@opamt+@credit1-@debit1
if(@bal>0)
set @type1='D'
else
set @type1='C'
set @opamt=@bal
set @type=@type1
end
else
begin
set @bal=@opamt-@credit1+@debit1
if(@bal>0)
set @type1='D'
else
set @type1='C'
set @opamt=@bal
set @type=@type1
end
exec Insert_GLReportTemp @vno,@date,@type1,@opamt1,@debit1,@credit1,@bal,@divcode,@clicode,@loccode,@docdesc,@doctype,@accode,0,@pdesc,@type,@userid
--Maint Accoount Reverse Refect
declare @vno1 varchar(10)
declare @type2 varchar(2)
declare @amt1 numeric(18,3)
declare @date3 varchar(10)
declare @doctype2 varchar(5)
declare @accode1 varchar(10)
declare @accdesc varchar(500)
-------------------------For GL Entry---------------------------
Declare c3 cursor for select fi_gl.vno,fi_gl.amt,fi_gl.type,Convert(char,fi_gl.date,101) as date,fi_gl.doctype,fi_gl.accode ,macc.accdesc from fi_gl inner join macc on fi_gl.accode=macc.accode and fi_gl.divcode=macc.divcode and fi_gl.loccode=macc.loccode and fi_gl.clicode=macc.clicode where fi_gl.date=@date and fi_gl.divcode=@divcode and fi_gl.loccode=@loccode and fi_gl.clicode=@clicode and fi_gl.vno=@vno and fi_gl.doctype=@doctype and fi_gl.accode!=@accode and fi_gl.ind!='OB'
open c3
FETCH NEXT FROM c3
--Fetch Row
into @vno1,@amt1,@type2,@date3,@doctype2,@accode1,@accdesc
WHILE @@FETCH_STATUS = 0
BEGIN
exec Insert_GLReportTemp @vno1,@date3,@type2,@opamt1,0,0,0,@divcode,@clicode,@loccode,@accdesc,@doctype,@accode1,@amt1,@pdesc,@type,@userid
FETCH NEXT FROM c3
--Fetch Row
into @vno1,@amt1,@type2,@date3,@doctype2,@accode1,@accdesc
end
close c3
DEALLOCATE c3
------------------------------------------------------------------------
-------------------------------------supplier Wise----------------------
Declare c4 cursor for select fi_gl.vno,fi_gl.amt,fi_gl.type,Convert(char,fi_gl.date,101) as date,fi_gl.doctype,fi_gl.accode ,msupplier.pdesc from fi_gl inner join msupplier on fi_gl.accode=msupplier.pcode and fi_gl.divcode=msupplier.divcode and fi_gl.loccode=msupplier.loccode and fi_gl.clicode=msupplier.clicode where fi_gl.date=@date and fi_gl.divcode=@divcode and fi_gl.loccode=@loccode and fi_gl.clicode=@clicode and fi_gl.vno=@vno and fi_gl.doctype=@doctype and fi_gl.accode!=@accode and fi_gl.ind!='OB'
open c4
FETCH NEXT FROM c4
--Fetch Row
into @vno1,@amt1,@type2,@date3,@doctype2,@accode1,@accdesc
WHILE @@FETCH_STATUS = 0
BEGIN
exec Insert_GLReportTemp @vno1,@date3,@type2,@opamt1,0,0,0,@divcode,@clicode,@loccode,@accdesc,@doctype,@accode1,@amt1,@pdesc,@type,@userid
FETCH NEXT FROM c4
--Fetch Row
into @vno1,@amt1,@type2,@date3,@doctype2,@accode1,@accdesc
end
close c4
DEALLOCATE c4
---------------------------------------------------------------------------------------
------------------------------------------------------------Customer Wise------------------
Declare c5 cursor for select fi_gl.vno,fi_gl.amt,fi_gl.type,Convert(char,fi_gl.date,101) as date,fi_gl.doctype,fi_gl.accode ,mcustomer.pdesc from fi_gl inner join mcustomer on fi_gl.accode=mcustomer.pcode and fi_gl.divcode=mcustomer.divcode and fi_gl.loccode=mcustomer.loccode and fi_gl.clicode=mcustomer.clicode where fi_gl.date=@date and fi_gl.divcode=@divcode and fi_gl.loccode=@loccode and fi_gl.clicode=@clicode and fi_gl.vno=@vno and fi_gl.doctype=@doctype and fi_gl.accode!=@accode and fi_gl.ind!='OB'
open c5
FETCH NEXT FROM c5
--Fetch Row
into @vno1,@amt1,@type2,@date3,@doctype2,@accode1,@accdesc
WHILE @@FETCH_STATUS = 0
BEGIN
exec Insert_GLReportTemp @vno1,@date3,@type2,@opamt1,0,0,0,@divcode,@clicode,@loccode,@accdesc,@doctype,@accode1,@amt1,@pdesc,@type,@userid
FETCH NEXT FROM c5
--Fetch Row
into @vno1,@amt1,@type2,@date3,@doctype2,@accode1,@accdesc
end
close c5
DEALLOCATE c5
----------------------------------------------------------------------------------------------------------
FETCH NEXT FROM c2
into @vno,@amt,@type,@date,@doctype,@docdesc --Fetch Row
end --//vno
close c2
DEALLOCATE c2
FETCH NEXT FROM @mainCursor --Fetch Row
into @accode,@pdesc
END--End of partty
close @mainCursor
DEALLOCATE @mainCursor
November 26, 2008 at 5:52 am
Oh boy!, can you give us the loginc behind all this?
I'm sure we can optimize most of it, if not all of it, but we need more info.
November 27, 2008 at 6:51 am
Forums are for small, targeted help situations. This is NOT that! 🙂 230 lines of code in a sproc with nested cursors referencing multiple tables with lots of complex logic AND numerous subordinate sproc calls for each row. This could literally take a very sharp resource a week or more to refactor, depending on the requirements and how complicated the internal sproc calls are. And sometimes stuff like this simply has to be done a row at a time, so it could all be for naught.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2008 at 7:01 am
Great I'll be the first to put a bid on this... 80 hours @ 95$ / hour >>> that'll be 7600$ to check up on this. Plus whatever flight, hotels, food, phone bills types of costs that will come up!
Let me know when you are ready for me to begin.
November 27, 2008 at 8:21 am
Ninja's_RGR'us (11/27/2008)
Great I'll be the first to put a bid on this... 80 hours @ 95$ / hour >>> that'll be 7600$ to check up on this. Plus whatever flight, hotels, food, phone bills types of costs that will come up!Let me know when you are ready for me to begin.
I am certain this can be done completely remotely, so we can avoid the travel costs. I think you will win on this one - your rate is lower than mine. 😀 But I don't have any availability anyway. Knock on wood I am still fully employeed!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2008 at 8:43 am
Looks like I can bid higher... so I'll move my rates up to 175$ / hour,do the maths for me : That'll be cheaper that way!
December 1, 2008 at 7:18 am
I'll put my bid in at $75/hr, on the basis of doing it remotely and during night and weekend hours. It's not looking like it's as complex as some have stated, but it's still far from completely clear as to what the ultimate objective is, which is why I'm bidding on a time/materials basis rather than a fixed-price contract. I can start tomorrow night. Send me a "private message" in this forum and I'll provide contact information.
My best guess is that it's performance CAN be improved, and it can probably be in better shape within ~2 weeks time, and I would like to believe it will be better much sooner than that. I'd suggest capping things at 100 hours, as there's no reason to send oneself to the poor house.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 1, 2008 at 7:29 am
Sure you can go with the "cheap" one... but look at the # of posts answered and see for yourself who is better qualified for the job :D.
December 1, 2008 at 7:35 am
If the sole qualification to do this was based entirely on one's experience with this forum, then that would completely ignore the last 30 years I've spent with information technology, so I would like to think that the original poster is smarter than that.
Steve
(aka smunson)
:):):)
Ninja's_RGR'us (12/1/2008)
Sure you can go with the "cheap" one... but look at the # of posts answered and see for yourself who is better qualified for the job :D.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 1, 2008 at 7:50 am
I have to get in on this. Are you actually going to look at hiring a consultant to rewrite this procedure? If so, I'd like to make a bid as well, but I think this should be done in a more formal manner than over a forum post. You can PM me the necessary contact information if you are looking for a formal bid. I would recommend providing as much information regarding requirements so anyone interested can make a well reasoned bid.
December 1, 2008 at 8:25 am
Sure PM me as well!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply