Index Needed?

datsun80 2014-12-03 21:37:33

I cannot rewrite the query so I am hoping there is an Index that will get rid of the Index Spool. I tried a couple of them but they did not help.

Is a spool unavoidable in this situation?

Here is the query:

SELECT  [t0].[Id] ,
        [t0].[DefinitionId] ,
        [t0].[Name] ,
        [t0].[Description] ,
        [t0].[IsActive] ,
        [t0].[SourceKey] ,
        [t0].[VersionStart] ,
        [t0].[VersionEnd] ,
        [t0].[IsComplete] ,
        [t0].[AttributeHash] ,
        [t5].[Id] AS [Id3] ,
        [t5].[DimensionValueId] ,
        [t5].[RangeFrom] ,
        [t5].[RangeTo] ,
        ( SELECT    COUNT(*)
          FROM      [dbo].[DimensionValueCriterion] AS [t6]
          WHERE     [t6].[DimensionValueId] = [t0].[Id]
        ) AS [value] ,
        [t4].[test] ,
        [t4].[Id] AS [Id4] ,
        [t4].[ColumnIndex] ,
        [t4].[OrganizationId] ,
        [t4].[Name] AS [Name2] ,
        [t4].[Ordinal] ,
        [t4].[SourceKey] AS [SourceKey2] ,
        [t4].[IsActive] AS [IsActive2] ,
        [t4].[Description] AS [Description2] ,
        [t4].[IsComplete] AS [IsComplete2] ,
        [t4].[DisplayAsAttribute] ,
        [t4].[DefaultValue] ,
        [t4].[Configuration] ,
        [t4].[IsNavigable] ,
        [t4].[test2] ,
        [t4].[Id2] ,
        [t4].[Name2] AS [Name3] ,
        [t4].[Description2] AS [Description3] ,
        [t4].[SourceKey2] AS [SourceKey3] ,
        [t4].[PrimaryUnitOfMeasureId] ,
        [t4].[CalculateBeginningBalance] ,
        [t4].[UseConvertedAmount] ,
        [t4].[ConfigurationData] ,
        [t4].[IsComplete2] AS [IsComplete3] ,
        [t4].[ReportingCurrencyId]
FROM    [dbo].[DimensionValue] AS [t0]
        LEFT OUTER JOIN ( SELECT    1 AS [test] ,
                                    [t1].[Id] ,
                                    [t1].[ColumnIndex] ,
                                    [t1].[OrganizationId] ,
                                    [t1].[Name] ,
                                    [t1].[Ordinal] ,
                                    [t1].[SourceKey] ,
                                    [t1].[IsActive] ,
                                    [t1].[Description] ,
                                    [t1].[IsComplete] ,
                                    [t1].[DisplayAsAttribute] ,
                                    [t1].[DefaultValue] ,
                                    [t1].[Configuration] ,
                                    [t1].[IsNavigable] ,
                                    [t3].[test] AS [test2] ,
                                    [t3].[Id] AS [Id2] ,
                                    [t3].[Name] AS [Name2] ,
                                    [t3].[Description] AS [Description2] ,
                                    [t3].[SourceKey] AS [SourceKey2] ,
                                    [t3].[PrimaryUnitOfMeasureId] ,
                                    [t3].[CalculateBeginningBalance] ,
                                    [t3].[UseConvertedAmount] ,
                                    [t3].[ConfigurationData] ,
                                    [t3].[IsComplete] AS [IsComplete2] ,
                                    [t3].[ReportingCurrencyId]
                          FROM      [dbo].[DimensionDefinition] AS [t1]
                                    LEFT OUTER JOIN ( SELECT  1 AS [test] ,
                                                              [t2].[Id] ,
                                                              [t2].[Name] ,
                                                              [t2].[Description] ,
                                                              [t2].[SourceKey] ,
                                                              [t2].[PrimaryUnitOfMeasureId] ,
                                                              [t2].[CalculateBeginningBalance] ,
                                                              [t2].[UseConvertedAmount] ,
                                                              [t2].[ConfigurationData] ,
                                                              [t2].[IsComplete] ,
                                                              [t2].[ReportingCurrencyId]
                                                      FROM    [dbo].[Organization]
                                                              AS [t2]
                                                    ) AS [t3] ON [t3].[Id] = [t1].[OrganizationId]
                        ) AS [t4] ON [t4].[Id] = [t0].[DefinitionId]
        LEFT OUTER JOIN [dbo].[DimensionValueCriterion] AS [t5] ON [t5].[DimensionValueId] = [t0].[Id]
WHERE   NOT ( [t0].[VersionEnd] IS NOT NULL )
ORDER BY [t0].[Id] ,
        [t4].[Id] ,
        [t4].[Id2] ,
        [t5].[Id]
/storage/attachments/1075-Plan.pesession
SQLkiwi 2014-12-04 11:08:04
The query optimizer has lots of plan choices for this query, but the information it has available suggests the cheapest strategy is to avoid explicit sorts, and to instead preserve index order to satisfy the final ORDER BY clause.

Is a spool unavoidable in this situation?

No, but it is there for a reason. The index spool lazily caches the results of the outer join below it:

Plan Fragment

This takes advantage of the duplicate values of DefinitionId in the DimensionValue table. The query processor can save time and resources by only performing the join once per unique correlated DefinitionId. Without the spool, the whole join would be repeated for every DefinitionId, including all the duplicates.

So, this is a performance optimization for this particular plan shape. Do not be misled by the estimated cost of 55% for the spool. Something will always cost most in a plan, and the costs are in any case based on estimates only, which can turn out to be wrong or inexactly correlated to actual performance for all sorts of reasons.

How effective the spool is at run time depends on the number of duplicate DefinitionId values, the cost of the spool itself, and the cost of performing the join. If the question had provided a post-execution (actual) plan (with runtime counts and rewind/rebind spool information) it would have been easy to judge.

That said, the fact the question has been asked, and the apparent 5.3 second duration suggests performance is not as good as you would like it to be. It is hard to judge precisely without access to the source data, but it may well be the plan chosen by the optimizer in this case turns out not to be the best choice.

Ensuring statistics are representative of the data may help the optimizer find a better plan. A clue that statistics may be out-of-date is contained in the query plan. It shows, for example, that the DimensionValueCriterion table has no rows, which seems unlikely. Update the statistics as appropriate and see if you get a better execution plan.

Failing that, you may want to test the performance impact of forcing only hash or merge joins using an OPTION (HASH JOIN, MERGE JOIN) hint. If this is effective, and judged to be likely to remain a good choice as data changes over time, you may be able to add this hint to the query (without changing the query itself) using a Plan Guide.

datsun80 2014-12-04 20:54:40
Thank you, this was very informative. I had a hunch that is what was going on. Stats are up to date. The DimensionValueCriterion table does in fact have 0 rows.

The whole query takes about 9 seconds. No one is complaining, I was just trying to get it under 5 seconds.

Kevin 2014-12-03 22:44:00
Do you have non-clustered indexes on [MR2012DataMart].[dbo].[DimensionValue].[DefinitionId] and [MR2012DataMart].[dbo].[DimensionValue].[DefinitionId]?
December 3, 2014
No Comments