Welcome to Sign in | Help
in Search

optimizare SELECT (sql server 2000)

Last post 08-10-2007, 1:47 PM by Alex. 13 replies.
Sort Posts: Previous Next
  •  08-07-2007, 4:04 PM 2411

    optimizare SELECT (sql server 2000)

    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.

     

     

  •  08-07-2007, 4:17 PM 2412 in reply to 2411

    Re: optimizare SELECT (sql server 2000)

    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?
  •  08-07-2007, 9:11 PM 2413 in reply to 2412

    Re: optimizare SELECT (sql server 2000)

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

  •  08-08-2007, 8:52 AM 2415 in reply to 2413

    Re: optimizare SELECT (sql server 2000)

    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.

  •  08-08-2007, 9:01 AM 2416 in reply to 2412

    Re: optimizare SELECT (sql server 2000)

    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.

  •  08-08-2007, 12:37 PM 2419 in reply to 2416

    Re: optimizare SELECT (sql server 2000)

    Î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

  •  08-08-2007, 1:02 PM 2420 in reply to 2419

    Re: optimizare SELECT (sql server 2000)

    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

     

  •  08-08-2007, 1:36 PM 2421 in reply to 2420

    Re: optimizare SELECT (sql server 2000)

    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.

  •  08-09-2007, 1:25 PM 2430 in reply to 2421

    Re: optimizare SELECT (sql server 2000)

    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

  •  08-09-2007, 2:23 PM 2432 in reply to 2430

    Re: optimizare SELECT (sql server 2000)

    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.

  •  08-09-2007, 4:45 PM 2434 in reply to 2432

    Re: optimizare SELECT (sql server 2000)

    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

  •  08-10-2007, 10:43 AM 2437 in reply to 2434

    Re: optimizare SELECT (sql server 2000)

    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.

  •  08-10-2007, 12:27 PM 2439 in reply to 2437

    Re: optimizare SELECT (sql server 2000)

    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

  •  08-10-2007, 1:47 PM 2444 in reply to 2439

    Re: optimizare SELECT (sql server 2000)

    dureaza 1 secunda

    si intoarce ~15.000 de inregistrari

    multumesc mult

    alex.

View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems