I have posted a few questions on this forum about a large transaction that is not committing fully. I have foun the culprit, this SP:
--------------------------------------------------------------------------------------
I wrote this SP to replace one a previous developer had in the database. The old procedure was way too slow(often times out with a 60 second command timeout)
Here is the code for the old SP.
This process needs to happen in less than a second, so going back to the original way of doing it is not an option. These procedures are basically subroutines of a main SP Set_loads_cost which is called after a load and logs are inserted by the vb client(loads are inserted in a vb loop, inside a big transaction) and calls Set_logs_cost, which uses a cursor to navigate through each log for a load (loads have a 1 to many foreign key relationship with logs) and call Set_log_cost which calls a different pricing procedure depending on some parameters. With the call to set_lengdiamprice commented out of set_log_cost the whole process happens in an acceptable time and all loads and logs are comitted. With the old version shown here and no command timeout the process succeeds in an ungodly long period of time. With the new version shown here the process runs almost as fast as if it were commented out, but a few times during the process SQL server will lose all the locks it is holding , causing any previous inserts and updates to disappear. The only loads and logs in the DB are ones that were processed after the last time SQL Server dropped the locks. Obviously i am doing something wrong in this Stored Procedure. I am offering my first born child as payment to anyone who can help me with this. TIA.
Ruairi
Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.
--------------------------------------------------------------------------------------
Code:
if exists (select * from sysobjects where id = object_id('dbo.get_lengdiamprice') and sysstat & 0xf = 4)
drop procedure dbo.get_lengdiamprice
GO
/****** Object: Stored Procedure dbo.get_lengdiamprice Script Date: 6/14/00 4:10:12 PM ******/
CREATE PROCEDURE get_lengdiamprice
@voucher int,
@contractortyperec int,
@contractorrec int,
@vendorrec int,
@brandname varchar (8),
@brandsrec int,
@sortsrec int,
@dag datetime,
@destinationrec int,
@vbdpricerec int,
@merchpayrate money,
@utilitypayrate money,
@speciesrec int,
@graderec int,
@logssortsrec int,
@grosslen smallint,
@grossdiam smallint,
@netlen smallint,
@netdiam smallint,
@tgrosslen smallint,
@tgrossdiam smallint,
@tnetlen smallint,
@tnetdiam smallint,
@grossv int,
@volume real,
@cdatabaserec int,
@price money output,
@cost money output
--@cdatabaserec int
as
declare @gradeindex int
declare @tgraderec int
declare @tsortsrec int
declare @maxgradesize int
declare @gradetyperec int
declare @lendiampricerec int
declare @lengthrec int
declare @diameterrec int
declare @logsegment char (1)
declare @len smallint
declare @diam smallint
declare @utilityflag char (1)
declare @tcontractortyperec int
declare @sql varchar (255)
-- added 12/19/2000
declare @lenforanygrade int
-- end addition
if (@contractortyperec = 5)
select @tcontractortyperec = 4
else
select @tcontractortyperec = @contractortyperec
select @logsegment = (select logsegment from brands where rec = @brandsrec)
select @lengthrec = (select lengthrec from brands where rec = @brandsrec)
if (@lengthrec = 1) -- use gross length
if (@logsegment = 'T') -- use segment length
select @len = @grosslen
else -- use total length
select @len = @tgrosslen
else -- use net length
if (@logsegment = 'T') -- use segment length
select @len = @netlen
else -- use total length
select @len = @tnetlen
select @diameterrec = (select diameterrec from brands where rec = @brandsrec)
if (@diameterrec = 1) -- use gross diameter
if (@logsegment = 'T') -- use segment diameter
select @diam = @grossdiam
else -- use total diameter
select @diam = @tgrossdiam
else -- use net diameter
if (@logsegment = 'T') -- use segment diameter
select @diam = @netdiam
else -- use total diameter
select @diam = @tnetdiam
select @utilityflag = 'N'
select @maxgradesize = (select max(rank) from spgr where speciesrec = @speciesrec)
-- begin search, normal log
select @tsortsrec = @logssortsrec
-- added 12/19/2000, if there are no prices for any grade of this species sort vbd go right to all sort search
select @lenforanygrade = (select rec from lendiamlen where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
sortsrec = @tsortsrec and
length1 <= @len and
length2 >= @len)
if (@lenforanygrade is null) -- if there are no prices for this vbd sort species length skip to all sort search
begin
select @price = null
goto skipgradesearch
end
-- end addition
exec get_grade_index @speciesrec, @graderec, @gradeindex output
while (@gradeindex < @maxgradesize)
begin
exec get_grade_rec @speciesrec, @gradeindex, @tgraderec output
-- added 12/19/2000 to speed up selection of price and pricerec
select P.price, P.rec into #test1
FROM
lendiamprice As P
Inner Join lendiamlen As L ON (P.lendiamlenrec = L.rec)
Inner Join lendiamdiam As D ON (P.lendiamdiamrec = D.rec)
where
L.vbdpricerec = @vbdpricerec and D.vbdpricerec = @vbdpricerec and
L.contractortyperec = @tcontractortyperec and D.contractortyperec = @tcontractortyperec and
L.speciesrec = @speciesrec and D.speciesrec = @speciesrec and
L.graderec = @tgraderec and D.graderec = @tgraderec and
L.sortsrec = @tsortsrec and D.sortsrec = @tsortsrec
and L.length1 <= @len and L.length2 >= @len
and D.diameter1 <= @diam and D.diameter2 >= @diam
-- end addition
select @price = (select max(price) from #test1)
select @lendiampricerec = (select max(rec) from #test1 where price = @price)
DROP TABLE #test1
if (@price is null)
select @gradeindex = @gradeindex + 1
else
select @gradeindex = @maxgradesize
end
-- added 12/19/2000
skipgradesearch:
-- end addition
if (@price is null)
begin
-- begin search, sorts = 'all'
select @tsortsrec = (select rec from sorts where name = 'ALL' and cdatabaserec = @cdatabaserec)
exec get_grade_index @speciesrec, @graderec, @gradeindex output
while (@gradeindex < @maxgradesize)
begin
exec get_grade_rec @speciesrec, @gradeindex, @tgraderec output
-- added 12/19/2000 to speed up selection of price and pricerec
select P.price, P.rec into #test
FROM
lendiamprice As P
Inner Join lendiamlen As L ON (P.lendiamlenrec = L.rec)
Inner Join lendiamdiam As D ON (P.lendiamdiamrec = D.rec)
where
L.vbdpricerec = @vbdpricerec and D.vbdpricerec = @vbdpricerec and
L.contractortyperec = @tcontractortyperec and D.contractortyperec = @tcontractortyperec and
L.speciesrec = @speciesrec and D.speciesrec = @speciesrec and
L.graderec = @tgraderec and D.graderec = @tgraderec and
L.sortsrec = @tsortsrec and D.sortsrec = @tsortsrec
and L.length1 <= @len and L.length2 >= @len
and D.diameter1 <= @diam and D.diameter2 >= @diam
-- end addition
select @price = (select max(price) from #test)
select @lendiampricerec = (select max(rec) from #test where price = @price)
DROP TABLE #test
if (@price is null)
select @gradeindex = @gradeindex + 1
else
select @gradeindex = @maxgradesize
end
end
if (@price = 0) or (@price is null)
begin
select @gradetyperec = (select gradetyperec from grade where rec = @graderec)
if (@gradetyperec = 2) or (@gradetyperec = 3) -- utility or cull grade
select @price = @utilitypayrate
select @utilityflag = 'Y'
end
if (@price is null)
select @cost = 0
else
select @cost = @price * @volume / 1000
if ((@price = 0) or (@price is null)) -- no price on file
exec print_error_14 @vendorrec, @brandname, @brandsrec, @sortsrec, @dag, @destinationrec, @vbdpricerec, @contractortyperec, @speciesrec, @graderec, @logssortsrec, @len, @diam
exec print_scale_lengdiamprice @voucher, @contractortyperec, @contractorrec, @brandname, @destinationrec, @speciesrec, @graderec, @logssortsrec, @grossv, @volume, @price, @cost, @lendiampricerec
GOHere is the code for the old SP.
Code:
if exists (select * from sysobjects where id = object_id('dbo.get_lengdiamprice') and sysstat & 0xf = 4)
drop procedure dbo.get_lengdiamprice
GO
CREATE PROCEDURE get_lengdiamprice
@voucher int,
@contractortyperec int,
@contractorrec int,
@vendorrec int,
@brandname varchar (8),
@brandsrec int,
@sortsrec int,
@dag datetime,
@destinationrec int,
@vbdpricerec int,
@merchpayrate money,
@utilitypayrate money,
@speciesrec int,
@graderec int,
@logssortsrec int,
@grosslen smallint,
@grossdiam smallint,
@netlen smallint,
@netdiam smallint,
@tgrosslen smallint,
@tgrossdiam smallint,
@tnetlen smallint,
@tnetdiam smallint,
@grossv int,
@volume real,
@cdatabaserec int,
@price money output,
@cost money output
as
declare @gradeindex int
declare @tgraderec int
declare @tsortsrec int
declare @maxgradesize int
declare @gradetyperec int
declare @lendiampricerec int
declare @lengthrec int
declare @diameterrec int
declare @logsegment char (1)
declare @len smallint
declare @diam smallint
declare @utilityflag char (1)
declare @tcontractortyperec int
declare @sql varchar (255)
if (@contractortyperec = 5)
select @tcontractortyperec = 4
else
select @tcontractortyperec = @contractortyperec
select @logsegment = (select logsegment from brands where rec = @brandsrec)
select @lengthrec = (select lengthrec from brands where rec = @brandsrec)
if (@lengthrec = 1) -- use gross length
if (@logsegment = 'T') -- use segment length
select @len = @grosslen
else -- use total length
select @len = @tgrosslen
else -- use net length
if (@logsegment = 'T') -- use segment length
select @len = @netlen
else -- use total length
select @len = @tnetlen
select @diameterrec = (select diameterrec from brands where rec = @brandsrec)
if (@diameterrec = 1) -- use gross diameter
if (@logsegment = 'T') -- use segment diameter
select @diam = @grossdiam
else -- use total diameter
select @diam = @tgrossdiam
else -- use net diameter
if (@logsegment = 'T') -- use segment diameter
select @diam = @netdiam
else -- use total diameter
select @diam = @tnetdiam
select @utilityflag = 'N'
select @maxgradesize = (select max(rank) from spgr where speciesrec = @speciesrec)
-- begin search, normal log
select @tsortsrec = @logssortsrec
exec get_grade_index @speciesrec, @graderec, @gradeindex output
while (@gradeindex < @maxgradesize)
begin
exec get_grade_rec @speciesrec, @gradeindex, @tgraderec output
select @price = (select max(price) from lendiamprice where
lendiamlenrec in
(select rec from lendiamlen where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
length1 <= @len and
length2 >= @len)
and
lendiamdiamrec in
(select rec from lendiamdiam where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
diameter1 <= @diam and
diameter2 >= @diam)
)
select @lendiampricerec = (select max(rec) from lendiamprice where
lendiamlenrec in
(select rec from lendiamlen where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
length1 <= @len and
length2 >= @len)
and
lendiamdiamrec in
(select rec from lendiamdiam where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
diameter1 <= @diam and
diameter2 >= @diam)
and price = @price)
if (@price is null)
select @gradeindex = @gradeindex + 1
else
select @gradeindex = @maxgradesize
end
if (@price is null)
begin
-- begin search, sorts = 'all'
select @tsortsrec = (select rec from sorts where name = 'ALL' and cdatabaserec = @cdatabaserec)
exec get_grade_index @speciesrec, @graderec, @gradeindex output
while (@gradeindex < @maxgradesize)
begin
exec get_grade_rec @speciesrec, @gradeindex, @tgraderec output
select @price = (select max(price) from lendiamprice where
lendiamlenrec in
(select rec from lendiamlen where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
length1 <= @len and
length2 >= @len)
and
lendiamdiamrec in
(select rec from lendiamdiam where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
diameter1 <= @diam and
diameter2 >= @diam)
)
select @lendiampricerec = (select max(rec) from lendiamprice where
lendiamlenrec in
(select rec from lendiamlen where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
length1 <= @len and
length2 >= @len)
and
lendiamdiamrec in
(select rec from lendiamdiam where
vbdpricerec = @vbdpricerec and
contractortyperec = @tcontractortyperec and
speciesrec = @speciesrec and
graderec = @tgraderec and
sortsrec = @tsortsrec and
diameter1 <= @diam and
diameter2 >= @diam)
and price = @price)
if (@price is null)
select @gradeindex = @gradeindex + 1
else
select @gradeindex = @maxgradesize
end
end
if (@price = 0) or (@price is null)
begin
select @gradetyperec = (select gradetyperec from grade where rec = @graderec)
if (@gradetyperec = 2) or (@gradetyperec = 3) -- utility or cull grade
select @price = @utilitypayrate
select @utilityflag = 'Y'
end
if (@price is null)
select @cost = 0
else
select @cost = @price * @volume / 1000
if ((@price = 0) or (@price is null)) -- no price on file
exec print_error_14 @vendorrec, @brandname, @brandsrec, @sortsrec, @dag, @destinationrec, @vbdpricerec, @contractortyperec, @speciesrec, @graderec, @logssortsrec, @len, @diam
exec print_scale_lengdiamprice @voucher, @contractortyperec, @contractorrec, @brandname, @destinationrec, @speciesrec, @graderec, @logssortsrec, @grossv, @volume, @price, @cost, @lendiampricerec
GORuairi
Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.