Update a field in a table is very slow using OdbcCommand

Question

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

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();

Answer 1

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.

Answer 2

@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.

Answer 3

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.

Answer 4

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

Now it's ok.

Thanks