BigText.ADDTEXT loop is slow in RTC

I had a code where I was combining several XML documents into one and used a BigText variable for the job.  At design time everything looked fine but when I executed the code in the Role Tailored Client everything was slow.  I sent this issue to Microsoft and the product team has responded.

It is true that AddText used in a loop is slower in RTC than in CC. The reason is the underlying .Net string type. As mentioned in the immutability section in: http://msdn.microsoft.com/en-us/library/system.string.aspx#Immutability the creation of many immutable strings can be very time consuming. Since the backing field in NAV is a string we have the same problem – and it would be vastly inefficient in all other scenarios to create a StringBuilder for each BigText variable – and you only save instantiating strings if you do more than one concatenation.

This leaves us the following options:
–          If partner is adding texts – consider using .Net Interop and use the StringBuilder class – and document the changed behavior of BigText
–          Refactor code to avoid hundreds / thousands of small additions (e.g. by building the total big text in chunks of e.g. 1000 – but will have to look at perf of that)
–          Create a new feature for a StringBuilder class in A/L (would possibly have to work for the other text types as well?)

  • I doubt that this would be prioritized in Sicily

I know that this is an unwanted side effect of using .Net strings – but that is also what buys us Unicode etc., so I would prefer that we document this new behavior.

So I rewrote this bit and am now using OutStream instead.  Just wanted you to know.

Code fix for Table 36 – Sales Header

Just sent Microsoft a suggestion on Microsoft Connect.

We where trying to import data into the Sales Header table and stopped on an error. The old code is

[code]
Payment Terms Code – OnValidate()
IF ("Payment Terms Code" <> ”) AND ("Document Date" <> 0D) THEN BEGIN
PaymentTerms.GET("Payment Terms Code");
IF (("Document Type" IN ["Document Type"::"Return Order","Document Type"::"Credit Memo"]) AND
NOT PaymentTerms."Calc. Pmt. Disc. on Cr. Memos")
THEN BEGIN
VALIDATE("Due Date","Document Date");
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END ELSE BEGIN
"Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
"Pmt. Discount Date" := CALCDATE(PaymentTerms."Discount Date Calculation","Document Date");
IF NOT UpdateDocumentDate THEN
VALIDATE("Payment Discount %",PaymentTerms."Discount %")
END;
END ELSE BEGIN
VALIDATE("Due Date","Document Date");
IF NOT UpdateDocumentDate THEN BEGIN
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END;
END;
IF xRec."Payment Terms Code" = "Prepmt. Payment Terms Code" THEN BEGIN
IF xRec."Prepayment Due Date" = 0D THEN
"Prepayment Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
VALIDATE("Prepmt. Payment Terms Code","Payment Terms Code");
END;[/code]

and I suggeste a change to the fourth last line

[code]
Payment Terms Code – OnValidate()
IF ("Payment Terms Code" <> ”) AND ("Document Date" <> 0D) THEN BEGIN
PaymentTerms.GET("Payment Terms Code");
IF (("Document Type" IN ["Document Type"::"Return Order","Document Type"::"Credit Memo"]) AND
NOT PaymentTerms."Calc. Pmt. Disc. on Cr. Memos")
THEN BEGIN
VALIDATE("Due Date","Document Date");
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END ELSE BEGIN
"Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
"Pmt. Discount Date" := CALCDATE(PaymentTerms."Discount Date Calculation","Document Date");
IF NOT UpdateDocumentDate THEN
VALIDATE("Payment Discount %",PaymentTerms."Discount %")
END;
END ELSE BEGIN
VALIDATE("Due Date","Document Date");
IF NOT UpdateDocumentDate THEN BEGIN
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END;
END;
IF (xRec."Payment Terms Code" = "Prepmt. Payment Terms Code") AND (xRec."Payment Terms Code" <> ”) THEN BEGIN
IF xRec."Prepayment Due Date" = 0D THEN
"Prepayment Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
VALIDATE("Prepmt. Payment Terms Code","Payment Terms Code");
END;[/code]

SQL Native Client Version

I wrote a solution to log blocking in NAV with the help of Microsoft SQL Native Client.  I wanted to be able to use this solution for a client but then ran into a problem with the SQL Native Client.  There are three versions out there and the connection string must include the version installed on the client machine.

