10. november 2004 - 11:03Der er
11 kommentarer og 1 løsning
FreeText Query med unions - performance
Jeg har en SQL query som driller. Jeg har behov for at gennemsøge en tabel og dens relaterede tabeller for en given tekststreng. Jeg anvender full-text indexing og CONTAINS kommandoen.
I det følgende er main min hoved tabel og rel1-4 mine relaterede tabeller. Essensen af min query er som følger:
SELECT DISTINCT id, code, name FROM main WHERE id IN ( SELECT main.id FROM main LEFT JOIN rel1 ON rel1.mainid=main.id WHERE CONTAINS (rel1.*,'hansen') UNION SELECT main.id FROM main LEFT JOIN rel2 ON rel2.mainid=main.id WHERE CONTAINS (rel2.*,'hansen') UNION SELECT main.id FROM main LEFT JOIN rel3 ON rel3.mainid=main.id WHERE CONTAINS (rel3.*,'hansen') UNION SELECT main.id FROM main LEFT JOIN rel4 ON rel4.mainid=main.id WHERE CONTAINS (rel4.*,'hansen') ) ORDER BY name
Querien virker godt nok men sjovt nok bliver den meget langsom (14+ sekunder) når jeg tilføler sub selecten i rel4 tabellen. rel4 tabellen har samme karakteristika som rel3 og det gør ingen forskel om jeg bytter om på sub selects eller fjerne dem så fx. kun rel4 sub selecten er tilbage - den er stadig langsom. Men fjerne jeg Rel4 selecten går det straks meget hurtigere (2-3 sek.) Selve selecten i rel4 tager kun 2 sekunder. Rel4 tabellen ER full-text indexeret og indeholder kun 4 felter hvoraf 2 er indekserede, så det burde ikke give problemer. Der er også oprettet fornuftige indekser på rel4.
Er der nogen der har en ide om, hvad der kan være galt - formodentlig med rel4 tabellens opbygning el. lign.?
Er der nogen grund til, at du ikke bruger CONTANSTABLE ?
Mht. performance.. Kunne man forestille sig, at rel4 returnerer rigtigt mange rækker? I så fald, vil jeg tro, det er din UNION kommando, der er bottleneck. Du skal være opmærksom på, at når du bare skriver UNION ( frem for UNION ALL ) så er der ikke ngoen grund til at angive DISTINCT i din øverste select - det betyder blot, at dubletter fjernes to gange.
Kan hver subselect returnere dubletter? Hvis ikke, så skift din UNION ud med en UNION ALL. UNION har en implicit sortering for at sikre distincte værdier ud.
I øvrigt er en EXISTS operator som regel hurtigere end en IN operator. Det vil give en select a la denne
SELECT DISTINCT id, code, name FROM main m1 WHERE exists ( SELECT 1 FROM main LEFT JOIN rel1 ON rel1.mainid=main.id WHERE CONTAINS (rel1.*,'hansen') and main.id = m1.id UNION ALL SELECT 1 FROM main LEFT JOIN rel2 ON rel2.mainid=main.id WHERE CONTAINS (rel2.*,'hansen') and main.id = m1.id UNION ALL SELECT 1 FROM main LEFT JOIN rel3 ON rel3.mainid=main.id WHERE CONTAINS (rel3.*,'hansen') and main.id = m1.id UNION ALL SELECT 1 FROM main LEFT JOIN rel4 ON rel4.mainid=main.id WHERE CONTAINS (rel4.*,'hansen') and main.id = m1.id ) ORDER BY name
der ud over - du får aldrig top performance når du anvender full text søgning på * i dine relX tabeller...
Hej Veronica! Er nogen grund til at jeg SKAL bruge CONTAINSTABLE? Der er ingen nævneværdig forskel på tidsforbruget hvad enten der er ingen eller mange rækker i rel4 queryen. Vi taler om størrelsesordenen 88 rækker.
Det var ikke for at pådutte dig CONTAINSTABLE - det er bare fordi at jeg bruger CONTAINSTABLE, og så ville jeg høre om det var bedre at bruge CONTAINS.. ?
Jeg ville nok have skrevet din query således:
SELECT DISTINCT main.id, main.code, main.name WHERE main.id IN ( SELECT [key] FROM CONTAINSTABLE (rel1, *, 'hansen') UNION ALL SELECT [key] FROM CONTAINSTABLE (rel2, *, 'hansen') UNION ALL SELECT [key] FROM CONTAINSTABLE (rel3, *, 'hansen') UNION ALL SELECT [key] FROM CONTAINSTABLE (rel4, *, 'hansen') )
Hej Veronica! Jeg har prøvet containstable og det ser faktisk ud som om den isoleret set er mere effektiv. Problemet er bare - så vidt jeg kan se - at den returnerer primærnøglen og man kan ikke angive flere kriterier i selve select statementet. Så hvis jeg f.eks. var interesseret i alle hansen'er i postnr 5000, skal jeg lave det som en join med containstable, som returnerer alle hansener i alle postnumre for at finde primærnøglen i hovedtabellen via fremmednøglen i relationstabellen, som ikke retureres af containstable. Giver det mening?
Hej igen, Ja du kan selvfølgelig også lave det som et join..
SELECT DISTINCT main.id, main.code, main.name FROM Main, ( SELECT [key] FROM CONTAINSTABLE (rel1, *, 'hansen') UNION ALL SELECT [key] FROM CONTAINSTABLE (rel2, *, 'hansen') UNION ALL SELECT [key] FROM CONTAINSTABLE (rel3, *, 'hansen') UNION ALL SELECT [key] FROM CONTAINSTABLE (rel4, *, 'hansen') ) as relation WHERE relation.[key] = main.id
Det er muligvis mere effektivt end IN-operatoren. /Veronica
Hej Veronica! Jeg ser problemet med CONTAINSTABLE, fordi ... SELECT [key] FROM CONTAINSTABLE (rel2, *, 'hansen') ... ikke er nok. Jeg skal fritekstsøge i 2. relationsniveau og derfor begrænse freetext søgningen til KUN at medtage dem som findes i subrelationen. Eller alternativ kun medtage dem som overholder et kriterie på et andet felt i tabellen som f.eks. et postnr.
Så må du jo bare joine CONTAINSTABLE med rel2 tabellen selv inde i din subquery .. ? Ellers tror jeg ikke helt jeg forstår problemet.
Kunne man i øvrigt forestille sig, at det var dette ekstra kriterie, som var årsagen til, at din query blev sløv ? Det var jo også et forsøg at prøve at sætte et index på dette ekstra kriterie?
Jeg har bare en formodning om at et CONTAINSTABLE() resultat med f.eks. 300.000 rows er tungere at danse med end et CONTAINS() som allerede er reduceret til fx. 3.000 FØR contains() udføres.
Jeg har allerede forsøgt at effektivisere ved at sætte indekser på, men det gør heller ingen målbar forskel.
Der kom ingen løsning på problemet, så jeg tager selv points tilbage
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
