[sql] Indexing/Full-text Search

[Tinuske]

Voor een zoekscherm in de applicatie waar ik mee bezig ben, kan men op verschillende criteria. NAdeel is alleen dat de where-clausule tot 8 seconden in beslag neemt, omdat er veel records in de database zitten. Ik heb op internet gezocht naar full-text searching en indexering om dit te versnellen.
Het is me nog steeds niet helemaal duidelijk wat nu het verschil is tussen beide.
Wie kan mij uitleggen wat het verschil precies is, en welke oplossing de beste keus is.

code:

WHERE
ISNULL(CA.CompanyCategoryId,0) = COALESCE(@intCategoryId,CA.CompanyCategoryId,0) AND
            C.[Naam] LIKE '%' + @strNaam + '%' AND
            (C.[Postcode] LIKE '%' + @strPostcode + '%' OR C.[BezoekPostcode] LIKE '%' + @strPostcode + '%' OR C.[FactuurPostcode] LIKE '%' + @strPostcode + '%' OR C.[OverigPostcode] LIKE '%' + @strPostcode + '%') AND
            (C.[Plaats] LIKE '%' + @strPlaats + '%' OR C.[BezoekPlaats] LIKE '%' + @strPlaats + '%' OR C.[FactuurPlaats] LIKE '%' + @strPlaats + '%' OR C.[OverigPlaats] LIKE '%' + @strPlaats + '%') AND
            (ISNULL(C.CountryId,0) = COALESCE(@intCountryId,C.CountryId,0) OR ISNULL(C.BezoekCountryId,0) = COALESCE(@intCountryId,C.BezoekCountryId,0) OR ISNULL(C.FactuurCountryId,0) = COALESCE(@intCountryId,C.FactuurCountryId,0) OR ISNULL(C.OverigCountryId,0) = COALESCE(@intCountryId,C.OverigCountryId,0)) AND
            (C.Tel1 LIKE '%' + @strTel1 + '%' OR C.Tel2 LIKE '%' + @strTel1 + '%') AND
            C.Opmerking LIKE '%' + @memOpmerking + '%' AND
            C.Inactief = @bitInactief AND*/
            C.CustomerId = @CustomerId

[ Voor 12% gewijzigd door Tinuske op 03-01-2005 11:56 ]

[Infinitive]

Ten eerste, normale indexen zullen je niet helpen wanneer je op substrings gaat zoeken in een tabel. Als je slechts één procenttekentje aan de voor of achterkant van een woord gebruikt kan dat wel (bijv. met suffix of prefix tree), maar zodra je er twee hebt dan werkt dat niet meer met een index.

Eén ding wat je kan doen is proberen van wat van die wildcards af te komen. Van een postcode of telefoonnummer heb je meestal wel het begin cq eind bijvoorbeeld.

Alternatief zou iets als full-text indexing zijn. Daar heb ik op zich niet zoveel ervaring mee, maar als je database het ondersteund zou ik het eens uitproberen. Daar lijken me wel allerlei complicaties bij te zitten ivm zaken als herindexering, maar wellicht dat dat soort details automatisch voor je afgehandeld worden.

[foske]

kweet niet of of je sqlteam kent (Vooral voor Microsoft SQL server bedoelt) maar daar staan wat artikellen over full text indexes
http://www.sqlteam.com/se...erms=full&SUBMITs1=Search

[whoami]

Tja, je gebruikt in je WHERE clause COALESCE, ISNULL en LIKE %bla%. Dan verbaast het me al niets dat je query traag is; door die constructies kan het DBMS gewoon geen indexen gebruiken. Check je execution plan maar eens in Query Analyzer.

Infinitive schreef op maandag 03 januari 2005 @ 12:09:
Ten eerste, normale indexen zullen je niet helpen wanneer je op substrings gaat zoeken in een tabel. Als je slechts één procenttekentje aan de voor of achterkant van een woord gebruikt kan dat wel (bijv. met suffix of prefix tree), maar zodra je er twee hebt dan werkt dat niet meer met een index.

Fout.
Het heeft niets te maken met die 2 wildcards, het heeft enkel te maken met de wildcard als prefix. Als je een prefix - wldcard gebruikt icm met een LIKE, kan het DBMS de index niet gebruiken. Hij heeft nl. geen aanknopingspunt.
Als je een % als suffix gebruikt, kan hij wel een index gebruiken, maar als je dus dit doet:

code:

veld LIKE '%miep'

Kan er al geen index meer gebruikt worden om te zoeken op 'veld'.

Ik denk dat je, in jouw geval idd best eens kijkt naar Full Text Search. Sla de Books Online van Sql Server er eens op na, en zoek oa naar de functie FREETEXT.

[ Voor 9% gewijzigd door whoami op 03-01-2005 12:17 ]

[Tinuske]

Heb inderdaad al gezocht op contains en op freetext, het nadeel van deze 2 functie's is dat je verplicht bent om zoekcriteria mee te geven.
Als je wilt zoeken op alleen de naam en niet op postcode of of plaats bijv, dan krijg jebij postcode :

freetext(postcode, '*')
of contains(postcode,'*') en dat wordt beide niet gepikt door ms-sql, hij heeft minimaal een letter of cijfer nodig icm met een wildcard.

HOe valt dit op te lossen ?

[.oisyn]

whoami schreef op maandag 03 januari 2005 @ 12:16:
Fout.
Het heeft niets te maken met die 2 wildcards, het heeft enkel te maken met de wildcard als prefix. Als je een prefix - wldcard gebruikt icm met een LIKE, kan het DBMS de index niet gebruiken. Hij heeft nl. geen aanknopingspunt.

En waarom zou een database geen suffix index bij kunnen houden?

[whoami]

.oisyn schreef op maandag 03 januari 2005 @ 13:29:
[...]


En waarom zou een database geen suffix index bij kunnen houden?

Hoe zou hij dat dan doen denk je ?
Hoe bepaalt hij dan hoeveel tekens er nog voor die suffix kunnen komen ?
Ik zie niet direct in hoe -als de data in een tree opgeslagen is- een DB via een index de records kan ophalen waarvan in een bepaald veld bv de string 'oisyn' voorkomt, met nog een onbekend aantal characters daarvoor.

[whoami]

Tinuske schreef op maandag 03 januari 2005 @ 13:20:
Heb inderdaad al gezocht op contains en op freetext, het nadeel van deze 2 functie's is dat je verplicht bent om zoekcriteria mee te geven.
Als je wilt zoeken op alleen de naam en niet op postcode of of plaats bijv, dan krijg jebij postcode :

freetext(postcode, '*')
of contains(postcode,'*') en dat wordt beide niet gepikt door ms-sql, hij heeft minimaal een letter of cijfer nodig icm met een wildcard.

HOe valt dit op te lossen ?

Als je niet op dat veld wilt zoeken, dan neem je het toch niet op in je WHERE clausule ?
Je kan je query toch dynamisch gaan opbouwen ?

[Janoz]

whoami schreef op maandag 03 januari 2005 @ 13:34:
[...]


Hoe zou hij dat dan doen denk je ?
Hoe bepaalt hij dan hoeveel tekens er nog voor die suffix kunnen komen ?
Ik zie niet direct in hoe -als de data in een tree opgeslagen is- een DB via een index de records kan ophalen waarvan in een bepaald veld bv de string 'oisyn' voorkomt, met nog een onbekend aantal characters daarvoor.

Je zou natuurlijk een extra indextree van de reversed waarden bij kunnen houden , maar dat werd eerder in het topic ook al genoemd

[Tinuske]

@whoami

De query die we gebruiken voor o.a. het zoekscherm. Is een vrij ingewikkelde query geworden. Ook omdat in het zoekscherm, sortering en paging mogelijk moet zijn.

Eeen gedeelte, afhankelijk van paginanummer van de resultaten wordt nu in een temptabel in het geheugen gezet , waarna met rowcount de juiste rijden worden teruggeven.
Op zich is dat gedeelte redelijk snel alleen de where clausule zorg nu voor de vertraging.

Nadeel is wel, dat de sql al erg groot is.
We zouden string concatenation kunnen gaan toepassen, maar zelf vind ik dat slecht onderhoudbaar en zie dat eerder als laatste mogelijkheid.

Misschien een beetje off-topic inderdaad.
Maar hoe hebben andere programmeurs dit opgelost ?
Dus een uitgebreide where ( met zoeken op woorden dus) icm paging en sortering .
Ook temptabel icm rowcount, of toch string concenation of iets heel anders?

[ Voor 5% gewijzigd door Tinuske op 03-01-2005 14:26 ]

[.oisyn]

whoami schreef op maandag 03 januari 2005 @ 13:34:
[...]


Hoe zou hij dat dan doen denk je ?
Hoe bepaalt hij dan hoeveel tekens er nog voor die suffix kunnen komen ?

Zoals Janoz al zegt, gewoon een text index zoals normaal, maar dan van de omgedraaide tekst. Als je een index voor de voorkant van een tekst kunt maken kun je die toch ook voor de achterkant van een tekst maken? Ik heb een db dit nog nooit zien doen, maar mijn kennis gaat dan ook niet verder dan mysql . Ik legde dan ook niet voor niets de nadruk op kunnen, maar uit de reactie van Infinitive maak ik op dat er dus wel databases zijn die het doen.

Ik zie niet direct in hoe -als de data in een tree opgeslagen is- een DB via een index de records kan ophalen waarvan in een bepaald veld bv de string 'oisyn' voorkomt, met nog een onbekend aantal characters daarvoor.

Dan zoekt ie naar de index records die eindigen op "oisyn" (wat in O(log n + k) te vinden is door een zoekboom te gebruiken die op de achterkant van de strings sorteert), en het hele bereik waar die index naar wijst voldoet dan aan de query.

[ Voor 27% gewijzigd door .oisyn op 03-01-2005 16:36 ]

[whoami]

.oisyn schreef op maandag 03 januari 2005 @ 16:31:
[...]


Zoals Janoz al zegt, gewoon een text index zoals normaal, maar dan van de omgedraaide tekst. Als je een index voor de voorkant van een tekst kunt maken kun je die toch ook voor de achterkant van een tekst maken? Ik heb een db dit nog nooit zien doen, maar mijn kennis gaat dan ook niet verder dan mysql . Ik legde dan ook niet voor niets de nadruk op kunnen, maar uit de reactie van Infinitive maak ik op dat er dus wel databases zijn die het doen.

Ik heb het een DB nog nooit zien doen. In SQL Server (waar het hier over gaat), is het alleszins onmogelijk.


Theoretisch lijkt het wel mogelijk, maar wellicht is de overhead van het bijhouden en bijwerken van zo'n index gewoon te groot.

[Verwijderd]

maar uit de reactie van Infinitive maak ik op dat er dus wel databases zijn die het doen.

En whoami's antwoord was daarop (bij mijn weten terecht): "Fout."

Bij mijn weten is er geen enkele database die een "WHERE veld LIKE '%waarde'" kan koppelen aan een index. Bij MSSQL, InterBase, Oracle, DB/2, Informix en MySQL ben ik die mogelijkheid tenminste nog niet tegengekomen...

In een (non-SQL, non-client-server) file based systeem als bv. Clipper is 't wel mogelijk (daar kun je indexeren op alles wat de client kent, dus ook eigen functies), maar de performance wordt daar eufemistisch gezegd niet echt beter van.