The first step is to find the “Program Files” folder
[code] LOCAL PROCEDURE GetProgramFilesPath@1200050010() ProgramFilesPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
ProgramFilesPath := SystemEnviroment.Item(‘ProgramW6432’);
IF ProgramFilesPath = ” THEN
ProgramFilesPath := SystemEnviroment.Item(‘ProgramFiles’);
END;[/code]and the System32 folder[code] LOCAL PROCEDURE GetSystemRootPath@1100408000() SystemRootPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
SystemRootPath := SystemEnviroment.Item(‘SystemRoot’) + ‘\System32’;
END;[/code]

Then I use the EXISTS function to check for the client version.

[code] LOCAL PROCEDURE OpenConnection@1200050006(ConnectAsUserID@1200050003 : Text[30];ConnactAsPassword@1200050002 : Text[30]);
VAR
MyServer@1200050001 : Record 2000000047;
MyDatabase@1200050000 : Record 2000000048;
ClientVersion@1100408000 : Text[30];
ProgramFilesPath@1100408001 : Text[50];
SystemRootPath@1100408002 : Text[50];
BEGIN
IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ISCLEAR(ADORecordset) THEN
CREATE(ADORecordset);

IF ISCLEAR(ADOStream) THEN
CREATE(ADOStream);

IF ADOConnection.State = 1 THEN
EXIT;

IF ISSERVICETIER THEN BEGIN

IF ISCLEAR(DomDoc) THEN
CREATE(DomDoc);

DomDoc.load(APPLICATIONPATH + ‘CustomSettings.config’);
DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseServer”]’);
MyServerName := DomNode.attributes.item(1).text;

DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseName”]’);
MyDatabaseName := DomNode.attributes.item(1).text;

END ELSE BEGIN
MyServer.SETRANGE("My Server",TRUE);
MyServer.FINDFIRST;
MyServerName := MyServer."Server Name";

MyDatabase.SETRANGE("My Database",TRUE);
MyDatabase.FINDFIRST;
MyDatabaseName := MyDatabase."Database Name";
END;

ProgramFilesPath := GetProgramFilesPath;

CASE TRUE OF
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\90\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\100\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\110\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI11’;
ELSE
BEGIN
SystemRootPath := GetSystemRootPath;
CASE TRUE OF
EXISTS(SystemRootPath + ‘\sqlncli.dll’):
ClientVersion := ‘SQLNCLI’;
EXISTS(SystemRootPath + ‘\sqlncli10.dll’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(SystemRootPath + ‘\sqlncli11.dll’):
ClientVersion := ‘SQLNCLI11’;
ELSE
ERROR(Text002);
END;
END;
END;

IF ConnectAsUserID <> ” THEN
ADOConnection.Open(
STRSUBSTNO(‘Provider=%5;Server=%1;Database=%2;Uid=%3;Pwd=%4;’,
MyServerName,MyDatabaseName,ConnectAsUserID,ConnactAsPassword,ClientVersion))
ELSE
ADOConnection.Open(
STRSUBSTNO(‘Provider=%3;Server=%1;Database=%2;Trusted_Connection=yes;’,
MyServerName,MyDatabaseName,ClientVersion));

ADOConnection.CommandTimeout(0);
END;[/code]

where Text002@1100408002 : TextConst ‘ENU=Microsoft SQL Native Client not found;ISL=Microsoft SQL Native Client finnst ekki.’;

No execute permission on CodeUnit with ID 8700

I am using web services in a company that does not have the license to use the Mobile Document Dispatcher.  In Codeunit 1 and trigger CompanyClose there are functions to stop listeners.

[code]

CompanyClose()
EmployeePortalApplSrv.StopQueue;
MobDocDispatcher.Stop;
BizTalkApplnSrvStartup.StopTcpListen;
IF GUIALLOWED THEN
LogInEnd;
[/code]

This is causing problems in the web service so I changed the code to

[code]
IF NOT ISSERVICETIER THEN BEGIN
EmployeePortalApplSrv.StopQueue;
MobDocDispatcher.Stop;
BizTalkApplnSrvStartup.StopTcpListen;
END;
IF GUIALLOWED THEN
LogInEnd;[/code]

and the error is history.