Slow SQL Update from ADO .NET

bbrown

This is NAV 2016

I am using ADO .NET to interact with an external SQL database on a different server. The connection is done thru a Linked Server. Everything is working fine so far except for 1 update statement. The statement is updating a small set of records (~4) in a fairly large table (~1 million). The table contains a non-clustered index to support the where clause of the update statement.

When executed via T-SQL (using linked server) the statement performs as expected and updates basically instantly. However run via .NET thru NAV the statement takes almost a full minute to complete. It was initially failing but I was able to find this my increasing the command timeout.

The statement is something like this. There is an index for [Field to filter]

UPDATE [Remote Server].[DBNAMe].[dbo].
SET [Field to Update] = 'New'
WHERE [Field to filter] = 44287

Any thoughts/ideas on what might be causing this performance issue?

bbrown

Correction: I am using the SQL client (System.Data.SqlClient)

kylehardin

What does your connection string look like in your NAV code?

It might be a good idea to step through your code with the debugger to confirm that it is the actual query that is slow, and not something else like the initial connection to the server, or preparing the SQLCommand, that sort of thing.

bbrown

Thanks for the reply.

I have stepped thru the debugger and it is definitely the query where is slows down.

The same connection string is being used to execute dozens of different queries. Including both select and update statements. So my thinking is that's not the issue.

Anyways here is the connection string. In case you see something I don't. The connection is to the NAV database. The query is run in that context to update the remote DB via linked server. When testing with T-SQL it was done in the same manner.

'Persist Security Info=False;Integrated Security=true;Initial Catalog=Development;server=CWNAVSQL1;Connection Timeout=60'


I am using "ExecuteNonQuery" here. But also on several other updates which seem to run fine.

kylehardin

The reason I asked about the connection string was to make sure you are making a direct connection to SQL and not going through some other type connection like ODBC. You are going direct, so that's not the problem.

The linked server is the next suspect. Is there a reason you have to involve a second SQL server instead of just going directly to where the data lives? Does your linked server use a View, which is then having to figure out how to map the Update query?

bbrown

If the linked server was the issue wouldn't the direct SQL statement present the same problem? That is also using the linked server.

The database being updated resides on a different server to which the NAV (NST) account does not have permissions. The Linked Server is configured with an account that does have permissions. Adding the NST account to the remote server is not an option.

The update is directly to a table. No views being used.

Also I have found a couple other updates that are a bit slow. But nothing as bad as this one. The others are maybe 2 or 3 seconds. While slow, I could live with them.

kylehardin

I don't have a good answer as to why a direct SQL statement is fast on the linked server but NAV is slow. And we are forced to live with the linked server.

These are other (not very fun) ideas I have.

Use some of the SQL performance tools to watch the transaction while it happens - profiler, or the Query Execution Plan on the linking (middle) SQL server. This assumes the problem is on the linking server, and not the far-end SQL server. Or you may have to look at the far-end server too.

Replicate your NAV code into a small Visual Studio C# project

I will go see if I have some sample NAV SQL.NET code I can share - maybe you can compare mine to yours and spot something that might help.

bbrown

Thanks. Any help you can offer is appreciated.

kylehardin

This is a stripped down version of something I have working on a production system. No linked server involved, but it does have working select and update commands.

OBJECT Codeunit 50020 SQL.NET Sample
{
OBJECT-PROPERTIES
{
Date=02/14/17;
Time=10:28:44;
Modified=Yes;
Version List=kyle;
}
PROPERTIES
{
OnRun=BEGIN
FridaysImport();
END;

}
CODE
{
VAR
SalesHeader@1240060013 : Record 36;
SalesLine@1240060014 : Record 37;
Customer@1240060019 : Record 18;
GLSetup@1240060022 : Record 98;
DimValue@1240060021 : Record 349;
Resource@1240060032 : Record 156;
PurchHeader@1240060039 : Record 38;
PurchLine@1240060040 : Record 39;
Vendor@1240060042 : Record 23;
SQLConnection@1240060000 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlConnection";
SQLCommand@1240060003 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommand";
SQLDataAdapter@1240060008 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlDataAdapter";
SQLCommandBuilder@1240060007 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommandBuilder";
SystemDataTable@1240060006 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataTable";
SystemDataRows@1240060005 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataRowCollection";
SystemDataRow@1240060004 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataRow";
ConnectionString@1240060001 : Text;
QueryString@1240060002 : Text;
QueryString2@1240060029 : Text;
RowError@1240060018 : Text[50];
TransID@1240060015 : Code[50];
TempCode@1240060038 : Code[20];
RowCount@1240060009 : Integer;
Text001@1240060011 : TextConst 'ENU="Data Source=%1;Initial Catalog=%2;Integrated Security=SSPI"';
RowCount2@1240060031 : Integer;
LineNo@1240060030 : Integer;
TempInt@1240060036 : Integer;
TempDateTime@1240060016 : DateTime;
TempDec@1240060017 : Decimal;
CreditMemo@1240060033 : Boolean;
HeaderOK@1240060037 : Boolean;
Text002@1240060010 : TextConst 'ENU=Customer %1 does not exist in NAV.';
Text003@1240060034 : TextConst 'ENU=Resource %1 does not exist in NAV.';
Text004@1240060035 : TextConst 'ENU=Address Commission %1 on base %2';
Text005@1240060041 : TextConst 'ENU=Vendor %1 does not exist for Commision %2.';
Text006@1240060043 : TextConst 'ENU=Broker Commission %1 on base %2';

LOCAL PROCEDURE FridaysImport@1240060004();
BEGIN
SQLConnect();

SQLProcessFridaysImport();

SQLClose();
END;

LOCAL PROCEDURE SQLConnect@1240060000();
BEGIN
ConnectionString := 'Data Source=sqlserver.network.local;Initial Catalog=Sample Database;Integrated Security=SSPI';
SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open();
END;

LOCAL PROCEDURE SQLProcessFridaysImport@1240060001();
BEGIN
QueryString := 'select [TransId],[TransType],[InvcNum],[NAVCustCode],[OrderDate],[ShipDate],[BatchId],[Vessel],';
QueryString := QueryString + '[Voyage],[Fixture],[InNav],[DateInNav],[NavComment],[NetDueDate],[InvcDate]';
QueryString := QueryString + ' from ' + 'My Table';
QueryString := QueryString + ' where [InNAV]=0 and [BatchReady]=1';

SQLCommand := SQLCommand.SqlCommand(QueryString, SQLConnection);
SQLCommand.CommandTimeout(0); // Wait timeout
SQLDataAdapter := SQLDataAdapter.SqlDataAdapter(SQLCommand);
SystemDataTable := SystemDataTable.DataTable;
SQLCommandBuilder := SQLCommandBuilder.SqlCommandBuilder(SQLDataAdapter);
SQLDataAdapter.Fill(SystemDataTable); // Executes query + fills DataTable
SystemDataRows := SystemDataTable.Rows;

RowCount := 0;
IF SystemDataRows.Count > 0 THEN BEGIN
WHILE RowCount < SystemDataRows.Count DO BEGIN
RowError := '';
SystemDataRow := SystemDataRows.Item(RowCount);
SQLFridaysHeader();
SQLUpdateHeaderRow();
RowCount += 1;
END;
END;
END;

LOCAL PROCEDURE SQLFridaysHeader@1240060002();
BEGIN
{
sample code
TransID := FORMAT(SystemDataRow.Item('TransId'));
IF NOT CreditMemo THEN BEGIN
IF SalesHeader.GET(SalesHeader."Document Type"::Invoice, FORMAT(SystemDataRow.Item('InvcNum'))) THEN
EXIT;
END ELSE BEGIN
IF SalesHeader.GET(SalesHeader."Document Type"::"Credit Memo", FORMAT(SystemDataRow.Item('InvcNum'))) THEN
EXIT;
END;
SalesHeader.INIT;
WITH SalesHeader DO BEGIN
IF NOT CreditMemo THEN
VALIDATE("Document Type", "Document Type"::Invoice)
ELSE
VALIDATE("Document Type", "Document Type"::"Credit Memo");
VALIDATE("No.", FORMAT(SystemDataRow.Item('InvcNum')));
EVALUATE(TempDateTime, FORMAT(SystemDataRow.Item('InvcDate')));
}
END;

LOCAL PROCEDURE SQLClose@1240060003();
BEGIN
IF NOT ISNULL(SystemDataRows) THEN SystemDataRows.Clear();
IF NOT ISNULL(SystemDataTable) THEN SystemDataTable.Clear();
IF NOT ISNULL(SQLCommandBuilder) THEN SQLCommandBuilder.Dispose();
IF NOT ISNULL(SQLDataAdapter) THEN SQLDataAdapter.Dispose();
IF NOT ISNULL(SQLCommand) THEN SQLCommand.Dispose();
IF NOT ISNULL(SQLConnection) THEN SQLConnection.Close();
IF NOT ISNULL(SQLConnection) THEN SQLConnection.Dispose();
END;

LOCAL PROCEDURE SQLUpdateHeaderRow@1240060008();
VAR
SQLCommand3@1240060000 : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommand";
BEGIN
QueryString := 'update ' + 'My Table';
QueryString := QueryString + ' set [InNav] = 1,';
QueryString := QueryString + '[DateInNav] = cast(''' + FORMAT(TODAY) + ''' as [datetime])';
IF RowError <> '' THEN
QueryString := QueryString + ',[NavComment] = ''' + RowError + '''';
QueryString := QueryString + ' where [TransId] = ''' + FORMAT(SystemDataRow.Item('TransId')) + '''';

SQLCommand3 := SQLCommand3.SqlCommand(QueryString, SQLConnection);
SQLCommand3.CommandTimeout(0);
SQLCommand3.ExecuteScalar();
END;

BEGIN
END.
}
}

bbrown

Thanks I will compare that to my code

kylehardin

One other idea: check cumulative updates. Looks like CU2 platform update addressed some dotnet performance issues.

bbrown

Current platform is CU9

I've asked the client to put a copy of the remote DB on the same SQL server as NAV. This will let me take the Linked Server out of the equation. At least I'll know if that is the issue or not.

bbrown

After some troubleshooting I've confirmed that the problem is the Linked Server with .NET. Using a direct SQL connection between the servers results in the updates takes only a few milliseconds.

Thanks for the help and ideas.