7

We have a view that is optimized for single item queries (200ms no parallelism):

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId = 3374700

It also works on small sets of static ids (~5).

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId in (3374700, 3374710, 3374720, 3374730, 3374740);

However if the objects come from an external source, then it generates a slow plan. The execution plan shows that the execution branch for the view part is ignoring the predicate on ObjectId whereas in the original case it uses them to perform index seeks.

select v.*
  from 
     (
       select top 1 ObjectId from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o  
  join OptimizedForSingleObjectIdView v -- (also tried inner loop join)
    on v.ObjectId = o.ObjectId;

We don't wish to invest in "dual" optimizing the view for non-singular cases. Rather, the solution we "seek" is to repetitively call the view once per object without resorting to an SP.

Most of the time the following solution calls the view row by row. However not this time and not even for just 1 object:

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0 -- non-trivial predicate
        order by ObjectId
     ) o
   cross apply
    (
      select top 2000000000 *
        from OptimizedForSingleObjectIdView v_
       where ObjectId = o.ObjectId 
       order by v_.SomeField
    ) v;

At one time I thought there was a claim that cross apply was guaranteed for row by row execution when it calls a UDF but this also failed:

create function FunctionCallingView(@pObjectId bigint)
returns table
as 
return select *
  from OptimizedForSingleObjectIdView 
 where ObjectId = @pObjectId;             

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
 cross apply FunctionCallingView(o.ObjectId) v

Adding option(force order) did not help--however there are two hash hints in the view already. Temporarily removing them did not help and slows down the single case.

Here is a snippet of the estimated plan for the function based slow case. The estimation of 1 row is correct. Far off to the right (not shown) is where there is a seek predicate that does not include that top 1 result. This seems similar to other cases we have where singular probed values from table seeks are not used as seek predicates elsewhere.

enter image description here

  • What happens if you use an OPTION(FAST 1) hint? – Erik Darling Jan 25 at 2:00
  • @ErikDarling no change afaict. – crokusek Jan 25 at 2:18
  • Geez, I asked the almost same question in 2012. Closing unless anyone has new info. Wrapping the view with a multi-statement UDF should work although it has a heavy syntax since the view needs to be defined. Of course that will prevent parallelism in 2012. – crokusek Jan 25 at 2:41
10

It is not possible to fully guarantee evaluating the view per row of the outer query, without using something that introduces a new T-SQL execution scope, for example a non-inline (multi-statement, BEGIN...END) table-valued function. This is pretty much the advice given in response to your previous question How to use merge hints to isolate complex queries in SQL Server.

At one time I thought there was a claim that cross apply was guaranteed for row by row execution when it calls a UDF

This does not apply to inline table-valued functions, since the definition is expanded into the calling query before optimization begins.


That said, there are some things you can do to strongly encourage the desired outcome.

The execution plan shows that the execution branch for the view part is ignoring the predicate on ObjectId whereas in the original case it uses them to perform index seeks.

You are expecting the ObjectId values to be evaluated "inside the view" using index seeks for each driving row. This is the correlated nested loops join (apply) style of execution. Note that using the APPLY T-SQL language element does not guarantee that physical execution will use the apply style.

It very much sounds as if SQL Server is choosing to execute with the ObjectId values tested at the Nested Loops Join operator instead. This is a non-correlated, or naive, nested loops join execution pattern.

Most likely, this is caused by the join hints you are using inside the view. Join hints are generally to be avoided, because they greatly restrict optimizer freedom, and not just for the physical type of join. In particular, join hints also force the order of joins for the whole query (just as if you had use a FORCE ORDER hint) and prevent several optimizations related to aggregation placement and strategy, and much else besides.

If you really must have join hints inside a view (something I would strongly suggest you avoid in general), you might find the most reliable way to get the plan shape you desire is:

  1. Create an inline (RETURNS TABLE) function using the view definition (not referencing the view).
  2. Provide @ObjectId as a parameter to the function.
  3. Placing a predicate using the parameter within the function body in a way that makes an index seek more likely.
  4. A FORCESEEK table hint inside the function may be used if truly every use should result in a seek.
  5. Call the new inline function using APPLY.

I generally dislike using specific syntaxes and hints in an attempt to force a certain physical plan shape. You may have more general success by parameterizing queries and guaranteeing the plan shape using a plan guide.

  • Got it working using (1,2,3) however placing the predicate at the end of the view definition inside the ITVF did not work with the cross apply although it works standalone calling the IVTF passing a literal ObjectId. With the prior hash hints removed it still fail in all cases. The saving grace is that the ITVF allows syntactic movement of the predicate up earlier in the chain. Wish there was a cross apply "rigid" hint. – crokusek Jan 25 at 18:59
  • Can a plan guide be used to shape only a specific portion of the plan allowing the remainder to evolve or does it lock the whole plan? My initial impression of using PGs over a few sprinkled hints is the (1) initial setup time, and (2) technical hurdle required for the next developer to maintain. It looks like changing the view down the road would re-generation of plan and analysis, etc? The abstract correlation between syntax and execution is nice right up until a hint is unavoidable. But then once the syntax is locked to the plan (after re-arranging) its an advantage going forward. – crokusek Jan 25 at 19:44
  • To complete the loop a view was created that joins all possible ObjectIds and cross applies them to the ITVF. Now this new view seems to be plug and play anywhere as a default join, no cross apply needed. – crokusek Jan 25 at 23:46
  • 1
    "syntactic movement of the predicate" is exactly my suggestion. I can't really comment on the other stuff because it depends on details only you can see at this point. If you want more detail, provide some more detail in your question and I will take another look. Plan guides apply to a whole statement. – Paul White Jan 26 at 9:45

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.