-1
\$\begingroup\$

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
\$\endgroup\$
6
  • 3
    \$\begingroup\$ "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? \$\endgroup\$ Apr 4 '18 at 7:38
  • \$\begingroup\$ @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 \$\endgroup\$
    – riki
    Apr 4 '18 at 7:44
  • \$\begingroup\$ 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. \$\endgroup\$
    – t3chb0t
    Apr 4 '18 at 8:10
  • \$\begingroup\$ 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. \$\endgroup\$
    – t3chb0t
    Apr 4 '18 at 8:12
  • \$\begingroup\$ 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. \$\endgroup\$ Apr 4 '18 at 8:50
2
\$\begingroup\$

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

\$\endgroup\$
4
  • 1
    \$\begingroup\$ I used it a few times already and I can confirm, SqlBulkCopy is really fast... \$\endgroup\$
    – t3chb0t
    Apr 4 '18 at 8:15
  • \$\begingroup\$ 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! \$\endgroup\$
    – riki
    Apr 4 '18 at 8:28
  • \$\begingroup\$ @riki, ah, yes, that makes sense as a bottleneck. Will have to think about it. \$\endgroup\$ Apr 4 '18 at 8:34
  • \$\begingroup\$ 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 \$\endgroup\$
    – riki
    Apr 4 '18 at 10:05
0
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.