January 27, 2009 at 1:26 pm
I have a web application that frequently performs full-text queries on lists of names. The names all belong to different client accounts. So the queries look something like this:
select blah
from TableA a inner join TableB b on a.ID=b.TableAID
where b.AccountID in (1,2,3) and
contains(a.FirstName, a.Lastname,... 'searchterm')
Most of the time this performs well. The problem is that sometimes (depending on the values in the IN operator) the execution plan differs.
In the FAST case, it will perform the FTS first, then do index seeks to get the data.
In the SLOW case, it will do index seeks on the AccountID (resulting in 150,000 index seeks in some cases), and then do the FTS on that result set.
I think that to solve this problem I need to force SQL Server to perform the FTS first. But when I add the WITH(index, 'TableAFullText') hint, SQL Server tells me that that index doesn't exist. Apparently it doesn't consider full-text indexes to be "real" indexes.
How can I force SQL Server to perform the FTS first?
January 27, 2009 at 2:39 pm
I don't know that you can force the use of a full text index that way. You could, however, force it to use a specific execution plan. Look up "Use Plan" in Books Online for how to do that. But, be warned, that can be a really bad idea in some cases. Make sure you read the warnings and notes and understand it fully before you use that option.
More likely, the better plan is to check your execution plan for both situations and see what the difference is, and work towards cleaning it up.
IN statements can move from index seeks to index scans pretty easily, if they include enough values that SQL thinks it'll get faster results that way.
- 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
January 27, 2009 at 3:15 pm
Thanks for your reply. I agree that forcing a specific plan would not be the right thing, not least because I have no idea what all the implications would be. As for index scans, there don't appear to be any right now; the problem is too many index seeks.
Here are the execution plans:
FAST query:
http://frameaction.com/LLExecutionPlan01.sqlplan
SLOW query:
January 28, 2009 at 1:35 pm
I tried saving those as sqlplans, but got XML errors from them. Can you save them as sqlplan files, zip them up, and attach them here? That would be best.
- 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
January 28, 2009 at 4:51 pm
Glad to. Here they are, attached. I changed the encoding message at the top from utf-16 to utf-8 so they could be readable by a browser; perhaps SQL Server is choking on that?
January 29, 2009 at 8:04 am
It looks like if you add GroupID to index IX_File, on table dbo.File, you'll probably solve your speed problem. Not a certainty, but it sure looks like it would help. Is that a valid solution, or will modifying that index mess something else up? (Shouldn't, but indexes can affect insert/update/delete speed.)
- 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
January 29, 2009 at 9:58 am
That's a good suggestion, and I tried it, but unfortunately to no avail. I also tried, in my dev environment, getting rid of various indexes that were in the way to try to get it to use this new one, but the execution plan went to an Index Scan before using my new index! Very strange.
I did have some luck by adding the OPTIMIZE FOR UNKNOWN hint at the end of the query. That has speeded things up for the slow cases. But I like the idea of changing the index structure much more than adding hints to the queries, so if you have any more ideas along those lines, they are very welcome. Thanks.
January 29, 2009 at 11:29 am
After you added that column to the index, did the execution plan change?
- 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
January 29, 2009 at 11:57 am
No, it did not. And as I mentioned, I even tried deleting the index that it was using first, only to have the execution plan eventually revert to an index scan.
January 29, 2009 at 2:18 pm
In that case, there's something up with the index and/or the execution plan. Have you tried running the query with a recompile?
- 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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply