March 17, 2022 at 9:08 am
pdanes wrote:Grant Fritchey wrote:Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
I know, but that doesn't really matter here. The table is small, the server is very lightly loaded, and this class of queries is sort of a catch-all, for things that users are unable to find using more tightly directed searches.
It does matter.
The way you wrote the query will cause the engine to join the small table to the big recordsets coming from tables Podrobnosti and Akces, and then apply the filter to every record in that huge resulting recordset.
That's why I recommended to move dbo.Taxonomy from JOIN to WHERE EXISTS. This way the filter will apply to the small set within the table and only then the outcome will be used for filtering the big record sets.
And in this case the use of variable would not really matter, assuming the number of records in Taxonomy will stay small forever.
I can't use EXISTS quite the way you recommended, because Podrobnosti is not in the Select clause, but it IS in the Join clause. I want the top record from Akces, but the condition is by the contents of Taxonomy, which is joined to Akces only through Podrobnosti: T.TaxonAutoID -> P.TaxonAutoID, P.AkcesAutoID -> A,AkcesAutoID.
The number of records in Taxonomy is not trivial - 23,000, and will remain around that, but the number of selected records is unlikely to ever be more than a few, usually only one.
March 17, 2022 at 2:02 pm
Grant Fritchey wrote:Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
A question, though - do you have any idea why using a variable bogs the query so badly, while using a literal flies, even without that index? I don't understand why that one thing should make such a difference, or really, any difference.
Yeah. A variable will use an average from the stats. A literal value will be used against the stats to look for accurate numbers. Same deal as parameter sniffing. Sometimes, average is better. Sometimes actual is better. Ain't query tuning fun?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply