How to get cross apply to operate row by row on a view?

7 votes
1 answer
2102 views
sql-server execution-plan view cross-apply
                          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.




                        
Asked by crokusek (2110 rep)
Jan 25, 2019, 01:15 AM
Last activity: Jan 26, 2019, 10:12 AM
How to get cross apply to operate row by row on a view?

Related Questions
