[SQL] Zoeken op volledige of gedeeltelijke match

[YopY]

Ik denk, ik open ook eens een topic.

Het is als volgt. Ik heb een query die auteurs ophaalt op basis van een keyword. Hij moet onder andere auteurs teruggeven die een titel geschreven hebben die overeenkomt met het keyword, en wel op drie manieren:

* Volledige titel (titel == keyword)
* Gedeeltelijke titel (volledig woord zit in titel)
* Gedeeltelijk woord (gedeelte van woord zit in titel)

Nu had ik dat in eerste instantie met een aantal subqueries opgelost, ongeveer zo:

SQL:

SELECT
    afn.id ,afn.authorName
FROM
    copyrighthouder cph
INNER JOIN vAuthorFullNames afn
    ON afn.Cph_Id = cph.id

WHERE
    cph.id IN (
        SELECT pch.cph_id FROM product prd
        INNER JOIN productcopyrighthouder pch
            ON pch.prd_id = prd.id
        WHERE prd.titel = @search
    )

    OR cph.id IN (
        SELECT pch.cph_id FROM product prd
        INNER JOIN productcopyrighthouder pch
            ON pch.prd_id = prd.id
        CONTAINS(prd.titel, @ftsearch)
    )

ORDER BY
    CASE WHEN cph.id IN (
        SELECT pch.cph_id FROM product prd
        INNER JOIN productcopyrighthouder pch
            ON pch.prd_id = prd.id
        WHERE prd.titel = @search
    ) THEN 1

    WHEN cph.id IN (
        SELECT pch.cph_id FROM product prd
        INNER JOIN productcopyrighthouder pch
            ON pch.prd_id = prd.id
        CONTAINS(prd.titel, @ftsearch)
    ) THEN 2
    END

De reden dat dit in twee subqueries kwam is omdat je dan in de ORDER BY ook dezelfde subqueries mee kunt nemen, om zo te kunnen sorteren op het matchtype - volledige titels komen eerst, gedeeltelijke, volledig woord titels komen tweede, en gedeeltelijk woord in de titels komen als derde (zie de order by-clausule).

Probleem met bovenstaand: de performance is belabberd, 30 seconden op tabellen met ~3000 auteurs en ~6500 boeken, en dat was nog maar met twee voorwaarden (volledige match titel en volledig woord in titel).

Als je de twee voorwaarden in één subquery stopt zijn de performanceproblemen weer opgelost: binnen 50 ms klaar. Waarschijnlijk gaat hij door de OR alle resultaten van de ene met die van de ander vergelijken, waardoor je vele duizenden vergelijkingen krijgt ineens. Subquery:

SQL:

 -- bla
WHERE
    cph.id IN (
        SELECT pch.cph_id FROM product prd
        INNER JOIN productcopyrighthouder pch
            ON pch.prd_id = prd.id
        WHERE prd.titel = @search
        OR CONTAINS(prd.titel, @ftsearch)
    )
-- bla

Effectieve vraag: Is er een methode waarmee je kunt selecteren op zowel volledige, gedeeltelijke (volledig woord) en gedeeltelijke match, waarbij je de manier waarop gematched is terug kunt vinden die ook nog eens performant is?

Ja ik weet, je kunt een externe indexer erbij halen, maar dat doen we liever niet op dit punt - het is een tijdelijke oplossing, in de nabije toekomst gooien we de database weg en vervangen we het met een content repository (oid).

Edit: Hm, het lijkt erop dat het niet zozeer in de verschillende inner queries zit, maar in de OR van de WHERE-clause. Als ik het volgende doe:

SQL:

WHERE 
    afn.authorName = @search
    OR cph.id IN (

        SELECT pch.cph_id FROM product prd
        INNER JOIN productcopyrighthouder pch
            ON pch.prd_id = prd.id
        WHERE
            prd.titel = @search
            OR CONTAINS(prd.titel, @ftsearch)
            OR prd.titel LIKE @wildcard
    )

dan is de uitvoeringstijd weer 30 seconden. Haal de afn.authorName = @search OR weg, en het is weer naar 50 ms.

Bij bovenstaande query heeft de STATISTICS IO het volgende te zeggen:

code:

Table 'Worktable'. Scan count 34, logical reads 39, physical reads 0, read-ahead reads 0.
Table 'CopyrightHouder'. Scan count 6, logical reads 77, physical reads 0, read-ahead reads 0.
Table 'Product'. Scan count 9048, logical reads 28131, physical reads 0, read-ahead reads 0.
Table 'ProductCopyrightHouder'. Scan count 2934, logical reads 316786, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 8984, logical reads 17976, physical reads 0, read-ahead reads 0.

de 'PropductCopyrightHouder'-koppeltabel (product <-> auteur) lijkt het zwaar te verduren te hebben. Komt dat door die OR?

Nog vollediger: De vAuthorFullNames is een view die slechts drie kolommen uit de CopyrightHouder tabel haalt en deze samenvoegt als volledige naam. Hier worden geen extra tabellen bijgehaald.

[ Voor 20% gewijzigd door YopY op 01-12-2010 16:22 ]

[glrfndl]

Ligt het niet aan de ORDER BY? Wellicht dat voor elke rij nog een subquery wordt uitgevoerd om de volgorde te bepalen? Is je query ook traag zonder ORDER BY?

Misschien kun je het met een CTE proberen? Of anders iets met een UNION?

Hoe werkt dit bijv.:

SQL:

with fullmatches as (
SELECT pch.cph_id , 1 as volgordenr
    FROM product prd 
        INNER JOIN productcopyrighthouder pch 
            ON pch.prd_id = prd.id 
        WHERE prd.titel = @search
),
partialmatches as (
SELECT pch.cph_id, 2 as volgordenr 
    FROM product prd 
        INNER JOIN productcopyrighthouder pch 
            ON pch.prd_id = prd.id 
        CONTAINS(prd.titel, @ftsearch) 
)       
SELECT 
    afn.id ,afn.authorName 
FROM 
    copyrighthouder cph 
INNER JOIN vAuthorFullNames afn 
    ON afn.Cph_Id = cph.id 
LEFT JOIN fullmatches fm on fm.cph_id = cph.cph_id
INNER JOIN partialmatches pm on pm.cph_id = cph.cph_id
order by coalesce(fm.volgordenr, pm.volgordenr)

(quick copy/paste, wellicht zitten er nog wat fouten in, en ik ga er van uit dat een volledige match ook altijd een partial match oplevert, maar dat moet haast wel?)

[Dido]

Sowieso zou je in plaats van die uitgebreide ORDER BY natuurlijk gewoon een UNION (ALL) kunnen doen (en de resultaten dus in drie losse selects ophalen).
Als een losse select 50ms duurt, zal de UNION met drie stuks rond de 150ms uitkomen, dat lijkt me al beter dan 30s