hurtige søgninger i mssql

Når du skal søge med like så vil det tage lang tid. Du kan lave et indeks på tabellen for de felter du vil søge på, det skulle gerne optimere søgningen.

Hvis du:

- fortæller brugerne at de søger på *starten* af ordene

ændrer SQL fra:

X LIKE '%...%'

til:

X LIKE '...%'

og laver INDEX på de 4 felter meta, destination, title og keywords
så vil du nok se en pæn forbedring af søge hastighed

Hvis det ikke er en mulighed kan du lave en separat tabel med 2 felter: ord
og id. Og så slå ord op i den med et normalt = test.

Du skal lige være obs på, at brug af OR forhindrer effektiv udnyttelse af indeks, Samtidig er max indeks key length er 900 bytes - du nærmer dig vist den...

En anden ting - du benytter en TOP 400 - det afskærer kun de viste data, men samtlige matchende rækker skal stadig gås igennem. En where betingelse - fx på lastmodified - vil afgrænse resultatsættet og dermed give et hurtigere svar.

Sidst - afhængigt af din server (diske, cpu & ram) er der grænser for hvad sql server kan levere. Sørg for at memory options på sql server og evt andre services er sat så swap undgås - og sørg for at log/data filer ligger på separate fysiske diske eller, bedre, raids.

Arne_v's forslag med en separat tabel vil fungere, den nærmer sig egentlig ideen i et fulltext index - omend den imho vil være hurtigere.

Du skal så lave en "parser" der kan bryde en tekst op i separate ord som du kan indsætte i din leksikontabel og en mange-til-mange der fortæller hvilke ord der bruges i hvilke records i din url/keyword etc tabel. Indsættelsen af nye rækker vil blive sløvet ned, naturligvis, men udsøgning vil blive til et rimelig simpelt sql opslag.

en separat tabel på f.eks KeyWords vil give omkring 2mill

Jo - men derfor kan det godt være hurtigt !

(mange records betyder ikke noget med index)

hovsa .
En separat tabel på f.eks KeyWords vil give omkring 2 millioner poster.
Hvis jeg skal have en søgning på description, title og keywords skal der så søges i 3 tabeller med ca. 9 millioner poster ialt. Derefter skal der sammenlignes med resultater, således at der ikke bliver retuneret dubletter, eller hvad !?!?
Vil det stadig være hurtigere ??

Tippet med TOP 400 var jeg ikke klar over, så det kan helt sikkert bruges.

Ja.

Loukas> Indeks er/kan være afsindigt effektive. Den "mindst effektive" form kaldes et binært træ - og i et sådant vil kun 10 opslag afgøre om en bestemt post findes blandt en million poster. Et binært træ har 2 grene på hver niveau.

I MS SQL benyttes bl.a. B+ træer som på hvert niveau har mere end 2 grene, det er altså lavere (færre niveauer) og en smule hurtigere at søge i!

nu har jeg lavet mine tabeller med ord.
Hvordan kommer SELECT så til at se ud?

TabelKeywords
id
ord

TabelTitle
id
ord

du skal kun have en ord-tabel, andet behøves ikke.

a la
ordliste(
  id  (p)
  ord  (ui)
)
urltabel(
  id (p)
  url
  metadata
  keywords
  title
  etc
  etc
  )

ord2url (
  ordid (p)
  urlid (p)
)

select u.*
from urltabel u inner join ord2url o2u
on u.id = o2u.urlid
inner join ordliste o
on o2u.ordid = o.id
where o.ord in ('hans','peter','lego','osv')

Når en ny række indsættes i "urltabel" skal du altså opdatere ord2url med id værdien for url-en samt id'er for alle de ord der benyttes i recorden.

Søgningen som ovenfor vil så finde alle url-rækker  hvor ordlisten indeholder et eller flere af de ord der står i listen. Bemærk at det er enkeltord du søger på. En indtastet søgestreng skal altså brydes op for at søgningen fungerer.

ah, ja: (p) = Primær nøgle, (i) = indeks, (ui) = unikt indeks

hmm, hvis jeg opretter en ordlisteTabel med ord fra både title og key words kommer der så ikke dubletter ??
Og betyder det noget hvis der gør ?
jeg er ikke helt med på idéen med ord2url.

øøh, ord2url er en tabel !?!?

Nej, du indsætter kun et givent ord én gang - det er ligegyldigt i hvilket felt det indgår. Og ja, det betyder noget hvis der gør - men du kan ikke indsætte dubletter når du lægger det angivne unikke indeks på.

ord2url fortæller hvilke ord der er i hvilke url-rækker. Det er en mange-til-mange relation, og den implementeres altid som tabel.

ex
ordliste
1 hans
2 bager

urltabel
1 keyword=hans
2 description=hans bager kager, keyword=morten

ord2url
ordid=1 urlid=1
ordid=1 urlid=2
ordid=2 urlid=2

ok?

Lige en indskudt bemærkning/ tips... trer

Som du jo grangiveligt er klar over (og har skrevet) er IN det samme som en OR, og en OR respektere ikke indexet, men kan klares vha. en UNION

select * from Table1 where f1 IN('a', 'b', 'c')

Kan omskrives til en meget hurtigere sådan
select * from Table1 where f1 = 'a'
union
select * from Table1 where f1 = 'b'
union
select * from Table1 where f1 = 'c'

janus_007> En UNION indeholder en implicit distinct, hvorfor du får et resultatsæt der skal sorteres og lægges sammen. Det er en temmelig tung operation.

