SQL Server Performance

T-SQL function written while using 2000server runs about 4 times slower on 2005server.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Bartosz Kmita, Sep 7, 2007.

  1. Bartosz Kmita New Member

    Hello all,
    I am new here.
    I've just migrated from 2000 to 2005 (not express but full commercial version). Now I have a little faster machine server, the same folder structure on new machine, default database and server settings etc.
    I use sql server as a DB server for an ERP application in my company. I have many stored procedures and function written in T-SQL while using sql server 2000. All DB processes, stored procedures, functions etc. run visibly faster on 2005 than 2000. But I have one T-SQL funcion (which is unfortunately executing very often by the ERP system) that runs expressly about 4 times slower. I had tried with forced parametrization and some other settings without any visible acceleration for that function. I have no idea where is the problem.
    below is the code of described function:
    CREATE function [CDN].[skladniki_rec] (@kod_zr varchar (40),@lp_zr smallint)
    returns @skladniki_rec table
    (
    kod_glownego varchar(40),
    numer varchar(100),
    kod varchar(40),
    numer_receptury smallint,
    nazwa varchar(255),
    ilosc decimal(11,4),
    jm varchar (8),
    Pochodzenie varchar(40),
    przegladnieto smallint,
    poziom smallint
    )
    AS
    begin

    declare @tabela_Bartek_skladniki_receptury_tymczsowa table(kod varchar(40))

    declare @numer_akt varchar (100);
    declare @poziom smallint;
    select @poziom=1
    declare @poziom_pomoc smallint;
    declare @kod_akt varchar (40);
    declare @ilosc_suma decimal(11,4);


    INSERT INTO @tabela_Bartek_skladniki_receptury_tymczsowa
    select distinct(Rec2.Twr_kod)
    from cdn.recnag rnag2
    join cdn.twrkarty NAG2 on Nag2.twr_kod=@kod_zr and rnag2.ren_TypKompletacji = 1 and rnag2.ren_TwrLP = @lp_zr
    and rnag2.ren_twrtyp = NAG2.twr_gidtyp and rnag2.ren_twrnumer = NAG2.twr_gidnumer
    join cdn.recelem relem on rnag2.ren_gidtyp = relem.ree_gidtyp and rnag2.ren_gidnumer = relem.ree_gidnumer
    and (relem.ree_typpozycji = 1 or relem.ree_typpozycji = 3) and relem.ree_archiwalny = 0
    join cdn.twrkarty REC2 on relem.ree_twrtyp = REC2.twr_gidtyp and relem.ree_twrnumer = REC2.twr_gidnumer


    INSERT INTO @skladniki_rec
    select
    @kod_zr,
    (select convert(varchar,count(*)+1)+'.'
    from @tabela_Bartek_skladniki_receptury_tymczsowa
    where kod<Rec.Twr_kod
    ),
    Rec.Twr_kod,@lp_zr,Rec.Twr_nazwa,round(sum(Ree_ilosc)/RN.ren_ilosc,4),Ree_Jmz,
    Rec.Twr_Wartosc1,
    case when RECC.ren_gidnumer is null then 1 else 0 end,
    1
    from cdn.recnag RN
    join cdn.twrkarty NAG on Nag.twr_kod=@kod_zr and RN.ren_TypKompletacji = 1 and RN.ren_TwrLP = @lp_zr
    and RN.ren_twrtyp = NAG.twr_gidtyp and RN.ren_twrnumer = NAG.twr_gidnumer
    join cdn.recelem on RN.ren_gidtyp = ree_gidtyp and RN.ren_gidnumer = ree_gidnumer and (ree_typpozycji = 1 or ree_typpozycji = 3)
    and ree_archiwalny = 0
    join cdn.twrkarty REC on ree_twrtyp = REC.twr_gidtyp and ree_twrnumer = REC.twr_gidnumer
    left outer join cdn.recnag RECC on REC.twr_gidnumer = RECC.ren_twrnumer and RECC.ren_typkompletacji = 1 and RECC.ren_archiwalna = 0 and RECC.ren_domyslna = 1
    group by Rec.Twr_kod,Rec.Twr_nazwa,Ree_Jmz,ree_twrtyp,ree_twrfirma,
    ree_twrnumer,RN.ren_ilosc,Rec.Twr_Wartosc1, RECC.ren_gidnumer

    update R
    set R.przegladnieto=1
    from @skladniki_rec R
    join cdn.twrkarty on twr_kod = R.kod
    left outer join cdn.recnag on twr_gidnumer = ren_twrnumer and ren_typkompletacji = 1 and ren_archiwalna = 0 and ren_domyslna = 1
    where ren_gidnumer is null


    while (select max(kod) from @skladniki_rec where przegladnieto=0) IS NOT NULL
    begin

    select @poziom=@poziom+1

    select @kod_akt=kod,@ilosc_suma=ilosc,@poziom_pomoc=poziom
    from @skladniki_rec
    where kod=(select max(kod) from @skladniki_rec where przegladnieto=0)
    and poziom=(select min(poziom) from @skladniki_rec where przegladnieto=0
    and kod=(select max(kod) from @skladniki_rec where przegladnieto=0))

    delete from @tabela_Bartek_skladniki_receptury_tymczsowa

    INSERT INTO @tabela_Bartek_skladniki_receptury_tymczsowa
    select distinct(Rec2.Twr_kod)
    from cdn.recnag rnag2
    join cdn.twrkarty NAG2 on Nag2.twr_kod=@kod_akt and rnag2.ren_TypKompletacji = 1 and ren_domyslna = 1
    and rnag2.ren_twrtyp = NAG2.twr_gidtyp and rnag2.ren_twrnumer = NAG2.twr_gidnumer

    join cdn.recelem relem on rnag2.ren_gidtyp = relem.ree_gidtyp and rnag2.ren_gidnumer = relem.ree_gidnumer
    and (relem.ree_typpozycji = 1 or relem.ree_typpozycji = 3) and relem.ree_archiwalny = 0
    join cdn.twrkarty REC2 on relem.ree_twrtyp = REC2.twr_gidtyp and relem.ree_twrnumer = REC2.twr_gidnumer

    select @numer_akt=numer
    from @skladniki_rec
    where kod=@kod_akt and poziom=@poziom_pomoc

    INSERT INTO @skladniki_rec
    select @kod_zr,
    (select @numer_akt+convert(varchar,count(*)+1)+'.'
    from @tabela_Bartek_skladniki_receptury_tymczsowa
    where kod<Rec.Twr_kod
    ),
    Rec.Twr_kod,@lp_zr,Rec.Twr_nazwa,round(sum(Ree_ilosc)*@ilosc_suma/RN.ren_ilosc,4),Ree_jmz,
    Rec.Twr_Wartosc1,
    case when RECC.ren_gidnumer is null then 1 else 0 end,
    @poziom
    from cdn.recnag RN
    join cdn.twrkarty NAG on Nag.twr_kod=@kod_akt and RN.ren_TypKompletacji = 1 and RN.ren_domyslna = 1
    and RN.ren_twrtyp = NAG.twr_gidtyp and RN.ren_twrnumer = NAG.twr_gidnumer
    join cdn.recelem on RN.ren_gidtyp = ree_gidtyp and RN.ren_gidnumer = ree_gidnumer and
    (ree_typpozycji = 1 or ree_typpozycji = 3) and ree_archiwalny = 0
    join cdn.twrkarty REC on ree_twrtyp = REC.twr_gidtyp and ree_twrnumer = REC.twr_gidnumer
    left outer join cdn.recnag RECC on REC.twr_gidnumer = RECC.ren_twrnumer and RECC.ren_typkompletacji = 1 and RECC.ren_archiwalna = 0 and RECC.ren_domyslna = 1
    group by Rec.Twr_kod,Rec.Twr_nazwa,Ree_Jmz,ree_twrtyp,ree_twrfirma,
    ree_twrnumer,RN.ren_ilosc,Rec.Twr_Wartosc1, RECC.ren_gidnumer


    update @skladniki_rec
    set przegladnieto=1
    where kod=@kod_akt and poziom=@poziom_pomoc

    end
    return

    end
    Any ideas what may be the reason of this behaviour? Maybe 2005 server is affecting by something that sql 2000 server wasn't.
    Thanks in advance!

  2. chopeen Member

    Try to eliminate subselects. My experience (I migrated from SQL Server 2000 to 2005 about a year ago) shows SQL Server 2005 really cannot cope with them.
  3. satya Moderator

  4. chopeen Member

  5. Nshan New Member

    try to use temporary table instead of a variable table.

Share This Page