Как часто соединение должно быть закрыто/открыто?

Ответ 1

Если вы находитесь на SQL Server 2008, вы можете сразу отправить несколько записей через параметр таблицы:

create type testResultUpload as table
(
    TestName varchar(500),
    Result decimal(18,4) null,
    NonNumericResult varchar(50) null, 
    QuickLabDumpid int
)

Затем вы можете создать DataTable на стороне клиента и передать его в sql как один кусок. Хотя, вы можете захотеть сделать тысячу за раз, чтобы начать с.

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

alter proc Insert_TestResult
(
    @testResultUpload testResultUpload readonly -- tvp must be readonly
)
as begin       

    -- This is short and sweet for demonstrative purposes
    -- but you should explicitly list your columns
    insert [SalesDWH].[dbo].[TestResults] 
    select
     *
    from @testResultImport

end

Затем на вашей стороне клиента:

// create your datatable in the form of the newly created sql type
var dt = new DataTable();
dt.Columns.Add("TestName", typeof(String));
dt.Columns.Add("Result", typeof(Decimal));
dt.Columns.Add("NonNumericResult", typeof(String));
dt.Columns.Add("QuickLabDumpid", typeof(String));

// add your rows here (maybe do it in steps of a thousand
// 100 Million over the pipe at once is ill-advised)
// call the following code to hit sql

using (var cnx = new SqlConnection("your connection string"))
using (var cmd = new SqlCommand {
    Connection = cnx,
    CommandType = CommandType.StoredProcedure,
    CommandText = "dbo.Insert_TestResults",
    Parameters = {
        new SqlParameter {
            ParameterName = "@testResultUpload",
            Value = dt,
            SqlDbType = SqlDbType.Structured // make sure to specify structured
        }
    }
})
{
    cnx.Open();
    cmd.ExecuteNonQuery();
}

Ответ 2

Еще один вариант для вас. У .NET Framework был класс SqlBulkCopy начиная с версии 2.0. Главное, что вам нужно сделать, это убедиться, что схема DataTable соответствует вашей таблице. В вашем тестовом примере что-то вроде этого:

private void _initDataTable() {
  dt = new DataTable();
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.String"), 
    ColumnName = "TestName"
  });
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.Decimal"), 
    ColumnName = "Result"
  });
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.String"), 
    ColumnName = "NonNumericResult"
  });
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.Int32"), 
    ColumnName = "QuickLabDumpid"
  });
}

Код доступа к данным выглядит примерно так:

private void _insertData() {
  using (var c = new SqlConnection(CS)) {
    c.Open();
    using (var trans = c.BeginTransaction()) {
      try {
        using (var bc = new SqlBulkCopy(
          c, SqlBulkCopyOptions.TableLock, trans))
        {
          bc.DestinationTableName = "dbo.Insert_TestResults";
          bc.WriteToServer(dt);
        }
        trans.Commit();
      }
      catch (Exception e) {
        trans.Rollback();
        throw;
      }
    }
  }
}  

Протестировано с 10 миллионами записей:

private void _fillDataTable() {
  int batchToInsert = 1000000;
  int numberOfTimes = 10;
  int recordCounter = 1;
  for (int i = 0; i < numberOfTimes; ++i) {
    for (int j = 0; j < batchToInsert; j++) {
      var row = dt.NewRow();
      row[0] = string.Format("TestName{0}", recordCounter);
      row[1] = (decimal) i;
      row[2] = string.Format("NonNumericResult{0}", recordCounter);
      row[3] = i;
      dt.Rows.Add(row);
      recordCounter += 1;
    }
    _insertData();
    dt.Clear();
  }
}

На моей машине Dev заняло чуть более двух с половиной минут. Вероятно, вам захочется поэкспериментировать с тем, сколько записей будет производиться одновременно. (не 1 миллион, как в приведенном выше тестовом случае) И, очевидно, вы вкладываете в таблицу в 10 раз больше данных (предполагая, что ваши данные в реальном времени будут выше), но я очень сомневаюсь, что этот метод будет принимать 3 дня:)

Удачи вам в любом методе, который вы решаете.

ИЗМЕНИТЬ: Если это не очевидно, я забыл упомянуть - поскольку вы указываете имя таблицы при настройке свойства DestinationTableName, что вам нужно - никакой хранимой процедуры или каких-либо других операторов SQL.

Ответ 3

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

Ваша процедура медленная, главным образом потому, что:

• Каждая вставленная строка находится в отдельной отдельной транзакции
• Раздельная поездка DB для каждой строки

Исправление для первого - группировать ваши вставки в транзакции - возможно, 1000 строк на транзакцию или что-то в этом роде.

Исправление для второго - либо использовать пакетную обработку команд (отправлять несколько команд за раз, разделенные точками с запятой), либо параметры таблицы. TVP также хороши, потому что команда INSERT INTO SELECT FROM выполняется как одна транзакция.

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

Использование SqlBulkCopy - еще одна опция, которая также может помочь свести к минимуму нагрузку на ваш журнал БД, в зависимости от того, как он настроен.

Кроме того, если вы вставляете 100M строк одновременно, вы можете рассмотреть возможность удаления любых индексов в таблице перед запуском и повторного добавления их после завершения. В противном случае, если вы не вставляете строки в порядке кластеризованного индекса, он будет очень фрагментирован, а для некластеризованных индексов вы в основном делаете вставку в дополнительную таблицу для каждой вставки в основную таблицу - сверху проблемы фрагментации.

Ответ 4

нет необходимости закрывать и повторно открывать между каждым исполнением sp. Кроме того, вы можете уменьшить общее время выполнения, разбивая работу на куски и выполняя каждый фрагмент в отдельном потоке, поэтому вместо 100 м последовательных вызовов 10 потоков одновременно обрабатывают 10 м вызовов, например; одно соединение на поток: открыть, выполнить цикл, закрыть.

Ответ 5

Если он многопользовательский, тогда закройте соединение после того, как вы закончите с ними. Если один пользователь, то вы можете оставить его открытым на время жизни приложения:)

Ответ 6

почему бы не использовать соединения с Idisposable. В этом случае вам не нужно закрывать, но он автоматически вернется в пул соединений. пожалуйста, обратитесь к этому, вы поймете больше того, что я имею в виду. Если у вас многопоточные приложения, используйте этот способ. http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx

Ответ 7

Пока вы используете ту же самую строку соединения при каждом вызове GetConnection2, физическое подключение к SQL Server не открывается и не закрывается каждый раз -.NET сохраняет ваше соединение открытым и повторно используемым. Он по-прежнему вызывает некоторую потерю производительности, но не такой большой, как повторное подключение к базе данных.

Ответ 8

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

Ответ 9

Вы должны использовать объединение пулов, номинированное на web.config, так что каждый раз, когда вы открываете и закрываете соединение, на самом деле не открывается и закрыли, но выбрали из бассейна. Также используйте using, чтобы убедиться, что вы правильно настроены.

Ответ 10

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

Я не знаю, применимо ли это в вашем случае, но попробуйте вместо этого использовать инструкцию INSERT-INTO-SELECT-FROM.

Хорошая практика заключается в том, чтобы закрыть соединение, если вы не знаете, сколько времени потребуется, пока вы не выполните следующую команду, однако, если в цикле выполняется несколько команд, я не закрывал и не открывал соединение каждый раз.