T-SQL

Re: optimizare SELECT (sql server 2000)

Alex — Fri, 10 Aug 2007 10:47:45 GMT

dureaza 1 secunda

si intoarce ~15.000 de inregistrari

multumesc mult

alex.

Re: optimizare SELECT (sql server 2000)

rsocol — Fri, 10 Aug 2007 09:27:18 GMT

Dacă e imposibil ca afm să fie NULL, atunci UNION-ul din acest view e inutil (şi deranjant dpdv al performanţei), deci ar trebui scos, obţinând:

select ... from customer cus
left join custrespperson resp on cus.id = resp.perid
where isactive = 1

Şi chiar şi dacă ar fi fost posibil ca afm să fie NULL, cred că UNION-ul tot ar fi trebuit scos, pentru că e absurd.

Sunt curios cât durează interograrea de mai sus acum, după ce ai scos şi acest UNION din view.

Răzvan

Re: optimizare SELECT (sql server 2000)

Alex — Fri, 10 Aug 2007 07:43:03 GMT

afm sa fie null e cam imposibil ca e restrictionat din aplicatie....nu poate salva daca nu introduce acest afm (cod_fiscal)

coloana acc_id este id-ul din tabela customer care e identity(1,1) si cheie primara

multumesc.

alex.

Re: optimizare SELECT (sql server 2000)

rsocol — Thu, 09 Aug 2007 13:45:31 GMT

Am rearanjat puţin query-ul mai sus (fără să rescriu nimic) şi este ceva de genul:

select ... from (
    select * from (select * from customer where isactive = 1) y
    union all
    select * from customer where afm is null and isactive = 1
) cus left join custrespperson resp on cus.id = resp.perid

Chiar vrei să apară de două ori clienţii care nu au cod fiscal ? Sau e imposibil să fie îndeplinită condiţia "afm is null" ? Dacă e posibil, atunci view-ul (în forma actuală) returnează duplicate (inclusiv pentru coloana acc_id, care am zis că poate fi considerată cheie primară) !

Răzvan

Re: optimizare SELECT (sql server 2000)

Alex — Thu, 09 Aug 2007 11:23:30 GMT

e mai mult decat perfect.....dureaza 2 sec

multumesc mult.

alex.

P.S. asta e celalalt view...asa ca idee

create view [dbo].[vw_CRMImportClienti]
as
select cus.id acc_id, isnull(cus.name,'-') acc_name, isnull(cus.afm,'-') acc_new_codfiscal,
cus.phone11 acc_telephone1, cus.phone12 acc_telephone2, cus.fax1 acc_fax,
cus.webpage acc_websiteurl, cus.email acc_emailaddress1,'Adresa de facturare' acc_address1_name,
cus.city1 acc_address1_city, cus.street1 acc_address1_line1, cus.district1 acc_new_judet_sector,
cus.zipcode1 acc_address1_postalcode, cus.phone22 acc_address1_telephone1,
1 acc_address1_addresstypecode, --1 = Bill to address
cus.remarks acc_description, cus.crdlimit acc_creditlimit,
'Adresa de livrare' adr_name, isnull(cus.street2,'-') adr_line1, cus.city2 adr_city,
cus.zipcode2 adr_postalcode, 2 adr_addresstypecode, --2=Ship to address
cus.phone21 adr_telephone1, cus.telex2 adr_telephone2, cus.telex1 adr_primarycontactname,
resp.name per_lastname, '-' per_firstname, resp.privatephone per_telephone1,
resp.mobile per_mobilephone, resp.email per_emailaddress1, resp.remarks per_jobtitle,
isnull(right(code,len(code)-3),'-') acc_new_codatlantis, cus.r_CRMGUID acc_GUID ,
cus.comid, cus.braid
from

(select * from

(select cus.* from customer cus

where cus.isactive = 1)y

union all

select * from customer where afm is null and isactive = 1) cus
left join custrespperson resp on cus.id = resp.perid

alex.

Re: optimizare SELECT (sql server 2000)

rsocol — Thu, 09 Aug 2007 10:25:52 GMT

OK, în cazul în care am putea considera că acc_id e cheie primară în vw_crmimportclienti, atunci putem rescrie următoarea porţiune:

    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    inner join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid
    where cst.comid = 3

    union all

    select *, null IDSUP from vw_crmimportclienti cst
    where acc_id not in (
        select acc_id from vw_crmimportclienti cst
        inner join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid
        where cst.comid = 3
    )

