U4-9434 - Add index to cmsDocument.nodeId + cmsDocument.publish

Created by Shannon Deminick 24 Jan 2017, 08:07:22 Updated by Claus Jensen 29 Mar 2017, 12:46:41

Subtask of: U4-9432

Create non clustered index on these columns due to the way queries work for content

Comments

Shannon Deminick 10 Feb 2017, 05:07:41

I'm running the database tuning advisor now, i can't remember which queries I've seen or which advisor mentioned this one but for now i'll wait until i know before adding this.


Shannon Deminick 15 Feb 2017, 10:17:37

When the cmsDocument table contains a lot of data and we run this query:

SELECT * FROM cmsDocument WHERE nodeId = 1834 AND published = 1 

which is a query that is run during updating an entity, the sql profiler tells us:

/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.6907%.
*/

/*
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[cmsDocument] ([nodeId],[published])

GO
*/

We also run this one just after that one:

SELECT * FROM cmsDocument WHERE nodeId = 1834 AND newest = 1 

which of course tells us this:

/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.6965%.
*/

/*
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[cmsDocument] ([nodeId],[newest])

GO
*/

However, when there aren't a ton of versions, there is no perf penalty. As far as I can tell this index has been suggested at some point because it's used during updates, however adding both of these indexes to this table will make updating slower. I've checked most of our queries in the query analyzer with a node that has a ton of documents and no other ones show up suggesting adding these or other indexes.

If/when we implement max versions then this won't really be an issue anyways.


Priority: Normal

Type: Task

State: Closed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version:

Sprint: Sprint 52

Story Points: 0.5

Cycle: