locked
CTE - avagy az uj technologia... RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     

    lattam, hogy 2005-ben mar van CTE lehetoseg, ami azon kivul, hogy lehet vele rekurziv selectet irni (VEGRE!) lehetoseget ad arar is talan gyorsabb legyen nehany eddig "csunya" megoldas.

     

    meg is neztem par dolgot...

    tipikus hogy egy history tablabol az aug 1 elotti utolso allapot kellene.

    nezzuk 3 megoldast:

    egy jol atlathato Subselectes megoldas:

     

    Code Snippet

    -- subselect

    select h.customerNo, h.stateid , h.MakerDate, h.Historyid

    from view_customerdetailsall_hist h

    join (select customerno, max(HistoryId) as LastId, max(MakerDate) as MD

    from view_customerdetailsall_hist

    where makerdate <'2007.08.01'

    group by CustomerNo

    ) c

    on c.LastId = h.HistoryId

    order by h.CustomerNo

    -- cost 9.26052

     

     

    mit lathato ennek a Costja 9.2

     

    nezzuk ezt CTE-vel:

    Code Snippet

    -- CTE

    with Cust

    as (select customerno, max(HistoryId) as LastId

    from view_customerdetailsall_hist

    where makerdate <'2007.08.01'

    group by CustomerNo)

    select h.customerNo, h.stateid , h.MakerDate, h.Historyid

    from view_customerdetailsall_hist h

    join Cust c

    on c.LastId = h.HistoryId

    order by h.CustomerNo

    -- cost 9.26052

     

    nahat, ugyan annyi! Sot, megneztem az ExecPlan is total ugyan az lett. mar csak azt kell eldonteni, hogy melyik az olvashatobb Smile

     

    de persze lehet ezt maskent is, #temp tablaval:

    Code Snippet

    -- temp tabla

    select customerno, max(HistoryId) as LastId, max(MakerDate) as MD

    into #t

    from view_customerdetailsall_hist

    where makerdate <'2007.08.01'

    group by CustomerNo

    -- 12990 rekord

     

    create clustered index #ixxx on #t (LastId)

     

    select h.customerNo, h.stateid , h.MakerDate, h.Historyid

    from view_customerdetailsall_hist h

    join #t c

    on c.LastId = h.HistoryId

    join #t c2

    on c2.MD = h.MakerDate

    and c2.CustomerNo = h.CustomerNo

    order by h.CustomerNo

    --cost 5.88222 + 0 + 0 + 3.23197 = 9.11419

     

     

    mint lathato ez kicsivel kisebb cost-ot igenyel, (futasidoben pedig kb 80%at igenyelte csak mint az elozo ketto)

     

    no de nezzuk a kovetkezo, kicsit muvi problemat: (lsd kov hsz)
    Wednesday, August 15, 2007 12:17 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    mi van, ha arra a franya subselectre 2x van szuksegem az adott selectben?

    kicsit eroltetett a pelda de pl kihozta, hogy 4 helyen hiba van,mert nem a legmagasabb historyid tartozik a legmagasabb datumhoz Smile

     

    tehat a pelda 2 subselecttel:

    Code Snippet

    select h.customerNo, h.stateid , h.MakerDate, h.Historyid

    from view_customerdetailsall_hist h

    join (select customerno, max(HistoryId) as LastId, max(MakerDate) as MD

    from view_customerdetailsall_hist

    where makerdate <'2007.08.01'

    group by CustomerNo

    ) c

    on c.LastId = h.HistoryId

    join (select customerno, max(HistoryId) as LastId, max(MakerDate) as MD

    from view_customerdetailsall_hist

    where makerdate <'2007.08.01'

    group by CustomerNo

    ) c2

    on c2.MD = h.MakerDate

    and c2.CustomerNo = h.CustomerNo

    order by h.CustomerNo

    -- cost 12.0747

     

    megnzetm hogy mi van ha a 2 subselect nem tok egyforma, hanem az elso csak a max(HistoryId)-t a 2. csak a max(MakerDate)-t tartalmazza, de semmi se valtozott tulkepp.

    ennek a costja kcisivel tobb mint az elozo verzioke, de a futasido az brutalisan tobb lett! mig az elzo peldak 3-4 sec alatt futottak le, addig ez tobb mint 30 percet fut!

     

    hogy nez ez ki CTE-vel? kicsit olvashatobb talan:

    Code Snippet

    with Cust

    as (select customerno, max(HistoryId) as LastId, max(MakerDate) as MD

    from view_customerdetailsall_hist

    where makerdate <'2007.08.01'

    group by CustomerNo)

    select h.customerNo, h.stateid , h.MakerDate, h.Historyid

    from view_customerdetailsall_hist h

    join Cust c

    on c.LastId = h.HistoryId

    join Cust c2

    on c2.MD = h.MakerDate

    and c2.CustomerNo = h.CustomerNo

    order by h.CustomerNo

    -- cost 12.0747

     

     

    termeszetesen(?) az exec plan a cost es a futasido is ugyan az mint a subselectes verzional

     

    es jon az ucso verzio a #temp tablaval:

    Code Snippet

    select customerno, max(HistoryId) as LastId, max(MakerDate) as MD

    into #t

    from view_customerdetailsall_hist

    where makerdate <'2007.08.01'

    group by CustomerNo

    create clustered index #ixxx on #t (LastId)

    create index #ixxx2 on #t (CustomerNo,MD)

    select h.customerNo, h.stateid , h.MakerDate, h.Historyid

    from view_customerdetailsall_hist h

    join #t c

    on c.LastId = h.HistoryId

    join #t c2

    on c2.MD = h.MakerDate

    and c2.CustomerNo = h.CustomerNo

    order by h.CustomerNo

    --cost select 5.88084 + 0 + 0 + 3.76522 = 9.64606

     

     

    mint lathato a cost kicsit jobb, nem sokkal roszabb mint amikor csak 1 join volt.

    a futas ido pedig .....

    5 masodperc...

     

     

     

     

    Wednesday, August 15, 2007 12:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     

    a kerdes:

    valamit roszul csinaltam? vagy rekurzion kivul tul sok mindenre nem jo a CTE lehetoseg?

    Wednesday, August 15, 2007 12:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     

    A legegyszerűbb, ha a CTE-re úgy gondolsz mint egy lokális view-ra, az pedig csak 1 plusz, hogy rekurzívan is tud működni. A mostani esetekben meg egészen egyszerűen csak azért volt gyorsabb a temp tábla, mert egészen egyszerűen kevesebbszer ment végig az adathalmazon. Itt egy egyszerűbb példa, amivel könnyebb megnézni. (CTE nincs benne, de az sem lenne gyorsabb mint az utolsó verzió):

     

    https://www.netacademia.net/tudastar/default.aspx?upid=15544

     

    ps:

    ja és a cross apply körül is körbenéznék, mert bizonyos esetekben az szokott a gyorsabb lenni

    Tuesday, August 21, 2007 7:30 PM
    Moderator