astfel:

select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
left join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid and cst.comid = 3

Cu această ocazie, am putea să scoatem tabela temporară (acum, că e folosită doar odată), şi obţinem:

select x.*,y.code CodAgent from (
    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    left join (
        select acc_new_codfiscal as cstafm, min(acc_id) as cstid
        from vw_crmimportclienti
        where acc_new_codfiscal in (
            select acc_new_codfiscal from vw_crmimportclienti
            where acc_new_codfiscal like '%[0-9]%'
            group by acc_new_codfiscal
            having count(acc_new_codfiscal) > 1)
        group by acc_new_codfiscal
    ) x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid and cst.comid = 3
) x inner join (
    select sal.Code code,ftr.cusid cusid
    from (
        SELECT ftr.cusid, min(ftr.id) ftrid
        FROM dbo.FINTRADE ftr
        INNER JOIN dbo.ITEMTRANS itt ON ftr.ID=itt.FTRID
        WHERE ftr.SOURCE=5 AND itt.BILLEDOUTPUT=1
        GROUP BY cusid
     ) z join fintrade ftr on ftr.id=z.ftrid
     INNER JOIN Salesman sal on ftr.COLIDSALESMAN = sal.id
) y on x.acc_id=y.cusid

Apoi, am putea să înlocuim porţiunea:

    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    left join (
        select acc_new_codfiscal as cstafm, min(acc_id) as cstid
        from vw_crmimportclienti
        where acc_new_codfiscal in (
            select acc_new_codfiscal from vw_crmimportclienti
            where acc_new_codfiscal like '%[0-9]%'
            group by acc_new_codfiscal
            having count(acc_new_codfiscal) > 1)
        group by acc_new_codfiscal
    ) x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid and cst.comid = 3

cu:

    select a.*, (
           SELECT MIN(b.acc_id) FROM vw_crmimportclienti b
           WHERE a.acc_new_codfiscal=b.acc_new_codfiscal
           AND b.acc_new_codfiscal like '%[0-9]%'
           AND a.acc_id>b.acc_id AND a.comid=3
       ) as IDSUP
    from vw_crmimportclienti a

Obţinând în final query-ul:

select x.*,y.code CodAgent from (
    select a.*, (
           SELECT MIN(b.acc_id) FROM vw_crmimportclienti b
           WHERE a.acc_new_codfiscal=b.acc_new_codfiscal
           AND b.acc_new_codfiscal like '%[0-9]%'
           AND a.acc_id>b.acc_id AND a.comid=3
       ) as IDSUP
    from vw_crmimportclienti a
) x inner join (
    select sal.Code code,ftr.cusid cusid
    from (
        SELECT ftr.cusid, min(ftr.id) ftrid
        FROM dbo.FINTRADE ftr
        INNER JOIN dbo.ITEMTRANS itt ON ftr.ID=itt.FTRID
        WHERE ftr.SOURCE=5 AND itt.BILLEDOUTPUT=1
        GROUP BY cusid
     ) z join fintrade ftr on ftr.id=z.ftrid
     INNER JOIN Salesman sal on ftr.COLIDSALESMAN = sal.id
) y on x.acc_id=y.cusid

Dacă e nevoie de mai multe optimizări, cred că ar trebui să intrăm în view-ul vw_crmimportclienti şi să vedem ce e în plus pe acolo (sau ce indecşi noi am putea face pentru acest query).

Răzvan

Re: optimizare SELECT (sql server 2000)

Alex — Wed, 08 Aug 2007 10:36:10 GMT

Razvan.....daca-ti zic ceva acum cred ca o sa-ti vina sa ma strangi de gat :)

e in totalitate vina mea recunosc pt ca nu am zis de la inceput ca acest select va fi folosit intr-un view si

"Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables."

mii de scuze :(

alex.

Re: optimizare SELECT (sql server 2000)

Alex — Wed, 08 Aug 2007 10:02:28 GMT

da...dureaza mult mai putin :)

prima varianta a scriptului dureaza 11 sec iar cea de a 2-a dureaza 7 sec (super bine chiar)

multumesc mult.

intredevar si acc_is e intotdeauna ne-null si o putem considera si "cheie primara" pe viewul respectiv.....nu sunt duplicate in acc_id

