Edit

Share via

Troubleshooting: Long Running SQL Queries Involving FlowFields by Disabling SmartSQL

  • Article

If the performance of loading a page that contains FlowFields in Microsoft Dynamics NAV 2013 or later is bad, you might want to try isolating and testing FlowField queries separately. This article describes how to troubleshoot this scenario, and hopefully solve the problem.

What is SmartSQL

Introduced in Microsoft Dynamics NAV 2013, SmartSQL optimization does all calculations of FlowFields on a page by using a single SQL query. This reduces the number of network roundtrips from the server to the database.

How SmartSQL works

To understand how SmartSQL optimization works, consider this example from the CRONUS demonstration database. The database includes page 22 Customer List, which has the following FlowFields:

  • Balance (LCY)
  • Balance Due (LCY)
  • Sales (LCY)

Now, to see the SQL statement that is generated by the Dynamics NAV platform, start an SQL Server Profiler with the following events selected:

  • Stored procedures – SP:StmtCompleted
  • Performance – ShowplanXML

Then, run the page in the Dynamics NAV client.

Here is the resulting SQL query that is found in an SQL trace (modified to be more readable):

SELECT TOP (50)
       ISNULL("Customer"."timestamp",@0) AS "timestamp"
     , ISNULL("Customer"."No_",@1) AS "No_"
     , ISNULL("Customer"."Name",@2) AS "Name"
       /* many more columns from the table dbo."MSFT$Customer" */
     , ISNULL("Customer"."OIOUBL Profile Code Required",@6) AS "OIOUBL Profile Code Required"
       -- SIFT columns start here
     , ISNULL("SUB$Balance (LCY)"."Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)",@3) AS "Balance (LCY)"
     , ISNULL("SUB$Balance Due (LCY)"."Balance Due (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)",@3) AS "Balance Due (LCY)"
     , ISNULL("SUB$Sales (LCY)"."Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)",@3) AS "Sales (LCY)"
  FROM dbo."MSFT$Customer" AS "Customer"

 -- SIFT calculations start here
OUTER APPLY (
SELECT TOP (1)
       ISNULL(SUM("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."SUM$Amount (LCY)"),@3) AS "Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)"
FROM dbo."MSFT$Detailed Cust_ Ledg_ Entry$VSIFT$5" AS "Balance (LCY)$Detailed Cust_ Ledg_ Entry"
WHERE ("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."Customer No_"="Customer"."No_"
AND ISNULL("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."Posting Date",@8)>=@12
AND ISNULL("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."Posting Date",@8)<=@13)
) AS "SUB$Balance (LCY)"  

OUTER APPLY (
SELECT TOP (1)
       ISNULL(SUM("Balance Due (LCY)$Detailed Cust_ Ledg_ Entry"."SUM$Amount (LCY)"),@3) AS "Balance Due (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)"
FROM dbo."MSFT$Detailed Cust_ Ledg_ Entry$VSIFT$5" AS "Balance Due (LCY)$Detailed Cust_ Ledg_ Entry"
WHERE ("Balance Due (LCY)$Detailed Cust_ Ledg_ Entry"."Customer No_"="Customer"."No_"
AND ISNULL("Balance Due (LCY)$Detailed Cust_ Ledg_ Entry"."Posting Date",@8)<=@14
AND ISNULL("Balance Due (LCY)$Detailed Cust_ Ledg_ Entry"."Initial Entry Due Date",@8)>=@15
AND ISNULL("Balance Due (LCY)$Detailed Cust_ Ledg_ Entry"."Initial Entry Due Date",@8)<=@16)
) AS "SUB$Balance Due (LCY)"  

OUTER APPLY (
SELECT TOP (1)
       ISNULL(SUM("Sales (LCY)$Cust_ Ledger Entry"."SUM$Sales (LCY)"),@3) AS "Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)"
FROM dbo."MSFT$Cust_ Ledger Entry$VSIFT$1" AS "Sales (LCY)$Cust_ Ledger Entry"
WHERE ("Sales (LCY)$Cust_ Ledger Entry"."Customer No_"="Customer"."No_"
AND ISNULL("Sales (LCY)$Cust_ Ledger Entry"."Posting Date",@8)>=@17
AND ISNULL("Sales (LCY)$Cust_ Ledger Entry"."Posting Date",@8)<=@18)
) AS "SUB$Sales (LCY)"  

 WHERE (ISNULL("Customer"."No_",@1)>@19)
 ORDER BY "No_" ASC

In the SQL query, each section that starts with the keyword OUTER APPLY is responsible for a FlowField calculation, and the SmartSQL optimization adds this to the SQL query as an outer join. For each FlowField in the table, an OUTER APPLY clause is added to the SQL statement.

How to isolate and test FlowField queries

If you want to see the cost of each of these subqueries, you can disable the SmartSQL optimization. To do this, run the Business Central Server Administration tool, and select the Disable SmartSQL check box in the Database section.

Now, when loading the page, each FlowField calculation is performed as a separate SQL statement, which can be found either in an SQL trace or by using the Query Store feature in SQL Server 2016 (or later).

Once the slow subquery has been identified, you can create an index to improve its performance (or enable the MaintainSIFTIndex property of the key, if it is disabled).

As you can see, poor performance might not be caused by the SmartSQL optimization. However, the fact that SmartSQL queries are not cached will only amplify the issue. It won't help to customize the page or change the visibility of the field either. If a FlowField is contained in the metadata of the page, it will be calculated.

Business Central Server Administration Tool
SQL Server Profiler
Troubleshooting: Using Query Store to Monitor Query Performance in Business Central
SQL Trace