Update a field in a table is very slow using OdbcCommand

Alen Cappelletti 951 Reputation points
2023-10-10T11:07:08.1766667+00:00

Hi all,

I got a little C# consolle app that is very slow updating a SQL SERVER 2017 table. The process start quickly and after a while slow down. I use a OdbcCommand... could be this that slow down the action? The app write on a filesystem a file and after, row-by-row, update a table in sql server.

All this process is on application server, away from SQL SERVER machine... but on same network.

I've create a precise index with ("CODICE_PEC_ID" +"TIPO") including also the field "DATA_RICHIESTA_CONSERVAZIONE"

as you can see from the execution plan below... it's used.
The update could be also 10" long... I try to comment it and all speed up 'til 1000 write on filesystem by minute.

Table is 2 017 682 rows... but without any extreme TYPE... all INT o varchar(10) or datetime.

CREATE TABLE [dbo].[PEC](
	[CODICE_ID] [int] NOT NULL,
	[CODICE_PEC_ID] [int] NOT NULL,
	[CODICE_DOCUMENTO_ID] [int] NULL,
	[NUMERO_INVIO] [int] NULL,
	[TIPO] [varchar](1) NULL,
	[stream_id] [uniqueidentifier] NOT NULL,
	[DATA_RICHIESTA_CONSERVAZIONE] [datetime] NULL,
	[DATA_CONSERVAZIONE] [datetime] NULL,
	[STATO] [varchar](1) NOT NULL,
	[ORIGINE] [varchar](1) NOT NULL,
	[USER] [varchar](60) NOT NULL,
 CONSTRAINT [PK_PEC_RICEVUTE] PRIMARY KEY CLUSTERED 
(
	[CODICE_ID] ASC,
	[CODICE_PEC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) 

CREATE NONCLUSTERED INDEX [idx_CODICE_PEC_ID_TIPO] ON [dbo].[PEC]
(
	[CODICE_PEC_ID] ASC,
	[TIPO] ASC
)
INCLUDE([DATA_RICHIESTA_CONSERVAZIONE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Do you have some tips?

Thanks Alen

User's image

connectionTorDoc.Open();
string sql = @"UPDATE TO40_CORE_DOCUM.PEC_RICEVUTE SET DATA_RICHIESTA_CONSERVAZIONE = '" + DateTime.Today.Date.ToString("yyyy-MM-dd") + "' WHERE CODICE_PEC_ID = " + pec.Item1.ToString() + " and TIPO = '" + pec.Item2.ToString() + "'";
OdbcCommand update = new OdbcCommand(sql, connectionTorDoc);
update.CommandTimeout = 0;
update.ExecuteNonQuery();
connectionTorDoc.Close();
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,913 questions
{count} votes

Accepted answer
  1. Dan Guzman 9,211 Reputation points
    2023-10-10T12:47:15.1733333+00:00

    Use SqlClient instead of Odbc in .NET for SQL Server data access and use a parameterized query.

    You didn't show the file system code but be aware that building strings with string concatenation is very expensive due to garbage collection. Use a string builder or writer to build records for the text file.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,206 Reputation points
    2023-10-10T13:54:28.3566667+00:00

    @Alen Cappelletti ,

    Row-by-row operation will be slow "by design".

    Maybe you can switch to a bulk update.

    I see two possible options:

    1. You can create a stored procedure and pass bulk data as XML or JSON in one single shot.
    2. If you can copy input file to a file share that is visible to a SQL Server instance, you can even use BULK INSERT directly.
    0 comments No comments

  2. Erland Sommarskog 102.4K Reputation points
    2023-10-10T21:13:48.11+00:00

    Yet a solution is to use a table-variable parameter. Then you can stream the data, so that the client does have keep the entire file in memory. I have an article on my web site that shows how to do this: https://www.sommarskog.se/arrays-in-sql-2008.html.

    0 comments No comments

  3. Alen Cappelletti 951 Reputation points
    2023-10-11T07:40:36.9533333+00:00

    As @Dan Guzman said I changed the provider to SqlClient instead of Odbc in .NET all all speed up.

    Now it's ok.

    Thanks