alex.

edit: am marit valoarea la

cstafm varchar(250)

ca-mi dadea eroare

Re: optimizare SELECT (sql server 2000)

rsocol — Wed, 08 Aug 2007 09:37:33 GMT

În primul rând, în loc de "acc_new_codfiscal like '%1%' or acc_new_codfiscal like '%2%' or ..." putem să scriem "acc_new_codfiscal like '%[0-9]%'".

Apoi, observăm că se repetă (de 4 ori) următoarea porţiune:

        select min(acc_id) cstid, min(acc_new_codfiscal) cstafm from vw_crmimportclienti
        where acc_new_codfiscal in (
            select acc_new_codfiscal from vw_crmimportclienti
            where acc_new_codfiscal like '%[0-9]%'
            group by acc_new_codfiscal
            having count(acc_new_codfiscal) > 1)
        group by acc_new_codfiscal

Pentru a fi mai uşor de citit, am putea folosi un CTE în SQL 2005 sau un view în SQL 2000. Totuşi, în acest caz, cred că am obţine o performanţă ceva mai bună cu o tabelă temporară. Pe de altă parte, dacă avem "group by acc_new_codfiscal" atunci "min(acc_new_codfiscal)" înseamnă pur şi simplu "acc_new_codfiscal". Deci:

CREATE TABLE #T (
   cstafm varchar(10) PRIMARY KEY,
   cstid int
)

INSERT INTO #T (cstafm, cstid)
select acc_new_codfiscal, min(acc_id) from vw_crmimportclienti
where acc_new_codfiscal in (
   select acc_new_codfiscal from vw_crmimportclienti
   where acc_new_codfiscal like '%[0-9]%'
   group by acc_new_codfiscal
   having count(acc_new_codfiscal) > 1)
group by acc_new_codfiscal

select x.*,y.code CodAgent from (
   select cst.*, x.cstid IDSUP from vw_crmimportclienti cst
   left join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id > x.cstid
   where x.cstid is not null and cst.comid = 3

   union all

   select cst.*, null IDSUP from vw_crmimportclienti cst
   left join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id = x.cstid
   where x.cstid is not null and cst.comid = 3

   union all

   select *, null IDSUP from vw_crmimportclienti cst
   where acc_id not in (
       select acc_id from vw_crmimportclienti cst
       left join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id > x.cstid
       where x.cstid is not null and cst.comid = 3

       union all

       select acc_id from vw_crmimportclienti cst
       left join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id = x.cstid
       where x.cstid is not null and cst.comid = 3
   )
) x inner join (
   select sal.Code code,ftr.cusid cusid
   from (
       SELECT ftr.cusid, min(ftr.id) ftrid
       FROM dbo.FINTRADE ftr
       INNER JOIN dbo.ITEMTRANS itt ON ftr.ID=itt.FTRID
       WHERE ftr.SOURCE=5 AND itt.BILLEDOUTPUT=1
       GROUP BY cusid
   ) z join fintrade ftr on ftr.id=z.ftrid
   INNER JOIN Salesman sal on ftr.COLIDSALESMAN = sal.id
) y on x.acc_id=y.cusid

DROP TABLE #T

Te-aş ruga să încerci query-ul de mai sus, să îmi spui cum stăm cu performanţa până aici.

În continuare, am putea să mai simplificăm UNION-urile respective, astfel:

1. Dacă avem LEFT JOIN iar apoi condiţie cu "WHERE x.cstid IS NOT NULL" înseamnă că de fapt avem INNER JOIN.

2. Putem primele două componente ale UNION ALL-ului diferă doar prin condiţia "cst.acc_id > x.cstid", respectiv "cst.acc_id = x.cstid" şi prin coloana IDSUP (care în al doilea caz e NULL), deci putem să le rescriem astfel:

    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    inner join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid
    where cst.comid = 3

3. În UNION ALL-ul din subquery-ul care urmează după "where acc_id not in (", diferenţa este similară cu cea de mai sus, numai că nu mai avem deloc coloana IDSUP, deci putem să o simplificăm într-un mod similar, obţinând în final:

CREATE TABLE #T (
    cstafm varchar(10) PRIMARY KEY,
    cstid int
)

