July 31, 2009 at 7:11 am
Hi there,
I want to show you a stored procedure made by third party company with cursors (maybe weeks to throw a result) and transformed by myself with tables variable (1-2 days). I want to know if it would exists another solution to reduce the time of execution of stored procedure.
I have 14 tables with the following counters of rows:
artavizec_storno - 915
artavizef_stociesiri - 313873
artchitcc_storno - 0
articole - 9120
artnir - 488419
avizec - 235544
bon - 1147854
chitfiscalec - 7223
elementcont - 88148
gestiuni - 7714
nir - 168638
societati - 276
stociesiri - 4496330
stocuri - 1731728
and the famous stored procedure is:
CREATE PROCEDURE [dbo].[RS_VechimeStocDetaliat22]
@data smalldatetime = '2009-06-30'
, @codgest varchar(5) = '%'
, @codart varchar(15) = '%'
AS
set nocount on
declare @datastoc smalldatetime
, @cantgasita numeric(28,8)
, @cantnecesara numeric(28,8)
, @pretintrare numeric(28,8)
, @nrintstoc int
, @nrintdoc int
, @nrpartida int
, @tipdoc varchar(2)
, @final tinyint
, @codsoc int
create table _stocfifo (nrintstoc int, codart varchar(15), codgest varchar(5), dataintrare smalldatetime, cantcond numeric(28,8), pretintrare numeric(28,8),
nrintdoc int, nrpartida int, tipdoc varchar(2), codsoc int, dataintrare_o smalldatetime, nrintdoc_o int, tipdoc_o varchar(2))
truncate table _stocfifo
select * from _stocfifo where tipdoc_o is null
insert into _stocfifo (nrintstoc,codart,codgest,dataintrare,cantcond,pretintrare,nrintdoc,nrpartida,tipdoc,codsoc)
select s.nrintstoc, s.codart, s.codgest, s.dataintrare, cant = s.cantcond - isnull(si.cantcondiesita,0), s.pretintrare, s.nrintdoc, s.nrpartidaaviz, s.tipdoc, s.codsoc
from stocuri s left join (select nrintstoc, cantcondiesita = sum(cantcondiesita) from stociesiri where datavanzare <= @data and (@codgest = '%' or codgest = @codgest) and codart like @codart group by nrintstoc) si on s.nrintstoc = si.nrintstoc
where s.dataintrare <= @data and (@codgest = '%' or codgest = @codgest) and codart like @codart and s.cantcond - isnull(si.cantcondiesita,0) > 0
/*Prepare TABLE variable to take resultset*/
DECLARE @stoc_c TABLE(
RowID INT IDENTITY(1, 1),
nrintstoc int ,
dataintrare smalldatetime,
nrintdoc int,
nrpartida int,
tipdoc varchar(2))
/*Local variables */
DECLARE
@count int, /*create local @@fetch_status*/
@iRow int /*row pointer (index)*/
/* create array simulator */
INSERT @stoc_c
select nrintstoc, dataintrare, nrintdoc, nrpartida, tipdoc from _stocfifo
--where tipdoc_o is null and tipdoc in ('BT')
/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT
/*initialize index counter*/
SET @iRow = 1
/*establish loop structure*/
WHILE @iRow <= @count
BEGIN
/*get row values*/
SELECT @nrintstoc=nrintstoc, @datastoc=dataintrare, @nrintdoc=nrintdoc, @nrpartida=nrpartida, @tipdoc=tipdoc
FROM @stoc_c
WHERE RowID = @iRow
/*perform operations with single row*/
set @final = 0
while @final = 0
begin
if @tipdoc in ('BP','bc','BA') set @final = 1
if @tipdoc = 'BT'
begin
if exists (select 1 from stociesiri where nrpartidaaviz = @nrpartida and nrintdoc = @nrintdoc and tipdoc = 'BT')
begin
select @datastoc = dataintrare, @nrintdoc = nrintdoc, @nrpartida = nrpartidaaviz, @tipdoc = tipdoc
from stocuri where nrintstoc = (select nrintstoc from stociesiri where nrpartidaaviz = @nrpartida and nrintdoc = @nrintdoc and tipdoc = 'BT')
end
else
begin
set @final = 1
end
end
if @tipdoc = 'NR'
begin
if exists (select 1 from artavizef_stociesiri where nrpartidaaviz = (select nrpartidaaviz from artnir where nrintnir = @nrintdoc and nrintartnir = @nrpartida))
begin
select @datastoc = dataintrare, @nrintdoc = nrintdoc, @nrpartida = nrpartidaaviz, @tipdoc = tipdoc
from stocuri where nrintstoc = (select nrintstoc from stociesiri where nrintiesire = (select nrintiesire from artavizef_stociesiri where nrpartidaaviz = (select nrpartidaaviz from artnir where nrintnir = @nrintdoc and nrintartnir = @nrpartida)))
end
else
begin
set @final = 1
end
end
if @tipdoc = 'ac'
begin
if exists (select 1 from artavizec_storno where nrpartidaavizstorno = @nrpartida)
begin
select @datastoc = dataintrare, @nrintdoc = nrintdoc, @nrpartida = nrpartidaaviz, @tipdoc = tipdoc
from stocuri where nrintstoc = (select top 1 nrintstoc from stociesiri where tipdoc = 'AC' and nrpartidaaviz = (select nrpartidaavizc from artavizec_storno where nrpartidaavizstorno = @nrpartida))
end
else
begin
set @final = 1
end
end
if @tipdoc = 'cc'
begin
if exists (select 1 from artchitcc_storno where nrpartidachitfstorno = @nrpartida)
begin
select @datastoc = dataintrare, @nrintdoc = nrintdoc, @nrpartida = nrpartidaaviz, @tipdoc = tipdoc
from stocuri where nrintstoc = (select top 1 nrintstoc from stociesiri where tipdoc = 'CC' and nrpartidaaviz = (select nrpartidachitf from artchitcc_storno where nrpartidachitfstorno = @nrpartida))
end
else
begin
set @final = 1
end
end
end
update _stocfifo set dataintrare_o = @datastoc, nrintdoc_o = @nrintdoc, tipdoc_o = @tipdoc where nrintstoc = @nrintstoc
/*go to next row*/
SET @iRow = @iRow + 1
END
select soc.societate, s.codgest, gestiune = g.denumire, clasa = isnull(ecl.numeelement,'N/A'), grupa = isnull(egr.numeelement,'N/A'), s.codart, s.dataintrare, s.pretintrare, cantramasa = s.cantcond, valoare = s.pretintrare * s.cantcond
, datadoc = s.dataintrare_o, tipdoc = s.tipdoc_o, nrdoc = coalesce(n.nrnir, b.nrbon, av.nraviz, c.nrchitf), furnizor = coalesce(n.codfurnizor, av.codclient, c.codclient, 'N/A')
from _stocfifo s join gestiuni g on s.codgest = g.codgestiune
join articole a on s.codart = a.codart
join societati soc on s.codsoc = soc.codsoc
left join elementcont ecl on ecl.codelement = a.clasa
left join elementcont egr on egr.codelement = a.grupa
left join nir n on s.tipdoc_o = 'NR' and s.nrintdoc_o = n.nrintnir
left join bon b on upper(left(s.tipdoc_o,1)) = 'B' and s.nrintdoc_o = b.nrintbon
left join avizec av on s.tipdoc = 'ac' and s.nrintdoc_o = av.nrintaviz
left join chitfiscalec c on s.tipdoc = 'cc' and s.nrintdoc_o = c.nrintchitf
where s.tipdoc_o is not null
--drop table _stocfifo
*/
set nocount off
GO
The question is: how to optimize this stored procedure?
The execution is : exec RS_VechimeStocDetaliat22 '2009-06-30'
July 31, 2009 at 7:19 am
Tables and indexes are described in the attachement.
July 31, 2009 at 8:55 am
Please post execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
The first two things I'd do here would be to replace the table variables with temp tables and to get rid of that while loop. Row-by-row operations, whether by cursor or while loop are slow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2009 at 11:50 pm
Soryy, I forgot to put the exec plan.
August 3, 2009 at 12:01 am
Hi Gila,
First time, the stored procedure was built with temp tables and was slowest than with table variables because of IO contention. As I said earlier, with temp tables (#stocfifo) the time of executing sp was about weeks versus table variables (in-memory) the time was about 2-3 days. I know that the loop is guilt by this issue and would know how to replace this loop.
August 5, 2009 at 11:35 am
Switching from Temp Tables to table variable is not going to help with IO problems. they are treated exactly the same regarding their location (memory or disk) and when they are written to disk.
Will take a look at your stuff later in the week. Been at a conference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2009 at 3:09 am
OK, thank you.
August 6, 2009 at 9:46 am
You have to replace the inner while-end with four updates like this:
update _stocfifo
set dataintrare_o = stocuri.dataintrare, nrintdoc_o = stocuri.nrintdoc, tipdoc_o = stocuri.tipdoc
from _stocfifo , @stoc_C scc, stocuri
where
scc.tipdoc='BT' and
_stocfifo.nrintstoc = scc.nrintstoc and exists
(select 1 from stociesiri where nrpartidaaviz = scc.nrpartida and nrintdoc = scc.nrintdoc and tipdoc = 'BT') and
stocuri.nrintstoc =
(select nrintstoc from stociesiri where nrpartidaaviz = scc.nrpartida and nrintdoc = scc.nrintdoc and tipdoc = 'BT')
This would be the code for 'BT', you have to write the other 3 updates for tipdoc='NR', 'AC', 'CC' , I am not sure if the order of the queries is important but it seems it is not ...
I suppose the indexes are ok, the last query seems fine too.
But I am not sure the while-end code really works fine - what if the first encountered row has a tipdoc that terminates the loop? and the 2nd row has a valid tipdoc that would qualify for a _stocfifo update ?!?!? 😉 and I suppose your tables are not ordered by tipdoc ...
Virgil Rucsandescu
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply