[SQL2005] Geavanceerde zoekmachine in SQL-Server

[Verwijderd]

We zijn op het moment bezig met de ontwikkeling van een site waar zoeken centraal staat. Het belangrijkste onderdeel is dus de zoekmachine. Nu kan een zoek aktie voorzien worden van wel 30 optie's in de vorm van een tinyint of een bit.

De verwachting is dat er een paar honder gebruikers tegelijk online zullen zijn en gebruik maken van deze zoek functie. Zijn er mensen die ook wel eens tegen deze uitdaging aan zijn gelopen en hoe hebben jullie dit opgelost.

Zelf hebben we al gedacht aan bepaalde zoek categorien te creeren en hier tabellen van maken en een job laten lopen die dit actueel houd. Gezien een query met zoveel parameters in de where niet echt te doen is qua performance.

We maken gebruik van sql server 2005 en de website word ontwikkeld in asp.net (vb.net).

[Verwijderd]

Om wat voor data en wat voor hoeveelheden gaat het?

[Verwijderd]

Het gaat om een site waar gebruikers een profiel op kunnen plaatsen met diverse gegevens over het uiterlijk, persoonlijke informatie, persoonlijke interesses.

Het systeem moet berekend zijn op een miljoen gebruikers. Het zal gaan om een miljoen records welke verspreid zitten over 6 tabellen. Alle informatie die we gebruiken om te zoeken zijn in de database opgeslagen met een tinyint of een bit.

Wat ook een belangrijk gegeven is dat gebruikers in hun zoek opgave gebruik kunnen maken van 5 opties maar ook van alle 30. Dit levert dus vele honderden combinaties aan zoek mogelijkheden op.

[ Voor 19% gewijzigd door Verwijderd op 18-08-2009 15:18 ]

[The Eagle]

Aangenomen dat je zoekfunctionaliteit dynamisch SQL clausules aan elkaar knoopt, zou ik gewoon een x-aantal indexen aanmaken op de zoekopties. Dan kun je bijna niet mis. Wel zorgen dat het geen unique indexen zijn btw. Als SQL server een beetje fatsoenlijk werkt zal ie zelf zien welke indexen ie het beste kan gebruiken en heb je geen performance-issue. Evt de tabel(len) cachen in het geheugen ipv vanaf schijf zal ook een hoop schelen.

En waarom zit de data over 6 tabellen verspreid? Als het allemaal profielen zijn, kun je met 1 flinke tabel toch ook gewoon toe? Die wordt dan weliswaar wat groot en veel gebruikt, maar ik zie daar geen reden in om zoiets te splitsen.

_{Sidenote: ik werk met oracle en daar kunnen dat soort dingen, maar ik mag aannemen dat SQL server als zichzelf serieus nemen RDBMS dit ook allemaal zou moeten kunnen. of is het nog steeds Sukkel server?}

[ Voor 37% gewijzigd door The Eagle op 18-08-2009 15:35 ]

[Xiphalon]

Verwijderd schreef op dinsdag 18 augustus 2009 @ 14:50:
Gezien een query met zoveel parameters in de where niet echt te doen is qua performance.

Want? Heb je dat gemeten? Ik heb hier regelmatig 500-600 parameters in een where clause staan, en die query performt prima hoor.

Verder 1mil regels is peanuts, dat blijft wel in de cache van SQL server staan, en dan zijn zelfs de FTS's die je gaat krijgen niet echt een probleem.

[TeeDee]

The Eagle schreef op dinsdag 18 augustus 2009 @ 15:32:
En waarom zit de data over 6 tabellen verspreid? Als het allemaal profielen zijn, kun je met 1 flinke tabel toch ook gewoon toe? Die wordt dan weliswaar wat groot en veel gebruikt, maar ik zie daar geen reden in om zoiets te splitsen.

offtopic:
Ik gok dat het om een genormaliseerde DB gaat en daarom 6 tabellen. Tenzij het sharding betreft, maar dan is sharding op een 1 miljoen records imo geen goede keus geweest.

SQL 2005 pakt jouw 30 parameters met 2 vingers in de spreekwoordelijke neus. Om je nu hier al druk om te gaan maken zonder fatsoenlijke performance tests...

[Niemand_Anders]

Je kunt eventueel ook kijken naar een bestaande open-source search engine zoals Lucene.net. Lucene heeft daarnaast een zeer indrukwekkende query syntax.


Daarnaast zijn bit velden in een database waarop je moet kunnen zoeken niet handig. SQLServer kent namelijk geen native bit veld. Het slaat 8 bit velden op in een byte en houd in zijn systeemtabellen bij elk bit naar welk veld verwijst. Vanaf SQL2005 kan MSSQL indexen aanleggen op bit velden, maar de query wordt via een bitwise comparisment uitgevoerd.

Je kunt beter TinyInt data types gebruiken dan een bit veld. Je hebt 30 bit velden. Dat kost je 1.000.000 * 7 * 30 = 210.000.000 bits extra. Ofwel zo'n 26,2 megabyte. Echter zal je performance met een factor 10 toenoemen.

[PolarBear]

The Eagle schreef op dinsdag 18 augustus 2009 @ 15:32:
_{Sidenote: ik werk met oracle en daar kunnen dat soort dingen, maar ik mag aannemen dat SQL server als zichzelf serieus nemen RDBMS dit ook allemaal zou moeten kunnen. of is het nog steeds Sukkel server?}

Leuk zo'n troll, maar Microsoft SQL server (zeker versie > 2000) en Oracle kunnen zich prima met elkaar meten. Ik ga toch ook Oracle geen PayServer noemen omdat je voor de duvel en z'n ouwe moer licenties nodig hebt en bij SQL server niet?

Belangrijkste hierin is testen en execution plans doornemen (als performance echt een probleem mocht zijn).

[Verwijderd]

Ik heb zojuist de tabellen volgespoeld met willekeurig data zodat ik een beetje realistisch kan testen. Vervolgens heb ik een aantal query's geschreven die het zoeken gaan doen.

Na uitvoerig testen blijkt het snelste te zijn om met dynamic sql te werken, zo kan je je select statement heel beperkt houden maar ook heel uitgebruid als alle parameters gebruikt worden. Je joins blijven hierdoor ook vaak beperkt.

Wat voor ons ook een voordeel is aan het dynamic sql is het toepassen van de diverse "order by" functies die wij op de site gebruiken. Voor alsnog zie ik inderdaad nog geen performance problemen. Alle zoekopdracht blijven onder de 1200 milliseconden over 1.000.000 records en we hadden als max 2000 milliseconden gesteld.

Ik sla sowieso alle zoek functies ook op, zo kan ik precies analyseren wat lang duurt en eventueel de indexen hierop aanpassen. Even als zoek opdracht die veel uitgevoerd worden eventueel sneller maken door daar een gecombineerde index van te maken.

De tijd zal het uiteraard nog moeten leren, maar ik denk dat dit goed gaat komen. Bedankt voor al jullie reacties

[ Voor 12% gewijzigd door Verwijderd op 21-08-2009 23:40 ]