INSERT INTO #T (cstafm, cstid)
select acc_new_codfiscal, min(acc_id) from vw_crmimportclienti
where acc_new_codfiscal in (
    select acc_new_codfiscal from vw_crmimportclienti
    where acc_new_codfiscal like '%[0-9]%'
    group by acc_new_codfiscal
    having count(acc_new_codfiscal) > 1)
group by acc_new_codfiscal

select x.*,y.code CodAgent from (
    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    inner join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid
    where cst.comid = 3

    union all

    select *, null IDSUP from vw_crmimportclienti cst
    where acc_id not in (
        select acc_id from vw_crmimportclienti cst
        inner join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid
        where cst.comid = 3
    )
) x inner join (
    select sal.Code code,ftr.cusid cusid
    from (
        SELECT ftr.cusid, min(ftr.id) ftrid
        FROM dbo.FINTRADE ftr
        INNER JOIN dbo.ITEMTRANS itt ON ftr.ID=itt.FTRID
        WHERE ftr.SOURCE=5 AND itt.BILLEDOUTPUT=1
        GROUP BY cusid
    ) z join fintrade ftr on ftr.id=z.ftrid
    INNER JOIN Salesman sal on ftr.COLIDSALESMAN = sal.id
) y on x.acc_id=y.cusid

DROP TABLE #T

Te rog încearcă şi query-ul de mai sus şi spune-mi cum stăm cu performanţa.

Următorul pas ar fi unificarea celor două componente a UNION-ului care a mai rămas, dar aici nu mai sunt foarte sigur că obţinem aceleaşi rezultate (în mod normal, ar trebui să fie la fel, dar fac nişte presupuneri în privinţa datelor din vw_crmimportclienti, pe care nu pot să le exprim foarte clar). Aşa că înainte de a face acest pas, aş avea nevoie să ştiu dacă putem considera că acc_id e cheie primară în vw_crmimportclienti (îmi dau seama că e un view şi că nu are definite niciun fel de chei, dar vreau să ştiu dacă luând în considerare cheile unice pe tabelele de bază şi modul în care e scris view-ul vw_crmimportclienti, rezultă dpdv logic că acc_id e totdeauna ne-nul şi că nu pot apare două rânduri în acest view cu aceeaşi valoare pentru acc_id ?).

Răzvan

Re: optimizare SELECT (sql server 2000)

Alex — Wed, 08 Aug 2007 06:01:30 GMT

intradevar e greu de citit mai ales la prima vedere......

o sa incerc sa fac o diagrama cu structura tabelelor...poate asa o sa inteleg si eu mai bine ce e pe acolo :)....initial scriptul (care nu a fost facut de mine) era doar pana la ultimul join (pana la ultimul X).......el initial intorcea niste clienti si date personale ale acestora....iar eu am mai adaugat si acel ultim inner join (cu Y) pt ca s-a vrut si codul agentului de vanzare pt acei clienti (acel CODE)

multumesc

alex.

Re: optimizare SELECT (sql server 2000)

Alex — Wed, 08 Aug 2007 05:52:42 GMT

am incercat Index Tuning dar mi-l imbunatateste cu 0%....imi zice el pe acolo sa sterg niste index-uri dar ca procent de imbunatatire imi arata 0

alex.

Re: optimizare SELECT (sql server 2000)

B_gd_n[ ]Sahlean — Tue, 07 Aug 2007 18:11:39 GMT

Pentru inceput presupunand ca folosesti 2000 iti recomand Index Tuning Wizard din Enterprise Manager.

Re: optimizare SELECT (sql server 2000)

Diana — Tue, 07 Aug 2007 13:17:01 GMT

E mai dificil de citit. Poti sa "pui" aici o diagrama cu structura tabelelor si relatiile dintre ele? Ce campuri trebuie sa contina rezultatul - de fapt ce trebuie sa "produca" acest script?

optimizare SELECT (sql server 2000)

Alex — Tue, 07 Aug 2007 13:04:48 GMT

salut, vede cineva o varianta mai simpla la selectul asta (poate asta il si optimizeaza):

