Welcome to Sign in | Help
in Search

optimizare procedura sql server 2000

Last post 02-08-2008, 4:43 PM by xmldeveloper. 2 replies.
Sort Posts: Previous Next
  •  02-05-2008, 5:18 PM 3954

    optimizare procedura sql server 2000

    salut, are cineva inspiratie si timp sa ma ajute sa optimizez si eu urmatoarea procedura.....sau o idee ceva.....multumesc anticipat

    am procedura:

    CREATE PROCEDURE dbo.cg_GetCentralizatorRamasite_PF_PJ_ListaPeCreanta

     @dataCurenta smalldatetime,
     @idprim uniqueidentifier,
     @filtru_tip_persoana tinyint
    AS

    IF @filtru_tip_persoana = 1
    begin

     select distinct 
      dbo.GetGrupareByIDTipMatricola(dbf.id_tipmatr,dbf.id_matr) as grup,
      dbf.an,
      coalesce(
        (select sum(suma)
        from gotax.dbo.debite_pf p
        inner join gotax.dbo.roluri_fiz r
         on r.id = p.id_rol
        where  
         p.id_tipdebit = 2
         and p.an = dbf.an
         and  dbo.GetGrupareByIDTipMatricola(dbf.id_tipmatr,dbf.id_matr)
          =
          dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
         and r.id_prim = @idprim
        )
       ,0)
       as ramasite,
      coalesce(
        (select sum(suma)
        from gotax.dbo.debite_pf p
        inner join gotax.dbo.roluri_fiz r
         on r.id = p.id_rol
        where  
         (p.id_tipdebit = 5 or p.id_tipdebit = 8)
         and p.an = dbf.an
         and  dbo.GetGrupareByIDTipMatricola(dbf.id_tipmatr,dbf.id_matr)
          =
          dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
         and r.id_prim = @idprim
        )
       ,0)
       as accesorii
       ,dbf.id_tipmatr
     from   gotax.dbo.roluri_fiz r
     inner join  gotax.dbo.debite_pf dbf
       on r.id = dbf.id_rol
     where  r.id_prim = @idprim and
      dbf.id_tipdebit in (2,5,8)
     order by dbf.id_tipmatr
     
    end


    IF @filtru_tip_persoana = 2
    begin

     select distinct 
      dbo.GetGrupareByIDTipMatricola(dbj.id_tipmatr,dbj.id_matr) as grup,
      dbj.an,
      coalesce(
        (select sum(suma)
        from gotax.dbo.debite_pj p
        inner join gotax.dbo.roluri_jur r
         on r.id = p.id_rol
        where  
         p.id_tipdebit = 2
         and p.an = dbj.an
         and  dbo.GetGrupareByIDTipMatricola(dbj.id_tipmatr,dbj.id_matr)
          =
          dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
         and r.id_prim = @idprim
        )
       ,0)
       as ramasite,
      coalesce(
        (select sum(suma)
        from gotax.dbo.debite_pj p
        inner join gotax.dbo.roluri_jur r
         on r.id = p.id_rol
        where  
         (p.id_tipdebit = 5 or p.id_tipdebit = 8)
         and p.an = dbj.an
         and  dbo.GetGrupareByIDTipMatricola(dbj.id_tipmatr,dbj.id_matr)
          =
          dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
         and r.id_prim = @idprim
        )
       ,0)
       as accesorii
       ,dbj.id_tipmatr
     from   gotax.dbo.roluri_jur r
     inner join  gotax.dbo.debite_pj dbj
       on r.id = dbj.id_rol
     where  r.id_prim = @idprim and
      dbj.id_tipdebit in (2,5,8)
     order by dbj.id_tipmatr
    end
    GO

    si functia care e folosita in procedura:

    CREATE FUNCTION GetGrupareByIDTipMatricola (@id_tipmatr tinyint,@id_matr uniqueidentifier) 
    RETURNS varchar(100) AS 
    BEGIN
    declare @aux varchar(100)

    if @id_tipmatr = 1 begin set @Aux = (select label from gotax.dbo.tip_matricole where id = 1) end

    if @id_tipmatr = 2 begin set @Aux =(select label from gotax.dbo.tip_matricole where id = 2) end

    if @id_tipmatr = 3 begin set @Aux = (select label from gotax.dbo.tip_matricole where id = 3) end

    if @id_tipmatr = 4 begin set @Aux =
       (select denumire from gotax.dbo.tip_alte_taxe where id =
        coalesce(
         (select id_tipalte from gotax.dbo.matricole_alte_pf where id = @id_matr),
         (select id_tipalte from gotax.dbo.matricole_alte_pj where id = @id_matr)
         )
       ) end

    return @aux
    END

    alex.

     

  •  02-08-2008, 11:06 AM 3968 in reply to 3954

    Re: optimizare procedura sql server 2000

    Eu zic ca s-ar putea imbunatatii performantele daca nu ai folosi atatea apeluri de functie. Adica ideea pe care ma bazez este sa incerci sa inlocuiesti functia GetGrupareByIDTipMatricola
    cu un select in care sa folosesti instructiunea CASE.

    E o parere, sper sa iti fie utila.

    MCP (C Sharp)
    Software Engineer,
    Cluj Napoca
  •  02-08-2008, 4:43 PM 3970 in reply to 3954

    Re: optimizare procedura sql server 2000

    uniqueidentifier ?

    Lucrati pentru o primarie nu ? Directia Taxe si Impozite Cool


    Cristian Andrei Lefter, SQL Server MVP
    MCT, MCSA, MCDBA, MCAD, MCSD .NET,
    MCTS, MCITP - Database Administrator SQL Server 2005
    http://sqlserver.ro
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems