XPO – 11.1 Sneak Peek – Support for the OUTER APPLY operator in SQL queries

22 March 2011

We have some good news for our XPO users. In v2011 vol1 we have improved our sql-generator by supporting the OUTER APPLY operator. This SQL operator enables you to create an aggregate’s sub queries outside the WHERE clause and the SELECT expressions. For instance, it provides the capability to perform nested aggregates and aggregates in the group by list of the GROUP BY clause.

 

How this support is useful for you? It is useful because Microsoft SQL Server and SQL Server Compact are limited  in performing aggregates in these scenarios. For instance, the following queries will throw an exception without the OUTER APPLY operator:

 

1) Nested aggregate:

 

select N0.Oid, (select max(N1.Date) from [Orders] N1

where N1.EmployeeId = N0.EmployeeId

AND (select count(*) from [Orders] N2 where N2.CustomerId = N1.CustomerId) <= 2)

from [Employees] N0

 

2) Aggregate in the group by list of a GROUP BY clause:

 

select (select count(*) as Res from "dbo"."FreeClass" N2

where (N0."Data" = N2."Data"))

from "dbo"."FreeClassSub" N0

group by (select count(*) as Res from "dbo"."FreeClass" N3

where (N0."Data" = N3."Data"))

 

However, if we modify the above queries to use the OUTER APPLY operator, they will perform correctly:

 

1) Nested aggregate:

 

select N0.Oid, OA0.Res

from [Employees] N0

outer apply

(select max(N1.Date) as Res from [Orders] N1

outer apply

(select count(*) as Res from [Orders] N2 where N2.CustomerId = N1.CustomerId) OA1

where N1.EmployeeId = N0.EmployeeId AND OA1.Res <= 2)) OA0

 

 

2) Aggregate in the group by list of a GROUP BY clause:

 

select OA0.Res from "dbo"."FreeClassSub" N0

outer apply

(select count(*) as Res from "dbo"."FreeClass" N3 where (N0."Data" = N3."Data")) OA0

group by OA0.Res

 

The OUTER APPLY operator is supported by Microsoft SQL Server and SQL Server Compact. So starting from version 11.1, criteria and group properties (like “[Orders][ [Customers][City = ‘London’].Count() > 10 ].Min(ShippedDate)”) can be performed on those DBMS without any problems. BTW, I recently saw one more practical example from a customer, who incurred the limit of the queries described above. I will post it here, just in case you have similar structures in your projects. So, here are his business classes:

 

public class ParentObject : XPObject {
    ...
    [
Aggregated]

    [Association("Parent-Children")]

    public XPCollection<ChildObject> ChildObjects { ... }

    [PersistentAlias("ChildObjects[].Max(LastChange)")]

    public DateTime LastChangeOfChildObjects {

        get { return (DateTime)EvaluateAlias("LastChangeOfChildObjects"); }

    }

    ...

}

public class ChildObject : XPObject {
    ...

    [Association("Parent-Children")]

    public ParentObject Parent { ... }

    public DateTime LastChange { ... }

    ...

}


The ParentObject was bound to a grid control working in the server mode. If you try to group by the LastChangeOfChildObjects column (take special note of the expression in its PersistentAlias attribute) in 10.2, it will not work, because of the described limitation. There are of course many other examples.

 

Finally, I should note that support for the OUTER APPLY operator is an internal improvement, and that means that XPO users will not require any additional actions to turn it on.

4 comment(s)
Sigurd Decroos

Outer Apply and Cross Apply are both very powerfull functions in SQL Server. It can be used for a lot more than shown here. I suggest people to look them up if you don't know these yet.

23 March, 2011
Slava D (DevExpress)

@Sigurd Decroos:

Yes, these operators are very powerful, but their use leads to some performance problems. In some cases, the query execution time grows up to 10 times vs simple subqueries. Therefore, XPO includes them in a query only in case when their use is necessary.

24 March, 2011
Sigurd Decroos

In a particular case, I gained 1200x speed improvement for a sproc. Now that's huge!

As with all queries, they have to be tested before released. Too many times I found myself changing parts of queries to gain speed. I can't keep adding indexes just for the fun of it :).

24 March, 2011
Gregor Stamac

Will this help with the issue I described in Q290491?

26 March, 2011

Please login or register to post comments.