Better or worse with cross / outer apply

GokhanVarol 2013-07-04 20:43:43

alt textUsing a cross apply top under the driver pushes compute scalar to serial zone, vs using outer apply and filter outside on a key not null require a parallel filter outside the outer apply.
Which one has less overhead?

alt text

SQLkiwi 2013-07-04 23:56:29
Neither feature is particularly desirable, though which is worse depends on the circumstances. If the Compute Scalar performs extensive computations on a large number of rows, it will be quite inefficient to run on a single thread. The optimizer does not cost scalar operators to any depth, which explains the reason it appears outside a parallel zone. How much damage the Filter does again depends on row count, and particularly on how much effort is spent producing and transporting rows that will eventually not be needed.

In this particular example, the serial Compute Scalar appears not to be too bad. It is computing a single simple concatenation. Nevertheless, you could try rewriting the code to perform an OUTER APPLY after the CROSS APPLY, where the extra APPLY just performs the concatenation. This is usually enough to convince the optimizer to run the calculation in a parallel zone. Not guaranteed, of course.

GokhanVarol 2013-07-05 00:14:32
I did not understand how to implement an OUTER APPLY after the CROSS APPLY
I was running a test also with the cross apply(only outer or only cross) the test finished, duration little different, not sure if there is a looser or winner for this workload.

link text

SQLkiwi 2013-07-05 00:21:34
Output only the raw columns used in the calculation from the CROSS APPLY and add an OUTER APPLY (SELECT x + ' ' + Y).
GokhanVarol 2013-07-05 00:32:02
They seem to be same or not make a considerable difference, regarding compute or filter out of the apply for this use case. I like Cross apply method, it looks more descriptive. Applying an outer apply and filtering out not nulls is kind of ugly/dirty for whom that would look into the sql to understand.
Thank you