# Insertion of big TXT file into Sql Server

The code posted below reads 500000 lines from a txt file and imports the lines read in a Sql Server 2014 database, the code works correctly but it takes too much time (even 40 minutes), especially during the update part, as can I speed up this code? I tried different solutions with c# but I do not get any noticeable improvements! Could I use another language like python as an alternative? One last thing the file does not contain separators between the fields so I can not use Bulk's TSQL statements! (The problem of slowness appears especially on update queries)

Code:

public Boolean ImportaListinoElettroveneta(String PercorsoFile)
{
GC.Collect();
GC.WaitForPendingFinalizers();
Boolean ret = true;
SqlConnection conn = Database.apriconnessione();
String QueryAggiornaNonDisponbili = "Update Articolo set Stato='Nondisponibile' where Importato='ELETTROVENETA' ";
SqlCommand command = new SqlCommand(QueryAggiornaNonDisponbili, conn);
command.CommandTimeout = 0;
command.ExecuteNonQuery();
try
{
String EliminaTabellaTemporanea = "IF OBJECT_ID('##Importazione') IS not  NULL  drop table ##Importazione";
command = new SqlCommand(EliminaTabellaTemporanea, conn);
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Errore la tabella temporanea non esiste: " + ex);
}
String Query_Creazione_Tabella_Temporanea = "create table ##Importazione( CodiceNumericoEV varchar(5000),DescrizioneArticolo varchar(5000),CodiceArticoloEV varchar(5000),MarcaEV varchar(5000),UM varchar(5000),PrezzoListino money,Sconto1 money,Sconto2 money,Sconto3 money,Sconto4 money,PrezzoNetto money,CodiceBarreMetel bigint,IVA varchar(5000),MarcaMetel varchar(5000),ArticoloMetel varchar(5000),DescrizioneMarca varchar(5000)) ";
command = new SqlCommand(Query_Creazione_Tabella_Temporanea, conn);
command.CommandTimeout = 0;
command.ExecuteNonQuery();
command.CommandText = "INSERT Into ##Importazione(CodiceNumericoEV,DescrizioneArticolo,CodiceArticoloEV,MarcaEV,UM,PrezzoListino,Sconto1,Sconto2,Sconto3,Sconto4,PrezzoNetto,CodiceBarreMetel,IVA,MarcaMetel,ArticoloMetel,DescrizioneMarca) Values(@CodiceNumericoEV,@DescrizioneArticolo,@CodiceArticoloEV,@MarcaEV,@UM,@PrezzoListino,@Sconto1,@Sconto2,@Sconto3,@Sconto4,@PrezzoNetto,@CodiceBarreMetel,@IVA,@MarcaMetel,@ArticoloMetel,@DescrizioneMarca)";

try
{
{
using (BufferedStream bs = new BufferedStream(fs, System.Text.ASCIIEncoding.Unicode.GetByteCount("271")))
{
string s;
while ((s = sr.ReadLine()) != null)
{
command.Parameters.Clear();
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Errore riga: CodiceArticolo:" + s.Substring(startIndex: 174, length: 16) + " tipo di errore: " + ex);
}
}
}
}
command.Parameters.Clear();
String QueryInserimentoNuoviArticoli = "Insert into Articolo(CodArt,Descrizione,CodMarca,CodEAN,Prezzo,PrezzoListino,UM,Fornitore,Importato) SELECT ArticoloMetel as CodArt,DescrizioneArticolo as Descrizione,MarcaMetel as CodMarca,CodiceBarreMetel as CodEAN,PrezzoNetto,PrezzoListino,UM,MarcaMetel as Fornitore,'ELETTROVENETA' as Importato FROM ##Importazione where ArticoloMetel not in ( select CodArt from Articolo where Importato = 'ELETTROVENETA' ) and MarcaMetel not in ( select CodMarca from Articolo where Importato = 'ELETTROVENETA' ) ";
SqlCommand command2 = new SqlCommand(QueryInserimentoNuoviArticoli, conn);
command2.CommandTimeout = 0;
command2.ExecuteNonQuery();
command2.Parameters.Clear();
String QueryAggiornamentoArticoliPresenti = " Update Articolo set Stato = 'Disponibile', Descrizione = i.Descrizione, CodEAN = i.CodEAN, Prezzo = i.PrezzoNetto, PrezzoListino = i.PrezzoListino, UM = i.UM, DataAggiornamento = getdate() from( SELECT ArticoloMetel as CodArt, DescrizioneArticolo as Descrizione, MarcaMetel as CodMarca, CodiceBarreMetel as CodEAN, PrezzoNetto, PrezzoListino, UM, MarcaMetel as Fornitore, 'ELETTROVENETA' as Importato FROM ##Importazione  where ArticoloMetel in (select CodArt from Articolo where Importato = 'ELETTROVENETA') and MarcaMetel in (select CodMarca from Articolo where Importato = 'ELETTROVENETA'))i where Articolo.CodArt = i.CodArt and i.CodMarca = Articolo.CodMarca and Articolo.Importato = 'ELETTROVENETA' and Articolo.Fornitore = i.Fornitore";
SqlCommand command3 = new SqlCommand(QueryAggiornamentoArticoliPresenti, conn);
command3.CommandTimeout = 0;
command3.ExecuteNonQuery();
try
{
String EliminaTabellaTemporanea = "IF OBJECT_ID('##Importazione') IS not  NULL  drop table ##Importazione";
command = new SqlCommand(EliminaTabellaTemporanea, conn);
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Errore la tabella temporanea non esiste: " + ex);
}
}
catch (Exception ex)
{
ret = false;
Console.WriteLine("Errore Importazione Listino Elettroveneta: " + ex);
Managementerror.SendError("Errore" + ex);
}
conn.Close();
return ret;
}


Scheme of Table Articolo:

CREATE TABLE [dbo].[Articolo](
[IdArticolo] [int] IDENTITY(1,1) NOT NULL,
[CodArt] [varchar](max) NULL,
[TipoArticolo] [varchar](5) NULL,
[CodMarca] [varchar](100) NULL,
[CodEAN] [bigint] NULL,
[Fornitore] [varchar](200) NULL,
[Importato] [varchar](200) NULL,
[UM] [varchar](10) NULL,
[Descrizione] [varchar](max) NULL,
[Prezzo] [money] NULL,
[PrezzoListino] [money] NULL,
[QualificatoreCodiceBarcode] [varchar](10) NULL,
[CodiceBarcode] [varchar](50) NULL,
[CodiceElectrocod] [varchar](30) NULL,
[FamigliaStatistica] [varchar](30) NULL,
[StatodelProdotto] [varchar](30) NULL,
[QuantitaMassimaOrdinazione] [int] NULL,
[MoltiplicatorePrezzo] [int] NULL,
[QuantitaMinimaOrdinazione] [int] NULL,
[QuantitaMultiplaOrdinazione] [int] NULL,
[ProdottoComposto] [int] NULL,
[QuantitaCartone] [int] NULL,
[CodiceValuta] [varchar](20) NULL,
[PDF] [varchar](8000) NULL,
[DataUltimaVariazione] [datetime] NULL,
[DataInserimento] [datetime] NULL,
[DataAggiornamento] [datetime] NULL,
[Stato] [varchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[IdArticolo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT ('A') FOR [TipoArticolo]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [CodMarca]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [Fornitore]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [Importato]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [UM]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [Prezzo]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [PrezzoListino]
GO

GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [QualificatoreCodiceBarcode]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [CodiceBarcode]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [CodiceElectrocod]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [FamigliaStatistica]
GO

GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [StatodelProdotto]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [QuantitaMassimaOrdinazione]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [MoltiplicatorePrezzo]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [QuantitaMinimaOrdinazione]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [QuantitaMultiplaOrdinazione]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [ProdottoComposto]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [QuantitaCartone]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [CodiceValuta]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [PDF]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [DataUltimaVariazione]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (getdate()) FOR [DataInserimento]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT (NULL) FOR [DataAggiornamento]
GO

ALTER TABLE [dbo].[Articolo] ADD  DEFAULT ('Disponibile') FOR [Stato]
GO

GO

• "I tried different solutions with c# but I do not get any noticeable improvements!" To save people wasting their time by proposing things you've already tried, what were these different solutions? Apr 4 '18 at 7:38
• @PeterTaylor for queries I have no alternatives (that I know) not being able to use the bulk! -all the solutions at this link for reading file: cc.davelozinski.com/c-sharp/fastest-way-to-read-text-files
– riki
Apr 4 '18 at 7:44
• Did you measure reading the file and insertion separately? Are you sure it's the insert that is slow and not the method reading the file? But since you did not separate this two tasks it'll be difficult to tell. You should do this first. Apr 4 '18 at 8:10
• Why should python or any other language be faster? It's either the parsing process or the number of inserts that make your query slow and you can code this the same bad way in any language. Apr 4 '18 at 8:12
• It would be helpful to have the scheme of Articolo, or at least to know what the primary key is and what other indexes it has. Apr 4 '18 at 8:50

Firstly, I find the code quite hard to read.

It's understandable that you would want to use your native language, but that doesn't prevent you from using capitalisation: e.g. Database.ApriConnessione() instead of Database.apriconnessione(). And it doesn't explain the inconsistent word separation between e.g. EliminaTabellaTemporanea and Query_Creazione_Tabella_Temporanea.

            try
{
String EliminaTabellaTemporanea = "IF OBJECT_ID('##Importazione') IS not  NULL  drop table ##Importazione";
command = new SqlCommand(EliminaTabellaTemporanea, conn);
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Errore la tabella temporanea non esiste: " + ex);
}


occurs twice, and by pulling it out to a separate method you would both ensure consistency and reduce the noise which makes it harder to see the core of the insertion. (Also, the error message doesn't make sense to me: the SQL checks that the table exists, so why do you think that an exception would be caused by the table not existing?)

The SQL lines are rather long. I would try using @"" to split them over multiple lines with indentation, because I think that would make them more readable. They would probably also be better pulled out of the method as fields or consts.

## Performance: profiling and insertion

I understand from your comment that all of the things you've tried relate to reading the data, but I don't see any profiling evidence that reading the data is the bottleneck. Profiling data can be surprising, but I would be very surprised if reading data from a flat file is slower than inserting it into a relational database, which maintains more complicated data structures. So first: profile.

Second, if insertion is indeed the bottleneck, there are at least two approaches which I would expect to be faster.

A. Use a prepared statement to insert. At present, the database is parsing the INSERT command every time.

B. You say that "I can not use Bulk's TSQL statements!", but a quick Google query shows that there are other ways of doing bulk inserts. SqlBulkCopy seems worth investigating. There are various sub-approaches to how to use it. Your data is only about 100MB, so you could read it all into a DataTable for bulk insertion. You could implement your own IDataReader. Or you could use a producer-consumer setup where you have one thread reading 1000 lines at a time and inserting them into a DataTable, and a second thread doing the bulk database insert of that DataTable.

## Update

You note that the bottleneck seems to be the UPDATE. Formatting this query for legibility:

UPDATE Articolo
SET Stato = 'Disponibile',
Descrizione = i.Descrizione,
CodEAN = i.CodEAN,
Prezzo = i.PrezzoNetto,
PrezzoListino = i.PrezzoListino,
UM = i.UM,
DataAggiornamento = GETDATE()
FROM (
SELECT
ArticoloMetel AS CodArt,
DescrizioneArticolo AS Descrizione,
MarcaMetel AS CodMarca,
CodiceBarreMetel AS CodEAN,
PrezzoNetto,
PrezzoListino,
UM,
MarcaMetel AS Fornitore,
'ELETTROVENETA' as Importato
FROM ##Importazione
WHERE
ArticoloMetel IN (SELECT CodArt FROM Articolo WHERE Importato = 'ELETTROVENETA') AND
MarcaMetel IN (SELECT CodMarca FROM Articolo WHERE Importato = 'ELETTROVENETA')
) i
WHERE
Articolo.CodArt = i.CodArt AND
Articolo.CodMarca = i.CodMarca AND
Articolo.Importato = 'ELETTROVENETA' AND
Articolo.Fornitore = i.Fornitore


The first thing which stands out is that the aliasing is unnecessary. You've just created the temporary table and named its fields: if those names don't work, change them! Then we can simplify to

UPDATE Articolo
SET Stato = 'Disponibile',
Descrizione = i.Descrizione,
CodEAN = i.CodEAN,
Prezzo = i.PrezzoNetto,
PrezzoListino = i.PrezzoListino,
UM = i.UM,
DataAggiornamento = GETDATE()
FROM (
SELECT *
FROM ##Importazione
WHERE
ArticoloMetel IN (SELECT CodArt FROM Articolo WHERE Importato = 'ELETTROVENETA') AND
MarcaMetel IN (SELECT CodMarca FROM Articolo WHERE Importato = 'ELETTROVENETA')
) i
WHERE
Articolo.CodArt = i.CodArt AND
Articolo.CodMarca = i.CodMarca AND
Articolo.Importato = 'ELETTROVENETA' AND
Articolo.Fornitore = i.Fornitore


Secondly, the WHERE clauses in the subquery seem pointless, since they're implied by the WHERE class on the main query. Simplifying further,

UPDATE Articolo
SET Stato = 'Disponibile',
Descrizione = i.Descrizione,
CodEAN = i.CodEAN,
Prezzo = i.PrezzoNetto,
PrezzoListino = i.PrezzoListino,
UM = i.UM,
DataAggiornamento = GETDATE()
FROM ##Importazione i
WHERE
Articolo.CodArt = i.CodArt AND
Articolo.CodMarca = i.CodMarca AND
Articolo.Importato = 'ELETTROVENETA' AND
Articolo.Fornitore = i.Fornitore


I don't know whether it will perform better, but it might be clearer to use MERGE:

MERGE INTO Articolo
USING ##Importazione i
ON
Articolo.CodArt = i.CodArt AND
Articolo.CodMarca = i.CodMarca AND
Articolo.Importato = 'ELETTROVENETA' AND
Articolo.Fornitore = i.Fornitore
WHEN MATCHED UPDATE
SET Stato = 'Disponibile',
Descrizione = i.Descrizione,
CodEAN = i.CodEAN,
Prezzo = i.PrezzoNetto,
PrezzoListino = i.PrezzoListino,
UM = i.UM,
DataAggiornamento = GETDATE()


And then with respect to performance, SQL Server has pretty good profiling tools, but the obvious bottleneck is the matching: neither table has any useful index. I'm surprised that Articolo.CodArt isn't a unique key, but since it isn't the obvious way to speed things up would be to add an index to the temporary table, either on CodArt or on (CodArt, CodMarca, Fornitore).

• I used it a few times already and I can confirm, SqlBulkCopy is really fast... Apr 4 '18 at 8:15
• first of all thanks for the reply, then I state that perhaps I have expressed myself badly, The major problem of higher latency occurs during the update queries. My idea of ​​using the bulk allowed me to save a few minutes, (with the code written above the insertion in the temporary table of the database takes about 5 minutes) the biggest problem is on the update queries those are the bottleneck!
– riki
Apr 4 '18 at 8:28
• @riki, ah, yes, that makes sense as a bottleneck. Will have to think about it. Apr 4 '18 at 8:34
• I try to use your solution and let you know, CodArt is not unique because I can be more identical CodArt with the same CodMarca Imported from different price lists for this use IdArticolo, however I try to use the solution you posted and let me know! @PeterTaylor
– riki
Apr 4 '18 at 10:05

Why are you creating new commands? Just reuse the command. Why are are you clearing the parameters and creating them on every loop? Create the parameters once and assign the values in the loop.

Why are you using a global ## temporary when it is just the program using it?

Why not update in the loop? You could use a producer consumer with asynchronous updates.

As for the update. That is too hard to read. No comment. I suggest you make it readable and post it on dba.stackexchange.com. That update statement is a mess.