May 20, 2011 at 9:12 am
Hi,
This is the first time I am posting here. Can anyone, give some direction on the issue I having.
-- 1) The following runs in less than 1 second - ouput count = 2081
SELECT count(*) FROM Consultants
WHERE Consultants.ConsIntID IN (SELECT ItemIntID FROM SaveSets WHERE SaveSetIntID='SS20047560')
-- 2) The following runs in less than 1 second - output count = 21603
SELECT count(*) FROM Consultants
WHERE ( Contains(Consultants.uuResumeText, '"sales"'))
-- 3) The following is a combination of the above two, takes 104 seconds - output count = 1255
SELECT count(*) FROM Consultants
WHERE Consultants.ConsIntID IN (SELECT ItemIntID FROM SaveSets WHERE SaveSetIntID='SS20047560') AND
( Contains(Consultants.uuResumeText, '"sales"'))
-- 4) The following is pretty much same as above, takes less than 1 second - output count = 2526
SELECT count(*) FROM Consultants
WHERE Consultants.ConsIntID IN (SELECT ItemIntID FROM SaveSets WHERE SaveSetIntID='SS00020949') AND
( Contains(Consultants.uuResumeText, '"sales"'))
Consultants Table - 100,000 Rows
SaveSets Table - 3,000,000 Rows
ConsIntID, ItemIntID, SaveSetIntID - unique indexes.
Can anyone throw any light on why query-3 is slower by a magnitude of 100 compared to query-1 and query-2.
Thanks for any help or any direction.
Kris
May 20, 2011 at 9:25 am
Check the stats. One of the plan must estimate to return a crap load of data and decides to scan instead of seek.
May 20, 2011 at 11:16 am
Pretty much with Ninja on this one. Light up 'actual execution plans' and take a look at the difference in them.
This parameter: SaveSetIntID='SS20047560'
My guess is when you get to the data itself, this is an uneven distribution, which means you're going to end up with different plans depending on the parameters. If you're planning to turn this into a procedure, take a look at parameter sniffing problems, my guess is you'll run into them. Also look into the 'optimize for' option.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 20, 2011 at 11:48 am
Did you try running the third query more than one time? Since the fourth ran much faster, you may be running into SQL Server having to build stats and execution plans on a first run, and being able to take advantage of them on the next query.
- 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
May 23, 2011 at 2:08 pm
@ninja, These stats are from the Actual Execution.
In the query-3, the 99% (of 104 secs) is used by the "Remote Scan/FullText Search Engine"
In the query-1, the 99% (of 1 sec) is used by the "Remote Scan/FullText Search Engine"
@craig-2, Yes, there is very uneven distribution (90% of the records are "SS000xxxxx", and only 10% are "SS200xxxxx"), but that still does not explain the reason why Query-3 takes 100+ seconds, vs less than a second each for both Query-1 AND Query-4.
@GSquared, I ran many times, and in very different orders. But it did not make any difference. (Ex, I ran query-3, then Query-4, back to Query-3, etc).
I am no way a DB expert. Being said that, is it possible (for some crazy reason), the last part of query-3 "( Contains(Consultants.uuResumeText, 'sales')) " is invoked 1255 times (i.e. for each record retrived by the rest of the where clause)? I know this sounds stupid.
Help please, any direction given will be really appreciated.
Kris
May 23, 2011 at 2:31 pm
spx (5/23/2011)
@ninja, These stats are from the Actual Execution.In the query-3, the 99% (of 104 secs) is used by the "Remote Scan/FullText Search Engine"
In the query-1, the 99% (of 1 sec) is used by the "Remote Scan/FullText Search Engine"
@craig-2, Yes, there is very uneven distribution (90% of the records are "SS000xxxxx", and only 10% are "SS200xxxxx"), but that still does not explain the reason why Query-3 takes 100+ seconds, vs less than a second each for both Query-1 AND Query-4.
It's nicknamed the tipping point. Eventually the system decides it's getting too many pages and goes to a scan instead of an index seek. My guess is Query 3 reached this.
@GSquared, I ran many times, and in very different orders. But it did not make any difference. (Ex, I ran query-3, then Query-4, back to Query-3, etc).
Excellent, always a good check.
I am no way a DB expert. Being said that, is it possible (for some crazy reason), the last part of query-3 "( Contains(Consultants.uuResumeText, 'sales')) " is invoked 1255 times (i.e. for each record retrived by the rest of the where clause)? I know this sounds stupid.
Actually, very good insight. CONTAINS is a full text indexing method, and it depends on what order it triggers in from the optimizer. However, to answer your question... We'll have to ask a few more questions.
First, take a look in my sig for the index/optimization help link (second one down, on the left). It'll walk you through pulling out your DDL (tables) and your execution plans (sqlplans) for us to peruse and see what we can help you with. My guess is some index tweaking and a bit of discussion and we can help you get Query 3 back on track... but we won't know for sure until we see everything that's going on.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 23, 2011 at 2:57 pm
As already suggested, look at the query execution plan to know what happending. To help things, I'd suggest inner joining that table SaveSets instead of using a sub-select in the WHERE clause.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply