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.