March 21, 2011 at 8:50 am
Ok, I'm no DBA and use indexs in a limited capacity.
However I recently introduced a view that is causing serious headaches in terms of performance. The view is rarely updated but read often.
At the minute it comprises of 4 columns and there are no indexes.
ParentId,CategoryId, LevelId, RowCount
All columns are integers and there are no unique values in any of the columns.
Typically I will be joining or filtering on either ParentId or CategoryId.
I think what I need is a covering index so that the view itself is never run.
What do I need in terms of indexes.
I must have a clustered index but should this be my covering index for all columns?
Or should there be a second nonclustered index?
March 21, 2011 at 9:17 am
The view contains a CTE and I just realised that prevents indexing.
Therefore I need to optimise the underlying tables / view design.
ALTER VIEW [dbo].[vw_Product_Category_Levels] WITH SCHEMABINDING
AS
WITH TopCat (ParentId,CategoryId, LevelId)
AS
(
SELECT tbl1.CategoryId, tbl1.CategoryId, 1
FROM dbo.vw_tbl_Product_Categories as tbl1
WHERE tbl1.ParentID = 0 AND tbl1.CAtegoryType = 10
UNION ALL
SELECT TopCat.CategoryId, tbl1.CategoryId, TopCat.LevelId + 1
FROM dbo.vw_tbl_Product_Categories as tbl1 INNER JOIN TopCat
ON tbl1.ParentId = TopCat.ParentID
)
SELECT ParentId,CategoryId, LevelId FROM TopCat
March 21, 2011 at 10:11 am
You'll have to test things. There isn't a way to be sure what's best, but here are a few suggestions. Note that the "view" isn't run, but incorporated into the query plan of whatever query it is included in. Those queries will ultimately determine what indexes make the most sense.
Is this querying another view? Without knowing that it's hard to tell
However, indexes on:
dbo.vw_tbl_Product_Categories - ParentId, CategoryType, CategoryID include (level) might help.
You might reorder CategoryID in there, depending on what other queries you have
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 21, 2011 at 10:22 am
This view is querying underlying views however they are relatively simple.
dbo.vw_tbl_Product_Categories equates to
SELECT Column names
FROM tbl_Product_Categories
WHERE Deleted = 0
PK / FK relatations are indexed.
When I viewed the execution plan most the resource was on an index spool (eager). This is from the CTE (i think)
Output :
[HW_Shop_2].[dbo].[tbl_Product_Categories].CategoryId
Prefix: [HW_Shop_2].[dbo].[tbl_Product_Categories].ParentId, [HW_Shop_2].[dbo].[tbl_Product_Categories].Deleted = Scalar Operator([Recr1007]), Scalar Operator((0))
The other option I'm considering is creating a physical table and creating triggers to update it. This is then essentially my indexed CTE view.
March 21, 2011 at 10:35 am
The index spool is expensive (http://ask.sqlservercentral.com/questions/6268/what-is-index-spool%5Burl%5D%5B/url%5D)
However I'm not sure what you're trying to do with the indexed CTE or using triggers to update a table. Is this queried often and ralely updated?
You also want a wider index to help here, not more indexes. Just because PK/FKs are indexed, that doesn't help with this query. You might need a new index that includes other columns, like Deleted. I'm not sure why you'd hide that in a view here. I get that you might be reusing code, but nesting views usually leads to performance issues. Use that view for querying active products. Don't use it for building your other views.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 21, 2011 at 11:18 am
Basically it's taking a hierarchy and representing it in a flatter view so I can query up and down it and do aggregations etc.
It's a typical "CategoryId, ParentId, Name" type of table.
which then gets flattened to something like :
ToplevelID, CategoryId, CategoryLevel
so if I wanted to query a particular category and all it's sub categories I would just query the TopLevelId and it would list out the categoryId's and the category level it is from.
The reason for the other views is that the application uses non destructive deletes and then clears them out every 30 days. So any table that does this has a view which the application uses instead of the raw table.
I decided to use these views instead of the table so I don't have to add the delete flag and that everything looks at the same version of the truth. If there are other variables that factor into the active records in a table it becomes a nightmare maintaining it. Especially if the application views change for some reason in the future.
March 22, 2011 at 4:59 am
Just so you know, nesting views adds considerably to the work the optimizer has to do to unpack the views to try to get to an optimal execution plan. When there is too much work, it will stop optimizing and simply take whatever plan it has available. This means you could be getting much worse performance than you should. I really strongly advocate against using nested views.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 22, 2011 at 5:39 am
ok thanks, point taken, I will put it into future design considerations.
what I managed to establish last night was that adding the deleted flag to the index I had for parentId got rid of the index spool and the related queries now seem quicker particularly where there are further joins.
The deleted flag should probably factor in pretty much every index.
March 22, 2011 at 6:57 am
I wouldn't use a view for this. If I had to continue with an Adjaceny List, I'd create two UDF's as iTVF's (Inline Table Valued Functions) so I could pick the nodes I wanted to find the upline or downline for. Think of such functions as "views that will take parameters".
--Jeff Moden
March 22, 2011 at 7:01 am
thanks for the tip...
"adjacency list" I knew it had a name!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply