Fulltext large (200.000) count query performace too slow

I am using count(1) not * sorry for the typo...

<<<
We never need to read the thousands of records BUT we ALWAYS need to count them.
<<<

However using the count property is also reading trough the record the difference is you just only display the count. Therefore if you are counting a large amount of data the speed will also get affected.

BUt you can try to use  the GROUP BY might speed up the query...

select count(field_ id) from content_books
GROUP BY fieldname
where contains(searchData,'"english"')

or

select count(field_ id), commonfieldname from content_books
GROUP BY commonfieldname
where contains(searchData,'"english"')

Please note that in each commonfieldname, there will be a seperate count. Assuming that there are few common field this might work.

HTH.

One of most common problems on VLDB when users try to exact count huge values, for example your "english" word occured 284,327 times among 100mln entries.

This is almost always nonsense, very resource-consuming operation.
You can speed-up all searches to one second, when only returns "More than 10000" occurences, provided by SELECT TOP (N) clause.

try:

select count(*) from (select top 10000 1 from content_books where contains(searchData,'"english"')) as t

Hi,

Thank you for the replies.

Cecrid D: I cannot do a work-around and the "more than N" can only be shown if i can know how many there are in a fast way, and that is THE problem...

Cecrid D & gladxml: the suggestions you make will INCREASE cost of processing this query (when testing it might not seem so because of internal SQL Server cache).

To improve it i need to get the info directly from the fulltext catalog. Extracting info from it will always have more cost and won't scale up.

See you!

Not to start a contraversy that is totally beside the point, but you will not find any difference between doing COUNT(*), COUNT(1), COUNT(YourColumnName).  The SQL Server optimizer is intelligent enough to know what you want.  That is not to say that it is always better to do IF EXISTS (SELECT 1 FROM ...

But now for the real problem.  Have you done a Full Population recently?

If the answer is yes, than there is not much more you can do.  It is sequentially counting the returned results.  Incidentally, there is a limit as to the total number of rows returned when using Full-Text Search.  With SQL Server 2000 it was only 20,000, but this max has been increased with SQL Server 2005

>> Cecrid D: I cannot do a work-around and the "more than N" can only be shown if i can know how many >> there are in a fast way, and that is THE problem...

when you learn SQL storage fundamentals well you'll be surprising that counting of "more than X" can be many times faster than determining exact value of COUNT(*)   :)

because: to determine exact COUNT server should read much more data, but for TOP N it stops counting after N matches.

have you tried creating a Clustered Index?
http://www.devx.com/dbzone/Article/29530/1954

or possibly shringking the field sizes to make the database smaller, thus quicker easier to sort because of using less cache?

Hi,

I don't know your experience with fulltext, but i it seems to me that you are missing the point. Please read this http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx to undestand how far i have gone and what i am trying to do to have the top performance of a "simple" fulltext count.

Resuming, there is a boundary between the fulltext catalog and the sql server so to have the top performance that i need i am looking for some additional parameters to pass to the fulltext, not inside sql server 2005. These

The optimization i did so far (althoug it has a huge cost on disk space) that is giving better results is to create a seperate table with the same data (?) only for the search. I don't know why, but IO and CPU usage is 1/10 less than with the original table. We are still trying to figure out what is hapenning (yes we rebuilt all indexes from scratch).

I am still looking for the solution and it should be in the area of "telling" the fulltext engine that we only need the count (inverted index UNSORTED queries with count is of milliseconds for millions of results).

Thank you again.

>>I don't know your experience with fulltext, but i it seems to me that you are missing the point.<<
He/she are just trying to help, cut them some slack.  So they are not familiar with Full-Text Search, let alone experience with it? Jjust say so and move on.

Now back to the question.  Have you implemented any of the recommendations from the link you posted?  If so, what were they and were there any improvements?

My guess is that you are now down to bigger and better hardware, I cannot possible see how you can significantly improve the performance to retrieve a count over so many rows.  This is no different than getting a count on a large table withour any indexes.

@acperkins:
Sometimes writen words sound "agressive" but they aren't (or where not when i wrote them :-) ).

Back to the subject:
We are testing the query recommendations found here: http://technet.microsoft.com/en-us/library/ms142560.aspx
The ones in the first document i posted that can be applied to our case had already been done/tested.

"This is no different than getting a count on a large table withour any indexes."
I hope this is not true (for the sake of IO :-) ). For UNSORTED queries (i am only asking for a count) the fulltext engines usually do reverse index matching that is VERY high performance (pure pointer navigation on "compressed" arrays).

Thks.

>>The ones in the first document i posted that can be applied to our case had already been done/tested.<<
The two that I found interesting were using CONTAINSTABLE instead of CONTAINS and partitioning the table into multiple tables and then doing a UNION ALL to get the total.

>>I hope this is not true (for the sake of IO :-) ) <<
Try it some time. :)
<quote>
From the original link you posted, this is the part that you may want to highlight:
Full-text query performance is roughly proportional to the number of matching keys returned from the catalog. On a large catalog, querying for words that are only rarely present in the indexed column returns fewer keys than querying for words that are contained in many columns, and accordingly the queries that return fewer matching keys generally perform better.
</quote>

Try using manual change tracking. Query performance might be lower during indexing.

Another solution might be a fragmented index - you might need to do a full merge
ALTER FULLTEXT CATALOG catname REORGANIZE

What a dumb to try and try again :)

Please understand that determining exact COUNT(*) = 247,348,458,434 will ALWAYS be slow because SQL Server should read ~100k pages even on the index!

Even Google returns approximation "of about 1,620,000,000", not exact count!

Well, that's not exactly right. Number of occurrences is kept on top of the index and if the index is not fragmented - the result should be almost instantaneous.

Well, but not exactly :) even for perfect indexing case, to determine N=535,456,735,986 count server should read LOG2(N) ~ 26 pages, but to determine "More than 500,000,000,000" it should read only one page...

Anyway, 26 times faster, and for non-perfect index matching (for ex LIKE '%WORD%') it can be thousands times.

Wondering if somebody got the final solution to this problem ? I am currently facing the same issue where the Count(*) is taking a very long time...

thanks
shyam

hi,
I ended up using the index on the other table.
Thx to all. Next time I face this will get into more detail..,, no time now...