CROSS APPLY Problem - Multi-to-One Filtering

Question

I found a problem with how MS SQL Server handles CROSS APPLY .

In the database I'm working with, there is a pricing system with the following scheme:

Service → Price Model <- Price component ( '->' denotes a foreign key pointing to a table )

Some pricing models have "stepwise pricing", which means that when the quantity parameter reaches different thresholds, the price will increase (1-3 units - price A, 4-8 units - price B, etc.).

The problem I am facing is that the INNER JOIN between [Service] and [Price Component] on [Price Model ID] creates duplicate rows, since I actually do not use prices in the price component, just another field in the table, which is the same for each of the lines [Price Component].

 SELECT * FROM [Service] s INNER JOIN [Price Component] pc ON s.[Price Model Id] = pc.[Price Model Id] 

The logical solution for this problem is to replace INNER JOIN with CROSS APPLY , which does this:

 SELECT * FROM [Service] s CROSS APPLY (SELECT TOP 1 * FROM [Price Component] pc WHERE s.[Price Model Id] = pc.[Price Model Id] ) AS pc 

The problem is that efficiency is completely destroyed in some other associations that are apparently not related to this change. Considering the execution plan, the compound used to execute 2.3 cycles now takes 4.8 million cycles.

I tried adding DISTINCT to the original query (since it does not use unique data from the [Price Component] table, and this is a solution to the function, except that it quadruples the execution time. Returning only the value that I need from the table [ Price Component], but it doesn't seem to help much:

 SELECT * FROM [Service] s CROSS APPLY (SELECT DISTINCT pc.moneyUnitId FROM [Price Component] pc WHERE s.[Price Model Id] = pc.[Price Model Id] ) AS pc 

Oddly enough, changing CROSS APPLY to OUTER APPLY eliminates problems with other unions, but the goal of CROSS APPLY (which, as I understand it, basically represents the difference between INNER JOIN and OUTER JOIN ) INNER JOIN .

Does anyone have any thoughts or ideas on what could cause a crazy increase in complexity with CROSS APPLY ?

UPDATE

So, after reading a few more about how to interpret the execution plan, I found out the following:

• The original query (using INNER JOIN s) is a long series of nested loops that starts with any filter data that you give them. Quite fast response time while the filter is included in the indexed fields.

• The modified query (using CROSS APPLY ) is a longer series of Hash Matches and joins every table that you give it, except those filters, and then applies the filters last. Always slower than death.

• A working modified query (with OUTER APPLY ) does the same as the original, but does not exclude results that do not match WHERE. As fast as the original.

So the problem is this: why does CROSS APPLY make a plan change to join all the tables before the requested filter?

Answer 1

Why not:

 select t1.colA, t3.colX from table1 t1 inner join (select distinct t2.t1FK, t2.colX from table2 t2) t3 on t1.ID = t3.t1FK 

Answer 2

IF execution plans are not identical between JOIN and CROSS APPLY , then the query optimizer uses the logical operator Nested Loops, which in some cases can adversely affect performance. See here for basic information about nested loops: http://msdn.microsoft.com/en-us/library/ms191318(v=sql.90).aspx

Answer 3

If you use only one field from a one-to-many table that you counted with group by and max () to filter records?

 select a.field1, a.field2, max(b.field3) from table1 a join table2 b on a.someid = b.someid group by a.field1, a.field2