SQL Server Performance Forum – Threads Archive

Full-Text Query Performance

Hi! I have a SQL 2005 Database with about 3 million records of generic objects. This objects may be of several types, representing types of entities.
I also have a full-text catalog covering four varchar(500) fields (the descriptions of the objects).
I have built a query based on the CONTAINSTABLE keyword, which is: SELECT o.id_obj, O.name1, o.name2, o.name3, o.name4, o.id_typeobj, FT.RANK
FROM CONTAINSTABLE(Objects, *, @Criteria, 300) FT
INNER JOIN Objects o on o.id_obj = FT.[KEY] It runs very quickly, retrieving about 10000 rows in less than 1 second. However this speed is only achieved the second time I run the query, because the first time I do it, the query takes about 50 seconds or so. After the first query is executed, all following queries made to the catalog (this one and other ones as well) perform lighting fast. Is there any configuration issue I’m missing? I’ve heard of a search service that can go idle or pause if the catalog is not queried for a while. How can I configure this service?? Appreciate any help! Thank you very much! JoÃ£o Gomes

The reason the second time runs faster is because the pages are cached in memory. The first time is slower because sql is going to disk to get the results. FT idle and pause states relate to building full text indexes rather than querying them. When running the query use perfmon to watch disk activity (disk queue) and cpu usgae (full text has a habit of consuming it all) to try and work out where the bottle neck is. If it’s disks consider moving the catalogue to another volume with less load/faster RAID etc etc. To test the query without restarting SQL run DBCC DROPCLEANBUFFERS() to clear the cache – though if this is a production box be warned this may have a performance effect on the users. SQL 2005 also has an option to Optimise the Index when you go into the catalogue properties – you may want to try this to see if it helps performance – particulary if you do incremental updates. Also remember that the slow part of the query may be the join back to your objects table – run DBCC SHOWCONTIG(‘objects’) and make sure it has a high scan density (>90%). Hope this helps
Simon

This sounds like it might be something like SQL intialising the .net framework in order to perform the search…? also try running perfmon with the cpu utilisation for all processes, and see which one is active during this 50 sec period? Cheers
Twan

you can run the below mentioned dbcc commands, before running the queries. the below given statements flushes the data in cache. dbcc freeproccache
dbcc dropcleanbuffers Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

]]>