Count the Number of Records in NAV/Business Central

0 Shares

Vjeko, wrote some very good posts about the bad performance of COUNT function used in Microsoft Dynamics NAV. You can check them out at the end of this post.

COUNT function can be used in NAV to find out how many records with certain filters you have in the database.

Let’s take a simple example and check also what happens at SQL Server queries level when you use COUNT vs when you use a NAV Query:
–> For a customer, you want to calculate the number of invoices having a leftover amount of more than 50 EUR.

Option 1:

Apply filters to table Customer Ledger Entry and then use COUNT function:

CustLedgerEntry.SETRANGE("Customer No.",'K00000024');
CustLedgerEntry.SETRANGE(Open,TRUE);
CustLedgerEntry.SETRANGE(Positive,TRUE);
CustLedgerEntry.SETFILTER("Remaining Amt. (LCY)",'>%1',50);
MESSAGE(FORMAT(CustLedgerEntry.COUNT));

Testing with the SQL Server Profiler, the line “CustLedgerEntry.COUNT” produces this query:

SELECT TOP (50) ISNULL("Cust_ Ledger Entry"."timestamp", 0) AS "timestamp"
	,ISNULL("Cust_ Ledger Entry"."Entry No_", 0) AS "Entry No_"
	,ISNULL("Cust_ Ledger Entry"."Customer No_", 0) AS "Customer No_"
	,ISNULL("Cust_ Ledger Entry"."Posting Date", 0) AS "Posting Date"
	,ISNULL("Cust_ Ledger Entry"."Document Type", 0) AS "Document Type"
	,ISNULL("Cust_ Ledger Entry"."Document No_", 0) AS "Document No_"
	,ISNULL("Cust_ Ledger Entry"."Description", 0) AS "Description"
	,ISNULL("Cust_ Ledger Entry"."Currency Code", 0) AS "Currency Code"
	,ISNULL("Cust_ Ledger Entry"."Sales (LCY)", 0) AS "Sales (LCY)"
	,ISNULL("Cust_ Ledger Entry"."Profit (LCY)", 0) AS "Profit (LCY)"
	,ISNULL("Cust_ Ledger Entry"."Inv_ Discount (LCY)", 0) AS "Inv_ Discount (LCY)"
	,ISNULL("Cust_ Ledger Entry"."Sell-to Customer No_", 0) AS "Sell-to Customer No_"
	,ISNULL("Cust_ Ledger Entry"."Customer Posting Group", 0) AS "Customer Posting Group"
	,ISNULL("Cust_ Ledger Entry"."Global Dimension 1 Code", 0) AS "Global Dimension 1 Code"
	,ISNULL("Cust_ Ledger Entry"."Global Dimension 2 Code", 0) AS "Global Dimension 2 Code"
	..... etc. (all fields from the table)
	,ISNULL("Cust_ Ledger Entry"."Mandate Code", 0) AS "Mandate Code"
FROM "YOURDBNAME".dbo."YOURCOMPANY$Cust_ Ledger Entry" AS "Cust_ Ledger Entry" WITH (READUNCOMMITTED)
OUTER APPLY (
	SELECT TOP (1) ISNULL(SUM("Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry"."SUM$Amount (LCY)"), 0) AS "Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)"
	FROM "YOURDBNAME".dbo."YOURCOMPANY$Detailed Cust_ Ledg_ Entry$VSIFT$2" AS "Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry" WITH (
			READUNCOMMITTED
			,NOEXPAND
			)
	WHERE ("Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry"."Cust_ Ledger Entry No_" = "Cust_ Ledger Entry"."Entry No_")
	) AS "SUB$Remaining Amt_ (LCY)"
WHERE (
		"Cust_ Ledger Entry"."Customer No_" = 'K00000024'
		AND "Cust_ Ledger Entry"."Open" = 1
		AND "Cust_ Ledger Entry"."Positive" = 1
		)
	AND (ISNULL("SUB$Remaining Amt_ (LCY)"."Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)", 0) > 50)
ORDER BY "Entry No_" ASC
OPTION (
	OPTIMIZE FOR UNKNOWN
	,FAST 50
	)

–> As you can see this query does not retrieve from SQL database only the count of the records. Instead, it retrieves all the records, with all the fields and then I suppose the count is calculated at middle tier level. You can imagine that this is very inefficient as you don’t need all those records, but only a simple count.

–> More than this, in my case I have a proper non-clustered index and SQL server can use it to make an Index Seek, but because it retrieves all the fields, it needs to make also a Key LookUp operation to get the values that are not in the index. If you don’t have a proper index or depending on your data it can do a clustered index scan and go through all the records you have in the database. This can be very time consuming for tables like Customer Ledger Entry or G/L Entry for example.

Option 2:

Create a Query in NAV and use it in code in order to get the number of records:

CustLedgEntryRemain2.SETFILTER(Customer_No,'K00000024');
CustLedgEntryRemain2.Open;
WHILE CustLedgEntryRemain2.READ DO
  MESSAGE(FORMAT(CustLedgEntryRemain2.Count1));

The line “CustLedgEntryRemain2.Open” produces this query:

SELECT ISNULL(COUNT("Cust_Ledger_Entry"."Entry No_"), 0) AS "Count1"
FROM "YOURDBNAME".dbo."YOURCOMPANY$Cust_ Ledger Entry" AS "Cust_Ledger_Entry" WITH (READUNCOMMITTED)
OUTER APPLY (
	SELECT TOP (1) ISNULL(SUM("Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry"."SUM$Amount (LCY)"), 0) AS "Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)"
	FROM "YOURDBNAME".dbo."YOURCOMPANY$Detailed Cust_ Ledg_ Entry$VSIFT$2" AS "Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry" WITH (
			READUNCOMMITTED
			,NOEXPAND
			)
	WHERE ("Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry"."Cust_ Ledger Entry No_" = "Cust_Ledger_Entry"."Entry No_")
	) AS "SUB$Cust_Ledger_Entry$Remaining Amt_ (LCY)"
WHERE (
		ISNULL("Cust_Ledger_Entry"."Open", 0) = 1
		AND ISNULL("Cust_Ledger_Entry"."Positive", 0) = 1
		AND ISNULL("SUB$Cust_Ledger_Entry$Remaining Amt_ (LCY)"."Remaining Amt_ (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)", 0) > 50
		AND ISNULL("Cust_Ledger_Entry"."Customer No_", 0) = 'K00000024'
		)
OPTION (
	OPTIMIZE FOR UNKNOWN
	,FAST 50
	,FORCE ORDER
	,LOOP JOIN
	)

–> Now, as you can see, we retrieve only one value from SQL Server (the number of records), instead of all the records with all fields.
–> It uses the same non-Clustered index, but because we don’t retrieve all fields, it does not need to make also a Key LookUp operation:

So, COUNT can be really slow and to my opinion, you should avoid using it if you don’t have a SIFT Index and you are working with a big table. In those cases, when you don’t need the exact number of records use ISEMPTY or the trick: IF SomeTable.FIND(‘-‘) AND (SomeTable.NEXT = 0) THEN, depending on situation. If you do need the exact number of records you can use a NAV Query which performs much faster than count.