select x.*,y.code CodAgent from
(
select cst.*, x.cstid IDSUP from vw_crmimportclienti cst
left join
  (select min(acc_id) cstid, min(acc_new_codfiscal) cstafm from vw_crmimportclienti
where acc_new_codfiscal in (select acc_new_codfiscal from vw_crmimportclienti
     where (acc_new_codfiscal like '%1%' or acc_new_codfiscal like '%2%' or acc_new_codfiscal like '%3%'
or acc_new_codfiscal like '%4%' or acc_new_codfiscal like '%5%' or acc_new_codfiscal like '%6%'
or acc_new_codfiscal like '%7%' or acc_new_codfiscal like '%8%' or acc_new_codfiscal like '%9%' or acc_new_codfiscal like '%0%')
     group by acc_new_codfiscal
     having count(acc_new_codfiscal) > 1)
group by acc_new_codfiscal) x
  on cst.acc_new_codfiscal = x.cstafm and cst.acc_id > x.cstid
where x.cstid is not null
and cst.comid = 3

union all

select cst.*, null IDSUP from vw_crmimportclienti cst
left join
  (select min(acc_id) cstid, min(acc_new_codfiscal) cstafm from vw_crmimportclienti
where acc_new_codfiscal in (select acc_new_codfiscal from vw_crmimportclienti
     where (acc_new_codfiscal like '%1%' or acc_new_codfiscal like '%2%' or acc_new_codfiscal like '%3%'
or acc_new_codfiscal like '%4%' or acc_new_codfiscal like '%5%' or acc_new_codfiscal like '%6%'
or acc_new_codfiscal like '%7%' or acc_new_codfiscal like '%8%' or acc_new_codfiscal like '%9%' or acc_new_codfiscal like '%0%')
     group by acc_new_codfiscal
     having count(acc_new_codfiscal) > 1)
group by acc_new_codfiscal) x
  on cst.acc_new_codfiscal = x.cstafm and cst.acc_id = x.cstid
where x.cstid is not null
and cst.comid = 3

union all

select *, null IDSUP from vw_crmimportclienti cst
where acc_id not in
(select acc_id from vw_crmimportclienti cst
left join
  (select min(acc_id) cstid, min(acc_new_codfiscal) cstafm from vw_crmimportclienti
where acc_new_codfiscal in (select acc_new_codfiscal from vw_crmimportclienti
     where ( acc_new_codfiscal like '%1%' or acc_new_codfiscal like '%2%' or acc_new_codfiscal like '%3%'
or acc_new_codfiscal like '%4%' or acc_new_codfiscal like '%5%' or acc_new_codfiscal like '%6%'
or acc_new_codfiscal like '%7%' or acc_new_codfiscal like '%8%' or acc_new_codfiscal like '%9%' or acc_new_codfiscal like '%0%')
     group by acc_new_codfiscal
     having count(acc_new_codfiscal) > 1)
group by acc_new_codfiscal) x
  on cst.acc_new_codfiscal = x.cstafm and cst.acc_id > x.cstid
where x.cstid is not null
and cst.comid = 3

union all

select acc_id from vw_crmimportclienti cst
left join
  (select min(acc_id) cstid, min(acc_new_codfiscal) cstafm from vw_crmimportclienti
where acc_new_codfiscal in (select acc_new_codfiscal from vw_crmimportclienti
     where ( acc_new_codfiscal like '%1%' or acc_new_codfiscal like '%2%' or acc_new_codfiscal like '%3%'
or acc_new_codfiscal like '%4%' or acc_new_codfiscal like '%5%' or acc_new_codfiscal like '%6%'
or acc_new_codfiscal like '%7%' or acc_new_codfiscal like '%8%' or acc_new_codfiscal like '%9%' or acc_new_codfiscal like '%0%')
     group by acc_new_codfiscal
     having count(acc_new_codfiscal) > 1)
group by acc_new_codfiscal) x
  on cst.acc_new_codfiscal = x.cstafm and cst.acc_id = x.cstid
where x.cstid is not null
and cst.comid = 3)

) x

inner join

(
select sal.Code code,ftr.cusid cusid
from   (
  SELECT     ftr.cusid,min(ftr.id) ftrid
  FROM         dbo.FINTRADE ftr INNER JOIN
               dbo.ITEMTRANS itt ON ftr.ID = itt.FTRID
  WHERE     (ftr.SOURCE = 5) AND (itt.BILLEDOUTPUT = 1)
               group by cusid
) y
                join fintrade ftr on ftr.id=y.ftrid
  INNER JOIN Salesman sal
  on ftr.COLIDSALESMAN = sal.id

) y
on x.acc_id=y.cusid

intoarce ~20 de mii de inregistrari si dureaza ~20 sec

multumesc

alex.