March 15, 2011 at 5:32 pm
Hi
The query plan shows a table scan from a dyn. created query (ERP):
SELECT
A.CONNECTIONPOINTID,A.DESCRIPTION,A.FROMRIVER,A.FROMRIVERPK,
A.TORIVER,A.TORIVERPK,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.RECVERSION,A.RECID
FROM RIVERCONNECTIONTABLE A
WHERE ((DATAAREAID=@P1) AND ((FROMRIVER=@P2) OR (TORIVER=@P3)))
There is a Non-Clustered index on: dataareaid, fromriver, toriver
The table has only few rows, less than hundert, may table scan is choosen instead of index scan.
What if there are a lot more rows, will that index help or does the OR clause avoid that?
Thanks for help, Jan
March 15, 2011 at 5:50 pm
Depends on how you build the index and the selectivity of two of the three options.
On a smallish table, you're looking at the speed to load the pages (5 or 6) vs. loading the indexes and then loading some of the pages anyway. The optimizer will usually decide it's faster to load up the pages when the table is very, very small.
As to the OR completely destroying seekability on a NC index, ... errr... you know, I'm not sure, and I should. I'm off to beat on the optimizer a while. Hopefully someone swings by in the mean-time.
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
March 16, 2011 at 12:02 am
For an OR you need one index for each part of the OR. A single composite index can't be used for seeks when there are ORs.
Try the following two indexes (drop the one you have if it's not needed for other stuff, if it is then ignore the first index recommendation here.
Index on dataareaid, fromriver
Index on dataareaid, TORIVER
Opposite column order on both is OK too.
If SQL doesn't use them it's because they are not covering. If it's important that SQL does, add all the columns from the select as INCLUDE to both indexes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2011 at 5:44 am
Another way that sometimes works is to eliminate the OR by rewriting the query to use UNION ALL and then putting each side of the OR clause on either side of the UNION ALL statement. It's not always the best approach, but it is a very viable method for dealing with an OR clause.
"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
March 16, 2011 at 5:50 am
Grant Fritchey (3/16/2011)
Another way that sometimes works is to eliminate the OR by rewriting the query to use UNION ALL and then putting each side of the OR clause on either side of the UNION ALL statement. It's not always the best approach, but it is a very viable method for dealing with an OR clause.
From what I recall, that was useful in SQL 2000 because the optimiser had more options for a union than for an Or. 2005+ it has the same options for both so shouldn't make a difference (shouldn't, not won't)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply