Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2008-12-08 : 10:48:48
|
| Hi Guys,I was in over the weekend to convert an 80 gig file down to a 40gig file. Reason it was so big is because it had text columns. (So you can't always reclaim free space by shrinking the database.) I changed them to varchar max. (When I say "changed" I mean I made a new db with the new column type and bcp'd the data). We store large documents in these columns and they have a Full-text index on them, 800 000 records.But today the full-text searches are running A LOT slower. And I've had to introduce SELECT TOP for the first time.(BTW does anyone know how sql desides how many records to bring back to screen in the first batch in the Studio query window? I used that to decide what to set my SELECT TOP to, in this case 1 000 - because I noticed it brought the first batch back in a chunk of between 1 000 and 7 000. Does anyone know what makes it decide whether to do 1 000 or 7 000 or somewhere in between?)Could a switch from TEXT to varchar(max) have caused the massive performance drop? It use to be very responsive. I'm trying to establish whether I can discount that possibility and put it down to the sheer amount of records that have to be traversed these days.I think I may have hit the limit of the performance that SQL Full-text indexing can provide.Anyone researched any of these? * Autonomy Corporation * Dieselpoint * Endeca * Fast Search & Transfer * ht://Dig * Inktomi * Lucene * Ferret * mnoGoSearch * Sphinx * Swish-e * Vivísimo * XapianOr doesn't google have one they sell as a packaged product? |
|
|
|
|
|
Full-Text Indexing