Загружайте в базу быстрее

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

    public void uploadContent()
    {
        var cs = Properties.Settings.Default.Database;
        SqlConnection dataConnection = new SqlConnection(cs);
        dataConnection.Open();

        for (int i = 0; i < urlList.Count; i++)
        {
            SqlCommand dataCommand = new SqlCommand(Properties.Settings.Default.CommandString, dataConnection);
            try
            {
                dataCommand.Parameters.AddWithValue("@user", userList[i]);
                dataCommand.Parameters.AddWithValue("@computer", computerList[i]);
                dataCommand.Parameters.AddWithValue("@date", timestampList[i]);
                dataCommand.Parameters.AddWithValue("@itemName", domainList[i]);
                dataCommand.Parameters.AddWithValue("@itemDetails", urlList[i]);
                dataCommand.Parameters.AddWithValue("@timesUsed", hitsList[i]);

                dataCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                using (StreamWriter sw = File.AppendText("errorLog.log"))
                {
                    sw.WriteLine(e);
                }
            }

        }
        dataConnection.Close();
    }

Вот командная строка, которую код извлекает из файла конфигурации:

Командная строка:

INSERT dbo.InternetUsage VALUES (@user, @computer, @date, @itemName, @itemDetails, @timesUsed)

sql c# sqlcommand
person Ryan Duffing    schedule 23.10.2012    source источник
comment
Я сгенерировал XML до сих пор, загрузил в sproc, который принимает XML и использует XML-запросы на стороне сервера для обработки узлов в строки таблицы. Это означает, что это всего лишь один вызов, и сервер выполняет всю обработку тысяч строк.   -  person Lloyd    schedule 23.10.2012
comment
Какую базу данных вы используете, обычно вы хотите запускать несколько вставок в одной команде. Например, см. этот пост: stackoverflow.com/questions/2624713/   -  person Simon    schedule 23.10.2012
comment
@ Саймон, я попробую. Я не думал об этом. Спасибо.   -  person Ryan Duffing    schedule 23.10.2012
comment
Имейте в виду, что вы, вероятно, будете генерировать команду sql динамически, и вам необходимо убедиться, что она не страдает от внедрения sql. Другими вариантами могут быть просмотр утилит, таких как sqlbulkcopy, и/или передача табличных параметров хранимым процедурам, или даже запрос пустой таблицы данных, добавление всех строк, а затем фиксация изменений после всех вставок.   -  person Simon    schedule 23.10.2012
comment
Проверьте этот поток stackoverflow.com/ вопросы/2972974/ . я думаю, это похоже на ваш вопрос   -  person ígor    schedule 23.10.2012
comment
@Simon Ваш метод сработал бы прекрасно, но, похоже, мне придется использовать массовую вставку, поскольку мне нужно вставить более 1000 строк.   -  person Ryan Duffing    schedule 23.10.2012

Ответы (4)


arrow_upward
2
arrow_downward

Как упоминалось в ответе @alerya, поможет следующее (здесь добавлено объяснение)

1) Создать команду и параметр вне цикла for Поскольку каждый раз используется одна и та же команда, нет смысла каждый раз создавать команду заново. В дополнение к созданию нового объекта (что требует времени), команда также должна проверяться каждый раз, когда она создается для нескольких вещей (таблица существует и т. д.). Это вводит много накладных расходов.

2) Поместите вставки в транзакцию. Включение всех вставок в транзакцию ускорит процесс, поскольку по умолчанию команда, не входящая в транзакцию, будет считаться отдельной транзакцией. Таким образом, каждый раз, когда вы что-то вставляете, сервер базы данных должен проверять, действительно ли сохранено то, что он только что вставил (обычно на жестком диске, скорость которого ограничена скоростью диска). Однако, когда несколько INSERT находятся в одной транзакции, проверку необходимо выполнить только один раз.

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

Кроме того Еще одна вещь, которую вы действительно должны сделать (хотя в краткосрочной перспективе это не ускорит работу), — правильно использовать интерфейс IDisposable. Это означает либо вызов .Dispose() для всех объектов IDisposable (SqlConnection, SqlCommand), либо, в идеале, их обертывание в блоки using():

using( SqlConnection dataConnection = new SqlConnection(cs) 
{
    //Code goes here
}

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

person sybkar    schedule 23.10.2012
comment
Спасибо. Я создал команду вне цикла, затем использовал массовую вставку, так что все это в рамках одной транзакции. Теперь я просто жду, пока наш администратор базы данных даст мне разрешения на массовую загрузку базы данных. - person Ryan Duffing; 23.10.2012
comment
@RyanDuffing, это хорошо. Просто убедитесь, что вы используете конструкцию using() или, как минимум, вызываете .dispose() для каждого объекта IDisposable! - person sybkar; 23.10.2012

arrow_upward
2
arrow_downward

  1. Создать команду и параметр за пределами for (int i = 0; i ‹ urlList.Count; i++)
  2. Также создайте вставку в транзакции
  3. Если возможно, создайте хранимую процедуру и передайте параметры как DataTable.
person alerya    schedule 23.10.2012

arrow_upward
1
arrow_downward

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

  1. Создайте плоский файл со всей вашей информацией в формате, который понимает BULK INSERT.
  2. Используйте команду BULK INSERT, чтобы импортировать этот файл в базу данных (http://msdn.microsoft.com/en-us/library/ms188365%28v=sql.90%29.aspx).

Пс. Я думаю, когда вы говорите SQL, вы используете MS SQL Server.

person Fabio    schedule 23.10.2012

arrow_upward
-1
arrow_downward

Почему бы вам не запустить метод uploadContent() из отдельного потока.
Таким образом, вам не нужно беспокоиться о том, сколько времени потребуется для выполнения запроса.

person Gabriel Marcos Jarczun    schedule 23.10.2012
comment
Его беспокоит, сколько времени занимают вставки. Он не сказал, что это мешает ему делать что-то еще... он также уточнил, что это происходит в конце программы, поэтому выполнение этого в другом потоке на самом деле не будет иметь значения с точки зрения предотвращения других вещей. - person sybkar; 23.10.2012