Question

JimVas on Wed, 26 Aug 2009 12:06:42


i have installed sql server 2005 in windows server 2003 .The full text search was working very fast (less than 1 sec) for about a month and after a server restarting it is working extremely slow(more than 8 min).
I have deleted and recreated the full text index several times but still is very slow.
Please provide me some help

thanks

Sponsored



Replies

Hilary Cotter on Fri, 28 Aug 2009 13:24:45


Has the amount of data in the table changed significantly?

How many rows are returned after 8 minutes?

If you issue a top_n_by rank query limiting your search to 10 results is it fast?

JimVas on Fri, 28 Aug 2009 20:10:56


Thanks for your reply

my table has 400.000 rows and it was working fine a week ago ,that means returning 1000 rows in less than 1 sec,now my table has 406.000 rows and it droped performance dramatically it returns the same results in 15 minutes..... or more
Is that a problem with the index itself ?
Does the index crash after a sudden voltage failure ?
I did drop and recreate the index several times but there is no change in time,i also change the directory the index sits but still no change

Michael Hotek on Sun, 30 Aug 2009 17:43:55


SET STATISTICS PROFILE ON

Then run your query.  If you look across to the average cost column, do you see anything that has a value of EXACTLY 1000?

JimVas on Mon, 31 Aug 2009 06:48:04


SET STATISTICS PROFILE ON

Then run your query.  If you look across to the average cost column, do you see anything that has a value of EXACTLY 1000?

Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals

thanks for your reply

i did that test , and i see a value of 10000 in <estimate rows> column.
Every day my search becomes slower and slower

Please give me some help......

Hilary Cotter on Mon, 31 Aug 2009 11:48:17


I doubt your index is corrupted.

Can I see what your query looks like?

Michael Hotek on Tue, 01 Sep 2009 00:47:56


You have one of two things happening:
1. You are passing non-Unicode data to the full-text function you are using
2. You are modifying the value before passing it to the full-text function

When you do either of these, SQL Server has no way to evaluate stats for the full-text piece of the query, so it uses a default value of 10,000.  That means the full-text function will never be used to restrict the query.  Instead it will be iterated across for everything returned from the rest of the query.

To fix that, you need to fix one or both of the issues above.

JimVas on Tue, 01 Sep 2009 15:31:55


You have one of two things happening:
1. You are passing non-Unicode data to the full-text function you are using
2. You are modifying the value before passing it to the full-text function

When you do either of these, SQL Server has no way to evaluate stats for the full-text piece of the query, so it uses a default value of 10,000.  That means the full-text function will never be used to restrict the query.  Instead it will be iterated across for everything returned from the rest of the query.

To fix that, you need to fix one or both of the issues above.

Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals

Thanks for your reply

First none of these 2 things happens.

After a lot of tests i realise that by deleting the most recent data from database the problem is fixed.
i have noticed that by deleting a specific date period from my table the problem fixes.
Im using sql server 2005 developer edition and i wonder if there are any limitations in that version

Michael Hotek on Wed, 02 Sep 2009 03:32:00


No, there aren't any limitations.  If you check STATISTICS_PROFILE now, is the value something other than exactly 10,000?  If so, then the performance problem is exactly what I mentioned before.  I've seen this happen, many times, going back to the RC of SQL Server 2005 when we opened the support case to troubleshoot it.

Can you post the query that you are running?

JimVas on Wed, 02 Sep 2009 05:27:57


No, there aren't any limitations.  If you check STATISTICS_PROFILE now, is the value something other than exactly 10,000?  If so, then the performance problem is exactly what I mentioned before.  I've seen this happen, many times, going back to the RC of SQL Server 2005 when we opened the support case to troubleshoot it.

Can you post the query that you are running?
Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals


When the query run fast the value in STATISTICS PROFILE is 1,when is Slow the value is 10000.
The query im using is the above

SELECT

 

inbox.id, inbox.mclientid_ as mclientid,inbox.mattach, inbox.messtypeid_ as messtypeid, inbox.mflag_ as mflag, inbox.mdate, inbox.mtime,inbox.mentrydate,inbox.mattachcode,inbox.msendername, inbox.msendermail, inbox.msubject,inbox.mread_ as mread,inbox.mread_ as mbodyplain FROM inbox INNER JOIN InboxUsers ON inbox.id = InboxUsers.mailid
where inboxusers.date>= @dfrom
and inboxusers.date<= @dto
and inboxusers.username= @username
and contains(inbox.mbodyplain,@keyword)

I have a fulltext index on <inbox> table on <mbodyplain> field and on the other table <inboxusers> there are 2 indexes on<date,username> field.

Thanks

JimVas on Fri, 11 Sep 2009 08:43:59


