Performance Tuning MainTable-Parent-Child

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Performance Tuning MainTable-Parent-Child

Author

Topic

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 14:49:53

I have split a table into Parent/Child so that we can store multiple versions of the data in the Child table.

With the benefit of hindsight I probably should have kept the original single-table and added some sort of child table for "previous versions"

The Parent Table has a column for the ID of the "Latest Version of Child record"

The purpose of this table is to store the names of Images (ID, Image Name, Height and Width)

The new tables are ImageParent, with the original ID and a column for the LatestChildID, and the ImageChild table with ID, Name, Height, Width.

We also took the opportunity to add a Type column to the ImageParent table (Static image or Video) - so that's an additional column that I now need in my query

I used to do


SELECT *
FROM MainTable AS T
    JOIN Images AS I
        ON I.ID = T.ImageID

and now I have to do


SELECT *
FROM MainTable AS T
    JOIN ImageParent AS P
        ON P.ID = T.ImageID
    JOIN ImageChild AS C
        ON C.ID = P.LatestImageID

and its hurting performance ...

So I tried an Indexed View:


CREATE VIEW MyImageView
WITH SCHEMABINDING
AS
SELECT T.ID, T.Type, P.Name, P.Width, P.Height
FROM ImageParent AS P
    JOIN ImageChild AS C
        ON C.ID = P.LatestImageID
GO
CREATE UNIQUE CLUSTERED INDEX IDX_MyImageView ON dbo.MyImageView
(
	ID
)
GO

and I've changed my query to be:


SELECT *
FROM MainTable AS T
    JOIN MyImageViewAS AS I WITH (NOEXPAND)
        ON I.ID = T.ImageID

but that is no faster than just joining to the Parent/Child pair of tables. Would you expect that?

The Query Plan is clearly using the View and Index.

The thing that is bothering me is the I/O Stats, originally:


Table 'ImageChild'. Scan count 0, logical reads 132, ...
Table 'ImageParent'. Scan count 0, logical reads 128, ...

and the I/o Stats after changing the query to use the view are:


Table 'MyImageView'. Scan count 42, logical reads 315, ...

CPU time is about the same, however, but it doesn't seem like an "improvement" to have so many Scans & Logical I/O's

The actual query is a fair bit more complicated than I have shown, so initially I'm just looking for any general gut feeling suggestions, or advice please.

Or maybe I need to do something else to make proper use of Indexed Views? We are using SQL Web edition, not Enterprise.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-14 : 15:00:59

How often do you access the previous versions? Why not just store previous versions in an archive table, keeping only the current version in the main table?

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 17:13:29

Yeah, that would be the better way - with the benefit of hindsight

We've rolled out the change now, and I'm looking for a minimal impact change for a patch. Changing the table-pair back to a single table and an "archive" table needs a full QA cycle, and we won't be able to do that for 6 months at least.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-15 : 00:07:20

Well in that case, if you execute it in SSMS showing Actual Exec Plan, does it suggest additional indexes?

Kristen
Test

22859 Posts

Posted - 2011-09-15 : 04:02:58

It does, but not related to this. I ignored it because the selectivity is dreadful, but I tried it and it seems to make quite a difference (but unrelated to this parent-child table thingie).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources