Вставки большого txt-файла в SQL сервере


Код размещен ниже читает 500000 строк из текстового файла и импорт строк прочитать в SQL Server 2014 с БД, код работает правильно, но это занимает слишком много времени (даже 40 минут), особенно во время обновления часть, как я могу ускорить этот код? Я пробовал разные решения на C# , но я не получаю каких-либо заметных улучшений! Я мог бы использовать другой язык, как Python в качестве альтернативы? Одна последняя вещь, файл не содержит разделителей между полями, так что я не могу используйте TSQL заявления Навального же! (Проблема неповоротливости появляется особенно на обновление запросов)

Код:

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;
}

Схема таблица статья:

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


416
-1
задан 4 апреля 2018 в 07:04 Источник Поделиться
Комментарии
2 ответа

Во-первых, я найти код очень трудно читать.

Понятно, что вы хотели бы использовать свой родной язык, но это не мешает вам использовать капитализация: например, Database.ApriConnessione() вместо Database.apriconnessione(). И это не объяснить непоследовательное разделение между напр. EliminaTabellaTemporanea и 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);
}

происходит дважды, и, вытащив ее в отдельный метод вы бы как для обеспечения согласованности и уменьшить шум, который делает это тяжелее, чтобы увидеть сердцевину вставки. (Кроме того, сообщение об ошибке не имеет смысла для меня: SQL проверяет, что таблица существует, так почему вы думаете, что исключение будет вызвано в таблице не существует?)

В SQL линии довольно длинные. Я хотел бы попробовать, используя @"" разделить их на нескольких строках с отступами, потому что я думаю, что бы сделать их более читабельными. Они, вероятно, также будет лучше, вытащил из способ как поля или constС.


Производительность: профилирование и вставки

Я так понимаю из вашего комментария, что все вещи, которые вы пытались относиться к чтению данных, но я не вижу никакого профилирования доказательства того, что чтение данных является узким местом. "Профилирование данных" может быть удивительно, но я бы очень удивился, если бы чтение данных из плоского файла медленнее, чем вставлять ее в реляционную базу данных, которая поддерживает более сложные структуры данных. Итак, первое: профиль.

Во-вторых, если вставки это действительно узкое место, есть как минимум два подхода, которые я бы ожидать, чтобы быть быстрее.

А. использовать подготовленное заявление для вставки. В настоящее время, база данных парсинг вставить команду каждый раз.

Б. Вы говорите, что "я не могу использовать Навального в TSQL не заявления!", но простой запрос в Гугле показывает, что есть и другие способы сделать массовой вставки. SqlBulkCopy , кажется, стоит разобраться. Есть несколько различных подходов к тому, как использовать его. Ваши данные только около 100МБ, так что вы можете прочитать все это в DataTable для массовой вставки. Вы могли бы реализовать свой собственный IDataReader. Или вы могли бы использовать производитель-потребитель настройки, где у вас есть один поток чтение 1000 строк за один раз и вставлять их в DataTableи второй поток делает основную базу вставка, что DataTable.


Обновление

Вы обратите внимание, что узким местом по-видимому UPDATE. Форматирование этого запроса для удобочитаемости:

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

Первая вещь, которая выделяется, является то, что сглаживание ненужно. Вы только что создали временную таблицу и назвал ее полей: если эти имена не работают, менять их! Тогда мы можем упростить

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

Во-вторых, WHERE положения в подзапросе, кажется, бессмысленно, так как они подразумеваются по WHERE класса на основной запрос. Упрощения в дальнейшем,

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

Я не знаю, будет ли он работать лучше, но это может быть понятнее в использовании 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()

А затем с учетом производительности SQL Server имеет довольно хорошие инструменты профилирования, но очевидным плюсом является совпадений: ни одна таблица не имеет никаких полезных индексов. Я удивлен, что Articolo.CodArt не уникальный ключ, но так как это не очевидный способ можно было бы добавить индекс для временной таблицы, либо на CodArt или на (CodArt, CodMarca, Fornitore).

2
ответ дан 4 апреля 2018 в 08:04 Источник Поделиться

Зачем вы создаете новые команды? Просто повторно использовать команды. Почему вы оправдали параметров и создавать их на каждом цикле? Создать как только параметры и присвоить значения в цикле.

Почему ты используешь глобальную ## временно, когда это просто программа, используя его?

Почему бы не обновить в курсе? Вы можете использовать производителя потребителю асинхронного обновления.

Что касается самого обновления. Это слишком трудно читать. Без комментариев. Я предлагаю вам сделать его читаемым и разместить его на dba.stackexchange.com. Это заявление обновление бардак.

0
ответ дан 4 апреля 2018 в 09:04 Источник Поделиться