Cum se rescrie o interogare cu MAX în clauza WHERE (Administrarea bazelor de date, Server Sql, Sql Server 2008 R2)

Allen a intrebat.

Am o interogare cu o clauză max în clauza where clauză, care este lentă.

select count(*)
from   TableName tbl1
where  tbl1.id = (
                select max(tbl2.id) 
                from TableName tbl2
                where tbl2.companyId = tbl1.companyId)
and    tbl1.active = 2

Vă rog să rețineți că ambele tabele sunt aceleași.

Am rescris-o ca:

select count(*) 
from   TableName tbl1 
inner join (
                select max(id) as id, companyId 
                from TableName tbl2 
                where tbl2.active= 2
                group by companyId
        ) tbl2 
        on  tbl2.companyId = tbl1.companyId 
        and tbl2.id=tbl1.id

dar numărul de înregistrări este diferit.

Poate cineva să spună cum pot să o rescriu pentru o performanță mai bună?

Comentarii

  • Nu am analizat-o în detaliu, așa că nu voi posta un răspuns complet până când nu o voi face, dar la prima vedere, în prima declarație filtrați în funcție de steagul activ al tabelului din interogarea exterioară și în cea de-a doua declarație pe steagul activ al tabelului din subinterogare. Acest lucru ar da cu siguranță numărători diferite pentru unele date de intrare. –  > Por David Spillett.
  • Dacă (companyId, id) este unic, ar trebui să obțineți același număr de rânduri. Este acesta cazul? –  > Por Daniel Hutmacher.
  • Daniel, ce ar trebui să fac dacă nu sunt unice? –  > Por Allen.
  • Cred că ar fi mai bine să ne explici rezultatul pe care îl dorești, astfel încât să putem lucra pornind de la acesta, decât de la o bucată de SQL despre care nu sunt 100% sigur că obține rezultatul pe care îl aștepți. De asemenea, ar fi util să enumerați cheile și indicii care sunt prezente pe coloanele relevante din tabel. –  > Por David Spillett.
  • Este posibil ca numărul de rânduri returnate de interogarea originală să fie întotdeauna 0 sau 1? Subinterogarea cu MAX va returna 1 rând. Numărătoarea pe acest rezultat va rămâne un rând. Sau zero dacă nu există nicio înregistrare. Această interogare pare destul de inutilă. –  > Por Magier.
2 răspunsuri
Paul White

Rezumat

Adăugați indexul:

CREATE INDEX index_name 
ON TableName 
    (companyid DESC, id DESC) 
INCLUDE 
    (active);

Încercați această rescriere:

SELECT COUNT_BIG(*) 
FROM
(
    SELECT TOP (9223372036854775807) 
        TN1.active
    FROM TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id) 
            FROM TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

Detalii

Specificația originală a interogării pare a fi:

Câte rânduri există în tabelul în care activ = 2; și id este egală cu cea mai mare coloană id din toate rândurile din aceeași coloană companyid (fără coloana activă = 2, pentru claritate).

Există o modalitate de a obține aceste rezultate cu o singură scanare a unui index adecvat.

De exemplu, având în vedere tabelul:

CREATE TABLE #TableName
(
    companyid integer NOT NULL,
    id integer NOT NULL,
    active integer NOT NULL
);

Câteva rânduri de date de probă:

INSERT #TableName
    (companyid, id, active)
VALUES
    (1, 1, 2),
    (1, 2, 2),
    (1, 3, 0),
    (2, 2, 2),
    (2, 3, 2),
    (2, 3, 0);

Cu indexul:

CREATE INDEX index_name 
ON #TableName 
    (companyid DESC, id DESC) 
INCLUDE 
    (active);

O interogare (bazată pe original) pentru a găsi rândurile fără predicat (activ = 2) este:

SELECT
    TN1.active
FROM #TableName AS TN1
WHERE
    TN1.id =
    (
        SELECT MAX(TN2.id) 
        FROM #TableName AS TN2
        WHERE
            TN2.companyid = TN1.companyid
    );

Planul de execuție pentru această interogare prezintă un o singură scanare a indexului, în ciuda faptului că interogarea face referire la aceeași tabelă de două ori:

Ideea de bază este de a scana indexul în ordinea cheilor, returnând rândul (rândurile) cu cea mai mare valoare. id în cadrul fiecărui grup de companyid valori.

Din păcate, nu putem adăuga pur și simplu predicatul active = 2 la această interogare fără a întrerupe transformarea optimizatorului care ne oferă scanarea unică. Problema (dacă vă interesează) este că noul predicat este împins în josul arborelui planului, rezultând o formă care nu mai corespunde transformării „group by top”.

O soluție de rezolvare adaugă un element nesemnificativ TOP în jurul părții din interogare pe care dorim să o transformăm, împiedicând noul predicat să fie împins dincolo de ea:

SELECT COUNT_BIG(*) 
FROM
(
    SELECT TOP (9223372036854775807) 
        TN1.active
    FROM #TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id) 
            FROM #TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

Planul de execuție este similar cu cel anterior, cu un Top suplimentar (cel fără sens, bigint.max, pe care l-am adăugat), un Filter pentru active = 2 și un Stream Aggregate final pentru a număra rândurile:

Nu vă pot promite că veți obține această formă de plan cu datele reale sau că va fi mai rapid. Totul depinde mai degrabă de numărul și distribuția rândurilor.

Noul Top are, de asemenea, efectul de a defavoriza un plan paralel (gruparea prin top este compatibilă în paralel, dar un top global nu este; paralelismul ar trebui să se oprească și să repornească în jurul acestuia).

Cu toate acestea, până când apare o opțiune mai bună sau până când se oferă mai multe detalii, este ceva ce ați putea încerca.

Vladimir Baranov

Dacă am înțeles corect interogarea dvs., următoarea interogare ar trebui să producă același rezultat, dar ar putea fi mai eficientă, dacă creați un index pe (companyId, id). Coloanele ar trebui să fie incluse în index în această ordine.

select count(*)
from TableName tbl1
where  
    tbl1.id = 
    (
        select TOP(1) tbl2.id
        from TableName tbl2
        where tbl2.companyId = tbl1.companyId
        order by tbl2.id DESC
    )
    and tbl1.active = 2
;