С#, EF & LINQ: медленный при вставке больших (7Mb) записей в SQL Server

Там длинная версия этого вопроса и короткая версия.

Краткая версия:

почему LINQ и EF настолько медленны при вставке одной большой (7 Мб) записи в удаленную базу данных SQL Server?

И здесь длинная версия (с некоторой информацией об обходных решениях, которые могут быть полезны другим читателям):

Весь следующий примерный код работает нормально, но поскольку мои пользователи находятся в Европе, а наши центры обработки данных находятся в Америке, это чертовски медленно. Но если я запускаю тот же код на виртуальном ПК в Америке, он запускается мгновенно. (И нет, к сожалению, моя компания хочет сохранить все данные на дому, поэтому я не могу использовать Azure, Amazon Cloud Services и т.д.)

Довольно многие из моих корпоративных приложений связаны с чтением/записью данных из Excel в SQL Server, и часто мы хотим сохранить необработанную копию файла Excel в таблице SQL Server.

Это очень просто сделать, просто читая исходные данные из локального файла и сохраняя его в записи.

private int SaveFileToSQLServer(string filename)
{
    //  Read in an Excel file, and store it in a SQL Server [External_File] record.
    //
    //  Returns the ID of the [External_File] record which was added.
    //

    DateTime lastModifed = System.IO.File.GetLastWriteTime(filename);
    byte[] fileData = File.ReadAllBytes(filename);

    //  Create a new SQL Server database record, containing our file raw data 
    //  (Note: the table has an IDENTITY Primary-Key, so will generate a ExtFile_ID for us.)
    External_File newFile = new External_File()
    {
        ExtFile_Filename = System.IO.Path.GetFileName(filename),
        ExtFile_Data = fileData,
        ExtFile_Last_Modified = lastModifed,
        Update_By = "mike",
        Update_Time = DateTime.UtcNow
    };
    dc.External_Files.InsertOnSubmit(newFile);
    dc.SubmitChanges(); 

    return newFile.ExtFile_ID;
}

Да, никаких сюрпризов нет, и он отлично работает.

Но я заметил, что для больших файлов Excel (7-8Mb) этот код для вставки одной (большой!) записи займет 40-50 секунд. Я помещал это в фоновый поток, и все это работало нормально, но, конечно, если пользователь оставит мое приложение, этот процесс будет уничтожен, что вызовет проблемы.

В качестве теста я попытался заменить эту функцию кодом, чтобы сделать это:

  • скопируйте файл в общий каталог на машине SQL Server.
  • называется хранимой процедурой для чтения необработанных данных (blob) в одну и ту же таблицу

Используя этот метод, весь процесс займет всего 3-4 секунды.

Если вам интересно, здесь хранимая процедура, которую я использовал для загрузки файла (который ДОЛЖЕН быть сохранен в папке на самой машине SQL Server) в запись базы данных:

CREATE PROCEDURE [dbo].[UploadFileToDatabase]
    @LocalFilename nvarchar(400)
