December 22, 2011 at 6:31 am
Hi
I do currently work on a website with a search that has to use wildcard a lot.
If the user searches for "Some Item 5kg" the search will have to do a "like %some% AND like %item% ..." etc. ( Shop Wildcard search like amazon etc does )
First I simply joined all the required tables and used this statement on every field. Which was very very slow.
Then I tried to fill all the textfields into a new table with a single Textfield, that contains all the other fields, so I wouldnt need to search through 15 small fields, but 1 big.
This actually worked out and brought down the time to be just very slow.
Now 1 Query needs like 1600ms, which is way too slow.
There are like 15 fields over 9 Tables that contain text I have to search through.
Filled into 1 field, the average textlength is like 2000 characters.
Indexes are set, as well as FT, but I think this wont help much, as wildcard in the front of the search will force the DB to cycle through just everything, everytime 🙁
Does anyone have an Idea how I can speed things up?
Thanks for the Help
Ben
December 22, 2011 at 8:01 am
Sounds like full text indexing would do exactly what you need. I've used it on multi-million row tables and get results in sub-millisecond on a regular basis, for very similar queries to what you're doing. Just use Contains or FreeText instead of Like, of course.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 8:10 am
We too do a lot like this with full text indexing as well. Amazing speed.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
December 22, 2011 at 8:34 am
Hi
Like 30 minutes ago I found a hint somewehere that CONTAINS does work with FT, but LIKE does not. knew CONTAINS before, but not this fact :hehe:
always wondered why the FT never made any improvement to me ... now I know why :w00t:
Many thanks! This made my search go down to like 3ms 🙂
December 22, 2011 at 8:40 am
The FullText predicates are CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE. Anything else (like LIKE) will use the normal indexes only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply