6

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.

  • I thought in materializing the result of the view in a table and modify the views just to select the data from the table. But I was wondering first if I can find a solution without telling the users to change how they access the view (there are reports on top that would break) – Lucas Feb 22 at 12:04
11

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 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.

| improve this answer | |
  • 2
    Thanks a lot Paul! I think this is the best approach, I've put a lot of effort trying to optimize the view when probably I would need to put more time fixing the root cause of the complexity. – Lucas Feb 23 at 10:35
1

First, the plans you pasted were the estimated execution plans, we would need the actual execution plan. I'm guessing that you right-clicked in SSMS and selected, "show execution plan"... instead you need to select "Include Actual Execution Plan" prior to running the query. They are usually the same but the actual plan includes the actual number of rows returned by each table/index.

That said and based on what you posted it appears that the problem is that the TOP query is getting a serial execution plan while the non-TOP one is getting a parallel plan. This would make sense because the optimizer will assign a lower cost to the TOP query as its expecting to do much less work.

The undocumented way to force a parallel execution plan is to use TRACEFLAG 8649 like so:

select top 10 * 
from TheView
where Date = '2020-02-20'
option (querytraceon 8649)

Running undocumented stuff in Production environments is a bad idea. Fortunately, Adam Machanic created make_parallel(): http://dataeducation.com/next-level-parallel-plan-forcing-an-alternative-to-8649/

You would use it like this.

select top 10 * 
from TheView
CROSS JOIN dbo.make_parallel()
where Date = '2020-02-20';

Another thing to consider is that your you are not including an ORDER BY clause with your TOP clause. If the order doesn't matter , you can play around with different ORDER BY's. Forcing a sort may force a parallel plan.

| improve this answer | |
  • 1
    Thank you very much for your answer Alan. I've tried the function make_parallel() for both top and non top cases and the performance actually go worst. I really appreciate the effort :) – Lucas Feb 23 at 10:30
  • At least you got to try it. It IS an excellent function that has been a lifesaver in the past but, not always. Paul Whites answer above is excellent. I learned a couple new things as always. – Alan Burstein Feb 24 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.