Re: optimizare procedura sql server 2000

xmldeveloper — Fri, 08 Feb 2008 14:43:04 GMT

uniqueidentifier ?

Lucrati pentru o primarie nu ? Directia Taxe si Impozite

Re: optimizare procedura sql server 2000

cretzu — Fri, 08 Feb 2008 09:06:26 GMT

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.

optimizare procedura sql server 2000

Alex — Tue, 05 Feb 2008 15:18:57 GMT

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.

T-SQL

Re: optimizare procedura sql server 2000

Re: optimizare procedura sql server 2000

optimizare procedura sql server 2000