I stedet for UNION bør man generelt bruge en UNION ALL - den indeholder ikke en DISTINCT og er derfor hurtigere.

Med store datasæt er der aldrig fordel ved at bruge en UNION fremfor en OR - problemet er simpelthen at resultatsættet ikke kan håndteres i ram hvorfor det ryger i TEMPDB i stedet - og dermed taler vi diskhastighed :-(

I de fleste tilfælde vil OR'en give bookmark operationer - dvs gentagne søgninger gennem index - men det er stadig hurtigere end at gennemføre en UNION.

Jeg prøvede lige i en temmelig lille tabel at lave de tre forskellige queryes (en med OR, en med UNION og en med UNION ALL). Query Analyzer har mulighed for at sammenligne cost ved queries vha funktion "Show Execution plan".

Resultat i cost % => OR = 0%, UNION = 52% og UNION ALL = 48%

Ergo - bookmark operationen er billigst (kan ikke måles på skalaen), UNION ALL er billigere end UNION operationen.  Havde der ikke været indeks på kolonne jeg brugte i where betingelsen havde resultatet naturligvis været anderledes - men god og korrekt indeksering er altid alfa og omega mht performance.

hmm.... mht UNION fremfor OR. Muligvis er det en ting der stammer fra MySQL der ikke havde indeks i de første versioner...

Hvorfor man så har valgt UNION fremfor UNION ALL - det kan jeg ikke regne ud (med mindre det var endnu en af de ting MySQL ikke kunne håndtere).

I øvrigt loukas. Hvordan kan du have 2 millioner keywords? Der er kun omkring 100.000 til 300.000 danske ord (når de mest specielle er med) - og det engelske sprog har ikke meget mere...

trer-> Det var nu også en union all jeg mente, men anyway det ændrer ikke på det faktum at det trick kun virker på mssql6.5 - min fejl :O)

Jeg har nu også lavet en test og den siger at der ingen forskel er på dem. Jeg oprettede en tabel med 1mill rækker, oprettede index på mine sarg's og her sagde execution plan 50% til "or" og 50% til "union all". Jeg kiggede også på io's og det var også det samme:
Table 'T_jk'. Scan count 2, logical reads 2087, physical reads 0, read-ahead reads 0.


Jeg faldt engang over : http://www.databasejournal.com/features/mssql/article.php/1443541 *GG*, men det var vist for lang tid siden :O)

Prøv kun at sammenligne or og union? - det burde give det samme.

Eneste jeg lige har herhjemme er en lille MSDE på en singlecpu maskine - og jeg har kun få tabeller og rækker at lege på.

Så: En UNION ALL giver 66% mens en OR giver 34% i cost.

Forskellen er, at jeg får flere bookmark operationer i UNION udtrykket samt en CONCAT mens jeg kun har én bookmark i OR'en. Det er muligvis fordi jeg har et almindeligt non-clustered index på - jeg har ikke prøvet med clustered index.

Anyway: Den korrekte anbefaling er at bruge Show Exec. plan løbende under udviklingen af ens queries - og huske at ens udviklingssetup skal være rimelig tæt på ens prod server hw-mæssigt (antal cpu'er påvirker query planer ret kraftigt).

trer-> jeg arbejder til daglig som dba'er :O)

Janus_007> Ditto her - primært med standardisering og performance optimering ;-)

Hyggeligt at møde dig :O)

Og rart at vide, hvis man pludselig står og er i tvivl om noget!

altid velkommen :-)  Kender du PASS ?

Næh det gør jeg ikke!?!

Professionel Association of SQL Server users. Der er også en dansk afdeling. Jeg skal lige skaffe et link til hjemmesiden - så poster jeg det her. Det bliver senere, skal ud af døren nu.

Jeg venter spændt :O)

Okay, hold fast: det er www.sqlpass.dk - og den den internationale org hedder www.sqlpass.org.

I Danmark er medlemmerne bl.a. fra Teknologisk Institut, Lego, Danmarks Radio, KMD, Novo Nordisk IT, Miracle, Kbhs Lufthavn, Columbus, Siemens, DM-Data og mange flere...

Det er ikke Access-niveauet det foregår på - men det er rare mennesker :-)

Hej igen !!
Jeg skal lige igang med at prøve lidt igen ;-)

trer -> 2 millioner keywords fordi der er dubletter !!!
Det kan måske laves bedre ?
KeyWordet skal jo høre til hver enkelt side i docsDK tabellen !!

jeg har problemer med dubletter.
Jeg har lavet tabellen ved at parse hver enkelt ord fra TITLE og KEYWORDS i tabellen docsDK.
Så det skal lige laves om...

Keywords skal IKKE indeholde dubletter. Hver keyword skal være unikt!

Du benytter ord2url til at forbinde hvert keyword med hver url-række.  Det er derfor det kaldes en mange-til-mange relation.

Dvs du har keyword "POUL" med id= 1 - ordet POUL indgår i URL rækken med ID 1, 12, 15 og 32.  Ord2url indeholder så id-par 1,1 1,12, 1,15 og 1,32

Keyword ELEKTRONIK har id 17 og bruges i URL rækkerne 37, 67 og 888 - så ord2url indeholder 17,37 17,67 og 17,888

Derfor er det kun nødvendigt at hvert keyword indgår én gang.

OK, nu er jeg med --- selv om det tog lidt tid ;-)
Jeg siger tusind tak for hjælpen for nu.
Og vender tilbage hvis der kommer flere problemer..