3

This is the schema :

enter image description here

And this is the sql that as I understand is too complex for SQL Optimizer: 

SELECT * FROM 
(
    select pp.Id as PaymentPartId,  b.Id as BudgetId, grouping(bp.ID) as g1 , sum(pp.Amount) PaymentsSum, sum(bp.Amount) BudgetSum
    from  Projects pr 
            inner join Payments p      ON pr.Id = p.ProjectID
            inner join PaymentParts pp ON p.Id = pp.PaymentId
            inner join Budgets b       ON pr.Id = b.ProjectID
            inner join Budgetparts bp  ON b.Id = bp.BudgetId
    group by pp.Id, b.Id, rollup(bp.ID)
)  x
WHERE   x.PaymentPartId = 777

SQLFIDDLE: http://sqlfiddle.com/#!6/aa74e/11 (with autogenerated data)

What I expect: execution plan should contain index seek on x.PaymentPartId. Why? Because this query is equivalent to:

select pp.Id as PaymentPartId,  b.Id as BudgetId,  grouping(bp.ID) as g1, sum(pp.Amount) PaymentsSum, sum(bp.Amount)  BudgetSum
from  Projects pr 
        inner join Payments p      ON pr.Id = p.ProjectID
        inner join PaymentParts pp ON p.Id = pp.PaymentId
        inner join Budgets b       ON pr.Id = b.ProjectID
        inner join Budgetparts bp  ON b.Id = bp.BudgetId
WHERE   pp.Id = 777
group by pp.Id, b.Id, rollup(bp.ID)

...and the last query uses index seek.

But SQL Optimizer not only refuse to use the index but ignore all hints (I propose you to expirement wiht sqlfiddle - it is really interesting).

So the question is: am I right that it is impossible to force SQL Server Optimizer to use index seek there? It seems like rollup is something that split sql optimizer "optimization frame" to two parts and it makes it impossible to optimize WHOLE query.

P.S. For those who votes for closing this "non-programming question": try to put optimizer hints (sqlfiddle is ready to test your programming skills!).

Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142
  • No, it isn't equivalent. – ypercubeᵀᴹ Feb 24 '14 at 13:16
  • why you think so? it returns the same result. you can run set except operation in order to make sure that... – Roman Pokrovskij Feb 24 '14 at 13:19
  • Maybe they are equivalent, not sure, can't get my head around `ROLLUP` this morning. But why aren't you using the second query? – ypercubeᵀᴹ Feb 24 '14 at 13:31
  • So you estimate this should be the same but do like this : select cast(myvar as int) from (select * from mytable where is_numeric(mywar) = 1) p. And here sometime we have good result, sometimes not and we estimate that the subquery should isolate and not gives an error. Your query are different and in my point of view is working correctly :) And as you notice, i'm not talking about the rollup but only subqueries and the place of your where clause – Mathese F Feb 24 '14 at 13:33
  • OK. What you would say if I will be able to point you that Oracle can optimize it (I see the clear TABLE ACCESS BY INDEX ROWID in Oracle execution plan)? – Roman Pokrovskij Feb 24 '14 at 13:39
  • "But why aren't you using the second query?" - because first query emulates the select form view. I expect that SS QO could do some optimization work for me when I quering view, and I expect that sometimes I could use hints.. That what about this question is. – Roman Pokrovskij Feb 24 '14 at 13:44
  • @MatheseF: if you are "not talking about rollup but only subqueries" - and think that SO do not optimize through subqueries, then just comment out rollup and grouping, and you will found that now SO become able to use index seek (and uses it). – Roman Pokrovskij Feb 24 '14 at 14:00
  • 2
    Or use an inline table valued function instead of a view then you can push the predicate down yourself. Similar to [the workaround here](http://www.sqlperformance.com/2013/03/t-sql-queries/the-problem-with-window-functions-and-views) – Martin Smith Feb 24 '14 at 14:00
  • @MartinSmith , thank you Martin, I know at least 5 ways to get the work done, but there I'm more interested in QO details. Why hints doesn't work? – Roman Pokrovskij Feb 24 '14 at 14:11
  • Dunno, Ask them! [Report it on Connect](https://connect.microsoft.com/SQLServer/Feedback). – Martin Smith Feb 24 '14 at 14:40
  • 2
    https://connect.microsoft.com/SQLServer/feedback/details/822931/query-optimizer-doesnt-understand-how-to-optimize-rollup – Roman Pokrovskij Feb 24 '14 at 15:36

1 Answers1

1

Why hints doesn't work? - Roman Pokrovskij

It is in the documentation: http://technet.microsoft.com/en-us/library/ms181714.aspx

Query hints can be specified only in the top-level query, not in subqueries

Cesar Vega
  • 95
  • 5