se da urmatorul cod:
SET @s = CURSOR FOR
select r1,u1,ois,n1,n2,c1,c2,m1,
life from TempStd
OPEN @s
FETCH NEXT
FROM @s INTO @r1 ,@u1 ,@OIS ,
@n1 ,@n2 , @c1 ,@c2 ,
@m1 ,@Life
WHILE @@FETCH_STATUS = 0
BEGIN
if @Life='New'
.................
end
else if @Life='Old'
begin
insert into Table2 (n1,c1,c2,lpd,lvd,cno,lmb,
lmd)
SELECT n1,c1,c2,
(
SELECT max(c)
FROM (
SELECT dt AS c, c1
FROM history
UNION ALL
SELECT lpd AS c, c1
FROM history
) u
WHERE h.c1 = u.c1
)as lpd,
(
SELECT max(c)
FROM (
SELECT dv AS c, c1
FROM history
UNION ALL
SELECT lvd AS c, c1
FROM history
) u
WHERE h.c1 = u.c1
)as lvd,
max(cno)+1 as MaxC, lmb, lmd
FROM history h
WHERE c1=@c1 and cno in (select max(cno) from history where c1=@c1)
GROUP BY n1, lmb, lmd,c1,c2,
dt,dv
end
..................
DEALLOCATE @s
problema este ca pt ca pt aprox 40.000 de inregistrari in tabela history si cu aprox 18.000 de inreg in tabela TempStd, toata procedura ruleaza cam in 30 de minute, ceea ce e departe de a fi ok.
m-ar interesa idei asupra posibilitatii de optimizare, altfel cand va creste numarul de inregistrari in history la cateva sute de mii, s-ar putea sa stau mult si bine
Multumesc