snippetsqlModerate
Disable top (n) Sort Optimization for a specific view
Viewed 0 times
topdisableviewoptimizationforspecificsort
Problem
I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.
As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.
This (top N) queries in the view perform super bad because the optimizer is choosing a different execution plan for this view (afaik CQScanTopSortNew)
I've tried to do some optimizations for the top (N) use case, like using hash joins but this spoils the non top (n) use cases.
The non top (n) performs good. I would like to know how can I prevent the optimizer to choose a different execution plan when it has a top (n) clause without dramatically change the structure or functionality of the view.
For instance, if I add a select distinct inside the view, the optimizer chooses the correct plan always, but the functionality of the view changes.
As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.
This (top N) queries in the view perform super bad because the optimizer is choosing a different execution plan for this view (afaik CQScanTopSortNew)
I've tried to do some optimizations for the top (N) use case, like using hash joins but this spoils the non top (n) use cases.
The non top (n) performs good. I would like to know how can I prevent the optimizer to choose a different execution plan when it has a top (n) clause without dramatically change the structure or functionality of the view.
For instance, if I add a select distinct inside the view, the optimizer chooses the correct plan always, but the functionality of the view changes.
Solution
I can't think of a fully transparent way to achieve what you want with views without disabling row goals in general, which probably won't suit your purposes.
As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.
Perhaps you could convince them to use a
They may find this more natural than choosing an arbitrary sort order (and one which is guaranteed to need a physical sort.
All that said, it's possible that a detailed investigation would reveal tuning options to make the query with
As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.
Perhaps you could convince them to use a
TOP (x) PERCENT instead? This has to count the number of rows in the whole set to work out the percentage. Materializing the full set comes with a tempdb cost, but this could be manageable if the data is a reasonable size. In any case it will give a plan without a row goal.SELECT TOP (1) PERCENT TV.* FROM TheView AS TV;They may find this more natural than choosing an arbitrary sort order (and one which is guaranteed to need a physical sort.
All that said, it's possible that a detailed investigation would reveal tuning options to make the query with
TOP as quick as it should be. That's consulting work though, so beyond what can be addressed here on the basis of estimated plans alone.Code Snippets
SELECT TOP (1) PERCENT TV.* FROM TheView AS TV;Context
StackExchange Database Administrators Q#260304, answer score: 11
Revisions (0)
No revisions yet.