trjhtr

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 (FileStream fs = File.Open(PercorsoFile, FileMode.Open, FileAccess.Read, FileShare.Read))
 
 using (BufferedStream bs = new BufferedStream(fs, System.Text.ASCIIEncoding.Unicode.GetByteCount("271")))
 using (StreamReader sr = new StreamReader(bs))
 
 string s;
 while ((s = sr.ReadLine()) != null)
 
 command.Parameters.Clear();
 try
 
 command.Parameters.AddWithValue("@CodiceNumericoEV", s.Substring(startIndex: 1, length: 13));
 command.Parameters.AddWithValue("@DescrizioneArticolo", s.Substring(startIndex: 13, length: 45));
 command.Parameters.AddWithValue("@CodiceArticoloEV", s.Substring(startIndex: 58, length: 25));
 command.Parameters.AddWithValue("@MarcaEV", s.Substring(startIndex: 83, length: 6));
 command.Parameters.AddWithValue("@UM", s.Substring(startIndex: 89, length: 2));
 command.Parameters.AddWithValue("@PrezzoListino", decimal.Parse(s.Substring(startIndex: 106, length: 15)));
 command.Parameters.AddWithValue("@Sconto1", 0);
 command.Parameters.AddWithValue("@Sconto2", 0);
 command.Parameters.AddWithValue("@Sconto3", 0);
 command.Parameters.AddWithValue("@Sconto4", 0);
 command.Parameters.AddWithValue("@PrezzoNetto", decimal.Parse(s.Substring(startIndex: 142, length: 15)));
 command.Parameters.AddWithValue("@CodiceBarreMetel", s.Substring(startIndex: 156, length: 13));
 command.Parameters.AddWithValue("@IVA", s.Substring(startIndex: 169, length: 2));
 command.Parameters.AddWithValue("@MarcaMetel", s.Substring(startIndex: 171, length: 3));
 command.Parameters.AddWithValue("@ArticoloMetel", s.Substring(startIndex: 174, length: 16));
 command.Parameters.AddWithValue("@DescrizioneMarca", s.Substring(startIndex: 190, length: 25));
 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,
 [LeadTime] [varchar](10) NULL,
 [QualificatoreCodiceBarcode] [varchar](10) NULL,
 [CodiceBarcode] [varchar](50) NULL,
 [CodiceElectrocod] [varchar](30) NULL,
 [FamigliaStatistica] [varchar](30) NULL,
 [FamigliadiSconto] [varchar](30) NULL,
 [IdFamigliaDiSconto] [int] 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

ALTER TABLE [dbo].[Articolo] ADD DEFAULT (NULL) FOR [LeadTime]
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

ALTER TABLE [dbo].[Articolo] ADD DEFAULT (NULL) FOR [FamigliadiSconto]
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

ALTER TABLE [dbo].[Articolo] WITH CHECK ADD FOREIGN KEY([IdFamigliaDiSconto])
REFERENCES [dbo].[FamigliaDiSconto] ([IdFamigliaDiSconto])
GO

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

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.