During my airlift presentation I have shown a SQL-Statement which was captured using sql profiler during runtime of a query-object.

This query brings the same results as TOP10-Customer-Report, but in shorter time.

Here is what the statement behind the query looks like.

SELECT TOP (10) ISNULL(“Customer”.”No_”,@0) AS “No”,ISNULL(“Customer”.”Name”,@1) AS “Name”,ISNULL(SUM(“SUB$Customer$Sales (LCY)”.”Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)”),@3) AS “Sum_Sales_LCY”,ISNULL(SUM(“SUB$Customer$Balance (LCY)”.”Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)”),@5) AS “Sum_Balance_LCY” FROM “Demo Database NAV (7-0)”.”dbo”.”CRONUS AG$Customer” AS “Customer” WITH(READUNCOMMITTED)

OUTER APPLY (SELECT TOP (1) ISNULL(SUM(“Sales (LCY)$Cust_ Ledger Entry”.”Sales (LCY)”),@2) AS “Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)” FROM “Demo Database NAV (7-0)”.”dbo”.”CRONUS AG$Cust_ Ledger Entry” AS “Sales (LCY)$Cust_ Ledger Entry” WITH(READUNCOMMITTED)  WHERE (“Sales (LCY)$Cust_ Ledger Entry”.”Customer No_”=”Customer”.”No_”)) AS “SUB$Customer$Sales (LCY)”

OUTER APPLY (SELECT TOP (1) ISNULL(SUM(“Balance (LCY)$Detailed Cust_ Ledg_ Entry”.”Amount (LCY)”),@4) AS “Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)” FROM “Demo Database NAV (7-0)”.”dbo”.”CRONUS AG$Detailed Cust_ Ledg_ Entry” AS “Balance (LCY)$Detailed Cust_ Ledg_ Entry” WITH(READUNCOMMITTED)  WHERE (“Balance (LCY)$Detailed Cust_ Ledg_ Entry”.”Customer No_”=”Customer”.”No_”)) AS “SUB$Customer$Balance (LCY)”

ORDER BY “Sales_LCY” DESC,”No” ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50, FORCE ORDER, LOOP JOIN)

This example shows how the query-object increases performance by the usage of TOP, nested statements and SUM.