Slow SQL Update from ADO .NET

bbrownbbrown Posts: 3,097Member
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?



There are no bugs - only undocumented features.

Comments

  • bbrownbbrown Posts: 3,097Member
    Correction: I am using the SQL client (System.Data.SqlClient)
    There are no bugs - only undocumented features.
  • kylehardinkylehardin Posts: 170Member
    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.
    Kyle Hardin - ArcherPoint
  • bbrownbbrown Posts: 3,097Member
    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.
    There are no bugs - only undocumented features.
  • kylehardinkylehardin Posts: 170Member
    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?
    Kyle Hardin - ArcherPoint
  • bbrownbbrown Posts: 3,097Member
    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.

    There are no bugs - only undocumented features.
  • kylehardinkylehardin Posts: 170Member
    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.
    Kyle Hardin - ArcherPoint
  • bbrownbbrown Posts: 3,097Member
    Thanks. Any help you can offer is appreciated.
    There are no bugs - only undocumented features.
  • kylehardinkylehardin Posts: 170Member
    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
    [email protected] : Record 36;
    [email protected] : Record 37;
    [email protected] : Record 18;
    [email protected] : Record 98;
    [email protected] : Record 349;
    [email protected] : Record 156;
    [email protected] : Record 38;
    [email protected] : Record 39;
    [email protected] : Record 23;
    [email protected] : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlConnection";
    SQLCommand[email protected] : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommand";
    [email protected] : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlDataAdapter";
    [email protected] : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommandBuilder";
    [email protected] : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataTable";
    [email protected] : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataRowCollection";
    [email protected] : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataRow";
    [email protected] : Text;
    [email protected] : Text;
    [email protected] : Text;
    [email protected] : Text[50];
    [email protected] : Code[50];
    [email protected] : Code[20];
    [email protected] : Integer;
    [email protected] : TextConst 'ENU="Data Source=%1;Initial Catalog=%2;Integrated Security=SSPI"';
    [email protected] : Integer;
    [email protected] : Integer;
    [email protected] : Integer;
    [email protected] : DateTime;
    [email protected] : Decimal;
    [email protected] : Boolean;
    [email protected] : Boolean;
    [email protected] : TextConst 'ENU=Customer %1 does not exist in NAV.';
    [email protected] : TextConst 'ENU=Resource %1 does not exist in NAV.';
    [email protected] : TextConst 'ENU=Address Commission %1 on base %2';
    [email protected] : TextConst 'ENU=Vendor %1 does not exist for Commision %2.';
    [email protected] : TextConst 'ENU=Broker Commission %1 on base %2';

    LOCAL PROCEDURE [email protected]();
    BEGIN
    SQLConnect();

    SQLProcessFridaysImport();

    SQLClose();
    END;

    LOCAL PROCEDURE [email protected]();
    BEGIN
    ConnectionString := 'Data Source=sqlserver.network.local;Initial Catalog=Sample Database;Integrated Security=SSPI';
    SQLConnection := SQLConnection.SqlConnection(ConnectionString);
    SQLConnection.Open();
    END;

    LOCAL PROCEDURE [email protected]();
    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 [email protected]();
    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 [email protected]();
    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 [email protected]();
    VAR
    [email protected] : 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.
    }
    }

    Kyle Hardin - ArcherPoint
  • bbrownbbrown Posts: 3,097Member
    Thanks I will compare that to my code
    There are no bugs - only undocumented features.
  • kylehardinkylehardin Posts: 170Member
    One other idea: check cumulative updates. Looks like CU2 platform update addressed some dotnet performance issues.
    Kyle Hardin - ArcherPoint
  • bbrownbbrown Posts: 3,097Member
    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.

    There are no bugs - only undocumented features.
  • bbrownbbrown Posts: 3,097Member
    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.

    There are no bugs - only undocumented features.
Sign In or Register to comment.