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

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.

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
;