AS
BEGIN
    --  By far, the quickest way to do this is to copy the file onto the SQL Server machine, then call this stored
    --  procedure to read the raw data into a [External_File] record, and link it to the Pricing Account record.
    --
    --      EXEC [dbo].[UploadPricingToolFile] 'D:\ImportData\SomeExcelFile.xlsm'
    -- 
    --  Returns: -1 if something went wrong  (eg file didn't exist) or the ID of our new [External_File] record
    --
    --  Note that the INSERT will go wrong, if the user doesn't have "bulkadmin" rights.
    --      "You do not have permission to use the bulk load statement."
    --  EXEC master..sp_addsrvrolemember @loginame = N'GPP_SRV', @rolename = N'bulkadmin'
    --
    SET NOCOUNT ON;

    DECLARE 
        @filename nvarchar(300),        --  eg "SomeFilename.xlsx"  (without the path)
        @SQL nvarchar(2000),
        @New_ExtFile_ID int

    --  Extract (just) the filename from our Path+Filename parameter
    SET @filename = RIGHT(@LocalFilename,charindex('\',reverse(@LocalFilename))-1)

    SET @SQL = 'INSERT INTO [External_File]  ([ExtFile_Filename], [ExtFile_Data]) '
    SET @SQL = @SQL + 'SELECT ''' + @Filename + ''', * 
    SET @SQL = @SQL + ' FROM OPENROWSET(BULK ''' + @LocalFilename +''', SINGLE_BLOB) rs'

    PRINT convert(nvarchar, GetDate(), 108) + ' Running: ' + @SQL
    BEGIN TRY
        EXEC (@SQL)
        SELECT @New_ExtFile_ID = @@IDENTITY
    END TRY
    BEGIN CATCH
        PRINT convert(nvarchar, GetDate(), 108) + ' An exception occurred.'
        SELECT -1
        RETURN
    END CATCH

    PRINT convert(nvarchar, GetDate(), 108) + ' Finished.'

    --  Return the ID of our new [External_File] record
    SELECT @New_ExtFile_ID
END

Ключ к этому коду заключается в том, что он создает команду SQL следующим образом:

INSERT INTO [External_File]  ([ExtFile_Filename], [ExtFile_Data])
SELECT 'SomeFilename.xlsm', * FROM OPENROWSET(BULK N'D:\ImportData\SomeExcelFile.xlsm', SINGLE_BLOB) rs

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

Как я уже сказал, для копирования файла в папку на машине SQL Server потребовалось 3-4 секунды и выполнить эту хранимую процедуру по сравнению с 40-50 секундами, чтобы сделать то же самое, используя код С# с LINQ или EF.

Экспорт данных BLOB из SQL Server во внешний файл

И, конечно, то же самое верно в обратном направлении.

Сначала я написал код С#/LINQ для загрузки одной записи базы данных (7Mb!) и записи ее двоичных данных в raw файл. Это заняло около 30-40 секунд.

Но если я сначала экспортировал данные SQL Server в файл (сохраненный на машине SQL Server)...

EXEC master..xp_cmdshell 'BCP "select ef.ExtFile_Data FROM [External_File] ef where ExtFile_ID = 585" queryout "D:\ImportData\SomeExcelFile.xslx" -T -N'

... а затем скопировал файл из папки SQL Server в папку пользователя, а затем снова запустил пару секунд.

И это мой вопрос: почему LINQ и EF так плохо при вставке одной большой записи в базу данных?

Я предполагаю, что задержка (расстояние между нами, здесь, в Европе, и наши центры данных в Штатах) являются основной причиной задержки, но просто странно, что копия файлов с болотным стандартом может быть намного быстрее.

Я что-то пропустил?

Очевидно, что я нашел пути к этим проблемам, но они включают дополнительные дополнительные разрешения для наших машин SQL Server и общих папок на машинах SQL Server, и наши администраторы баз данных действительно не любят предоставлять права на такие вещи, как "xp_cmdshell"...

Несколько месяцев спустя...

На этой неделе у меня была такая же проблема, и я попробовал предложение Кевина Х использовать Bulk-Insert для вставки большой (6Mb) записи в SQL Server.

Используя объемную вставку, потребовалось около 90 секунд, чтобы вставить запись 6 Мб, даже если наш центр обработки данных находится на расстоянии 6000 миль.

Итак, мораль истории: при вставке очень больших записей базы данных избегайте использования регулярной команды SubmitChanges() и придерживайтесь использования массивной вставки.

Ответы

Ответ 1

Вы можете попробовать использовать профилировщик, чтобы узнать, что делает Entity Framework со вставкой. Например, если он выбирает данные из вашей таблицы, может потребоваться много времени, чтобы вернуть данные по проводу, и вы можете не заметить это локально.

Я обнаружил, что лучший способ загрузить большой объем данных (как количество записей, так и размер записи) в sql-сервер из С# - это использовать SqlBulkCopy. Даже если вы вставляете только 1 запись, вы все равно можете воспользоваться этим изменением.

Чтобы использовать массовую копию, просто создайте datatable, который соответствует структуре вашей таблицы. Затем вызовите код следующим образом.

using (SqlConnection destinationConnection = new    SqlConnection(connectionString))
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
    bulkCopy.DestinationTableName = "External_File";
    bulkCopy.WriteToServer(dataTable);
}