No, there aren't any limitations.  If you check STATISTICS_PROFILE now, is the value something other than exactly 10,000?  If so, then the performance problem is exactly what I mentioned before.  I've seen this happen, many times, going back to the RC of SQL Server 2005 when we opened the support case to troubleshoot it.

Can you post the query that you are running?
Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals


When the query run fast the value in STATISTICS PROFILE is 1,when is Slow the value is 10000.
The query im using is the above

SELECT

 

inbox.id, inbox.mclientid_ as mclientid,inbox.mattach, inbox.messtypeid_ as messtypeid, inbox.mflag_ as mflag, inbox.mdate, inbox.mtime,inbox.mentrydate,inbox.mattachcode,inbox.msendername, inbox.msendermail, inbox.msubject,inbox.mread_ as mread,inbox.mread_ as mbodyplain FROM inbox INNER JOIN InboxUsers ON inbox.id = InboxUsers.mailid
where inboxusers.date>= @dfrom
and inboxusers.date<= @dto
and inboxusers.username= @username
and contains(inbox.mbodyplain,@keyword)

I have a fulltext index on <inbox> table on <mbodyplain> field and on the other table <inboxusers> there are 2 indexes on<date,username> field.

Thanks


Dear Sirs

Im Still waiting for reply........

Please send me a reply as soon as possible

Hilary Cotter on Mon, 14 Sep 2009 11:00:55


Create these indexes:

create index inboxusers_1 on inboxusers(date, username) include(mailid)
create index inbox_1 on inbox(id)


then see if this query runs any faster.

SELECT inbox.id, inbox.mclientid_ as mclientid,inbox.mattach, inbox.messtypeid_ as messtypeid, inbox.mflag_ as mflag, 
inbox.mdate, inbox.mtime,inbox.mentrydate,inbox.mattachcode,inbox.msendername, inbox.msendermail, 
inbox.msubject,inbox.mread_ as mread,inbox.mread_ as mbodyplain FROM inbox INNER JOIN InboxUsers 
ON inbox.id = InboxUsers.mailid
join CONTAINSTABLE(inbox, mbodyplain,@keyword) as k on k.[Key]=inbox.id
where inboxusers.date>= @dfrom 
and inboxusers.date<= @dto 
and inboxusers.username= @username 

JimVas on Sat, 19 Sep 2009 05:10:47


Create these indexes:

create index inboxusers_1 on inboxusers(date, username) include(mailid)
create index inbox_1 on inbox(id)


then see if this query runs any faster.

SELECT inbox.id, inbox.mclientid_ as mclientid,inbox.mattach, inbox.messtypeid_ as messtypeid, inbox.mflag_ as mflag, 

inbox.mdate, inbox.mtime,inbox.mentrydate,inbox.mattachcode,inbox.msendername, inbox.msendermail, 

inbox.msubject,inbox.mread_ as mread,inbox.mread_ as mbodyplain FROM inbox INNER JOIN InboxUsers 

ON inbox.id = InboxUsers.mailid

join CONTAINSTABLE(inbox, mbodyplain,@keyword) as k on k.[Key]=inbox.id

where inboxusers.date>= @dfrom 

and inboxusers.date<= @dto 

and inboxusers.username= @username 




looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

thanks for your reply
After a lot of tests i found that there is a problem with (date, username) index ,after i rebuild the index the problem seems to be solved but i can not understand why the index corrapts and needs rebuild?
I will try your query  and i will let you know

thank you again

M. Golden on Thu, 08 Nov 2012 17:46:38


You have one of two things happening:
1. You are passing non-Unicode data to the full-text function you are using
2. You are modifying the value before passing it to the full-text function

When you do either of these, SQL Server has no way to evaluate stats for the full-text piece of the query, so it uses a default value of 10,000.  That means the full-text function will never be used to restrict the query.  Instead it will be iterated across for everything returned from the rest of the query.

To fix that, you need to fix one or both of the issues above.

Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals

Excellent point regarding non-Unicode text especially if you have conditions on string constants.

I had added a Fulltext index to a text field as a particular block of SQL was taking a very long time to run due to having a condition on the text field.
But despite adding the index the performance did NOT increase.

For example I had SQL with the following condition:

WHERE MyTextField LIKE '%My Text%'

But I should have had:

WHERE MyTextField LIKE N’%My Text%’

Once I prefixed the string constant with 'N' to indicate Unicode text the performance increased significantly.

Thanks for mentioning this small but important point!  Hopefully this will be of value to someone else that is using string constants and has forgotten the Unicode prefix!

SQLWork on Fri, 09 Nov 2012 21:31:53


Yes, but if you had used the full text feature it could be even faster.  A LIKE does not  invoke any full text features.

  ... CONTAINS(MyTextField, '"My Text"');

The full text predicates are CONTAINS, CONTAINSTABLE, FREETEXT, and FREETEXTTABLE.

RLF