Ufdjrw

I'm writing an application that queries the database of a third party application.

My app creates a "library" of SQL views in the third party DB so my later queries are much easier to write and read. (Not only because of the domain logic of my app, but also because the third party DB uses terrible names for tables and columns.)

I've noticed that one of my views (which joins other views, which in turn join other views...) shows unusual slowness on a client's system. I broke it down to smaller parts but couldn't figure out any clear suspect.

Following is a minified version of the view, with other views it normally references turned into CTEs, which is still exactly as slow as the original view. If I break it into any smaller pieces though, they each execute very fast. I've also added a few comments showing examples of small changes that make the query much faster.

-- The query takes about 5s when the server has no other load
-- That's too slow because the UI of the app needs the results
with
orderLines as (
 select r.DocEntry as rdrDocId,
 r1.LineNum as rdrLineId
 from rdr1 r1
 join ordr r on r.DocEntry = r1.DocEntry
 -- If I filter only by LineStatus or only by DocStatus here, query takes <1s
 where r1.LineStatus = 'O' and r.DocStatus = 'O'
),
picklistDetails as (
 select U_KommNr as pklDocId,
 max(cast(U_Space as int)) as maxPlace
 from [@PICKING]
 where U_DeletedF = 'N'
 group by U_KommNr
),
picklistDocs as (
 select p.AbsEntry as pklDocId,
 case
 when pd.maxPlace is null then 0
 else pd.maxPlace
 end as pklDocMaxPlace
 from opkl p
 left join picklistDetails pd on pd.pklDocId = p.AbsEntry
),
picklistDocLines as (
 select AbsEntry as pklDocId,
 PickEntry as pklLineId,
 OrderEntry as rdrDocId,
 OrderLine as rdrLineId
 from PKL1
)
select p.pklDocMaxPlace
from picklistDocs p
join picklistDocLines p1 on p.pklDocId = p1.pklDocId
join orderLines r1 on r1.rdrDocId = p1.rdrDocId
 and r1.rdrLineId = p1.rdrLineId
-- If I force parallelism by using the following option, query takes <1s
--option(querytraceon 8649)

In addition to the fact that all parts of the query execute quite fast in isolation, I also get much faster execution time (again <1s in total) when I use #temp tables instead of CTEs, like the following:

-- This batch execution returns the same result but takes <1s

select r.DocEntry as rdrDocId,
 r1.LineNum as rdrLineId
into #orderLines
from rdr1 r1
join ordr r on r.DocEntry = r1.DocEntry
where r1.LineStatus = 'O' and r.DocStatus = 'O'

select U_KommNr as pklDocId,
 max(cast(U_Space as int)) as maxPlace
into #picklistDetails
from [@PICKING]
where U_DeletedF = 'N'
group by U_KommNr

select p.AbsEntry as pklDocId,
 case
 when pd.maxPlace is null then 0
 else pd.maxPlace
 end as pklDocMaxPlace
into #picklistDocs
from opkl p
left join #picklistDetails pd on pd.pklDocId = p.AbsEntry

select AbsEntry as pklDocId,
 PickEntry as pklLineId,
 OrderEntry as rdrDocId,
 OrderLine as rdrLineId
into #picklistDocLines
from PKL1

select p.pklDocMaxPlace
from #picklistDocs p
join #picklistDocLines p1 on p.pklDocId = p1.pklDocId
join #orderLines r1 on r1.rdrDocId = p1.rdrDocId
 and r1.rdrLineId = p1.rdrLineId

Can anyone make sense of the behavior of SQL Server here? To me it seems kind of like a bug / failure of the query optimizer.

If I can't find a way to make the view as fast as it should be, I'll probably just turn it into a procedure that uses #temp tables like in the second code I pasted, but optimally I'd like to avoid that. I have dozens of views with similar complexity and none are so slow.

Can anyone make sense of the behavior of SQL Server here? To me it
seems kind of like a bug/failure of the query optimizer.

No, this is not a bug.

The task split to few smaller units:

Temporary table approach is nothing more than splitting that large query plan into smaller pieces and executing them independently.

The smaller the piece, the bigger the chance that SQL Server Query Optimizer will not perform some dramatical mismatch in cardinality estimation and will choose right physical operators and types of joins, so smaller chance to see a nested loop over millions of rows or some other nasty thing.

When there is a time run the piece of code stated below, the Query optimizer knows how much rows in every involved temporary table and how they are distributed:

select p.pklDocMaxPlace
from #picklistDocs p
join #picklistDocLines p1 on p.pklDocId = p1.pklDocId
join #orderLines r1 on r1.rdrDocId = p1.rdrDocId
 and r1.rdrLineId = p1.rdrLineId

One unit of work:

The CTE approach, as mentioned by Lukasz and Robert in comments, is a kind of syntax sugar, similar to view on view on view. However, in the end, query optimizer has to flatten all CTEs into one consolidated and sometimes large query plan and execute it as one unit. Therefore, the larger plan the bigger chance of performance related surprises.

So, in contrast to a previous snippet, the query optimizer compiles plan at the moment when the number of rows is just guessed by a cardinality estimation using statistics:

select p.pklDocMaxPlace
from picklistDocs p
join picklistDocLines p1 on p.pklDocId = p1.pklDocId
join orderLines r1 on r1.rdrDocId = p1.rdrDocId
 and r1.rdrLineId = p1.rdrLineId

querytraceon 8649:

When you enable option(querytraceon 8649) you just force the query optimizer to change behavior, the same way as other query hints or like traces similar to 4199.
So forced parallelism, perhaps occasionally generated a better plan, but you can hardly rely on this.

Some ideas of how it can be solved:

• Statistics update on involved tables


• Playing with the switching of new and legacy cardinality estimators


• (imho) Rewrite CTE to a derived table?


• If large datasets involved, then splitting logic into smaller pieces using #temp table approach is something that can be a consistent workaround to choose.


• etc etc

There is one exception:

• Indexed views. By using hint NOEXPAND (or if Enterprise Edition in use). The logic of the view should not be flattened into the overall query plan of the query that involves it.