Can I tune this?
-
TheSQLGuru (12/23/2010)
Bummer is that the optimal index is essentially another copy of the table, which could be large given that it is a DW fact table and also gum up the data load, index maintenance, backups, etc. But if the query needs to run fastest sometimes you gotta do what you gotta do!Ta.
It's a wide index, for sure, but omits 6 or 7 columns from the main table, so it's not really a copy. It looks like a dimension table to me...?
Anyway, I was looking to explain the query plans Lutz saw really, rather than propose a final solution. No doubt Brandie's real query has very different characteristics. If she replies, I'll be happy to contribute further if I can usefully do so.
If there are typically a good number of version records per coverage key, there might be room for the APPLY to win yet 🙂
Paul
-
Back to this project...
Okay, I appear to be stuck on what I can do to the underlying tables (read - not much), but those links are helping me understand a few things. I appreciate them.
The cube is processing much faster than I expected, given how many named queries I'm using in it. The query I posted here has ended up being a section of a much bigger query, which has gotten filtered down with various JOIN conditions and a WHERE clause that addresses one of the other (not posted here) tables. So I think that helped.
Thank you all for your responses. I'm still looking at my queries for performance enhancements because the cube does take over an hour to process. Still, that's better than the 5-6 hours I initially expected.
EDIT: I just realized I forgot to mention that this query will be used in a cube. Doh. Don't know if that's important to tuning the query itself.
-
Paul,
What is the effect of building a non-clustered index over a clustered index?
The table already uses CoverageKey and Effective (both ASC) as it's clustered index. Include only works on non-clustered indexes, so I can't change the clustered index to be what you suggested.
I've never written a non-clustered index to overlay a clustered index before. Usually, I use other columns. Is there any kind of performance hit to using a clustered index within a non-clustered index?
-
Paul,
On your blog, you state:
Impressively, the estimated execution cost for the original query was about 0.007; for the “Segment Top” plan, the estimated cost is about 0.006 - slightly less!
Where did you get those figures? I can't find them anywhere in the Display Estimated Execution Plan.
-
Brandie Tarvin (2/17/2011)
Paul,What is the effect of building a non-clustered index over a clustered index?
The table already uses CoverageKey and Effective (both ASC) as it's clustered index. Include only works on non-clustered indexes, so I can't change the clustered index to be what you suggested.
I've never written a non-clustered index to overlay a clustered index before. Usually, I use other columns. Is there any kind of performance hit to using a clustered index within a non-clustered index?
I'm sure Paul will have a much more precise answer...
But here's my explanation: the clustered index has all remaining columns in the leaf node, whereas a nonclustered overlapping a clustered index is only limited to the columns representing the clustered index. So it's usually much more narrow.
At least that's what I use as a reson for overlapping the indexes in such a way.
-
Brandie Tarvin (2/17/2011)
What is the effect of building a non-clustered index over a clustered index?The table already uses CoverageKey and Effective (both ASC) as it's clustered index. Include only works on non-clustered indexes, so I can't change the clustered index to be what you suggested.
I've never written a non-clustered index to overlay a clustered index before. Usually, I use other columns. Is there any kind of performance hit to using a clustered index within a non-clustered index?
Well, I'm not Paul, but hopefully I can help.
First the easy question. The first difference in the indexes is Effective being DESC. I'd have to poke through the structure but my guess is this would help it line up with other things you're doing, such as order bys.
Regarding the include on the clustered index, it's unnecessary because the clustered index automatically includes everything in the table, as it's basically the primary storage's sorting and seeking mechanism. It sits on top of the primary table, using the table itself as the leaf level.
Slapping a NC Index on top of your Clustered in a similar format is usually to lessen memory and processing load by removing unnecessary columns from heavy processing. It's a technique that especially useful in reporting environments as there's little data mobility (usually) so the overhead of multiple indexes is light after the initial builds.
You'll find your performance hits for it is like in any other non-clustered index, that being updates, deletes, and inserts will take longer updating yet another location. Being on the same item as the clustered index will hopefully lessen your impact, as usually clustered indexes don't change much, if at all.
EDIT: Heh, Lutz got in ahead of me and said it much more succinctly. Sorry about the echo.
- Craig FarrellNever 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 -
Brandie Tarvin (2/17/2011)
Paul,On your blog, you state:
Impressively, the estimated execution cost for the original query was about 0.007; for the “Segment Top” plan, the estimated cost is about 0.006 - slightly less!
Where did you get those figures? I can't find them anywhere in the Display Estimated Execution Plan.
Hey Brandie,
The exact figures may vary a little, but the relationship should be about the same:
Paul
-
Brandie Tarvin (2/17/2011)
What is the effect of building a non-clustered index over a clustered index?Exactly as Lutz and Craig said: the non-clustered index is much narrower, so more many more rows fit on each 8KB index page. More rows per page = fewer page accesses = faster.
The table already uses CoverageKey and Effective (both ASC) as it's clustered index. Include only works on non-clustered indexes, so I can't change the clustered index to be what you suggested.
The clustered index 'includes' all in-row columns already, so you don't need to worry about that. The key point is that DESC ordering presents the rows to the Segment iterator in the right order, as I mentioned before.
I've never written a non-clustered index to overlay a clustered index before. Usually, I use other columns. Is there any kind of performance hit to using a clustered index within a non-clustered index?
The main disadvantage to creating a non-clustered index on the same columns as the cluster is that it needs to be maintained. Typically, this overhead is rather small. It also uses a little extra space, of course.
-
Thank you all for answering my questions. I appreciate it. I never viewed indexes that way before. I guess I still have a lot to learn.
And I forgot to try the mouse hover. Thank you for the image, Paul.
Back to the project. Wish me luck. @=)
-
Brandie Tarvin (2/18/2011)
And I forgot to try the mouse hover.You can also click on a plan icon (SELECT in this case) and look in the Properties window (hit F4).
Back to the project. Wish me luck. @=)
Good luck.
-
Brandie Tarvin (2/18/2011)
Thank you all for answering my questions. I appreciate it. I never viewed indexes that way before. I guess I still have a lot to learn.And I forgot to try the mouse hover. Thank you for the image, Paul.
Back to the project. Wish me luck. @=)
As requested: Good Luck!!
-
Oh my gosh.
Paul, that index does some serious IO and OP cost reduction on that specific bit of code. And that's using the old code. With the new code (putting that bit into a CTE that I later join to the rest of the query), it brings down the Cached Plan Size and the Subtree Cost.
For example, IO cost on this branch (where this code is) went from 229.972 to 68.355.
This is awesome. Thanks everyone for the pointers. This is helping a lot.
Viewing 12 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply