Блог

У меня есть приложение на C #, в котором у меня возникает эта проблема: когда я запускаю этот фрагмент:

Первый способ

 public void GetList(List<string> liste, List<int> outliste)
{
    foreach( string s in liste){
        outliste.Add(SqlFunction(s));
    }
}
public int SqlFunction(string str)
{
    string query = "select id from user where name=" str;
    ...................
    // return the id
}
  

время выполнения составляет 51 сек.

Второй способ

 public void SqlSecondWayFunction(List<string> liste, List<int> outliste)
{

   string query ="select id from user where (";
   foreach(string str in liste){
       query  = "name=" str   "or  ";
   }
   query  = " 1=0 )";

   ...................
   // fill outliste by the result of the query
}
  

Время выполнения составляет 1 м: 19 секунд!!!!!!!!! (количество liste составляет около 11000).

Итак, мне нужно знать, почему первый способ быстрее?

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

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

По сути, вы просите базу данных сравнить name столбец с 11 000 строками для каждой записи в таблице. Если таблица содержит, например, 100 000 записей, то вы выполняете 1 100 000 000 сравнений строк. И затем вы все равно возвращаете все записи, потому что одним из ваших условий является «1 = 1», что всегда верно.

Несколько вещей:

• Добавление длинного списка OR в ваше предложение WHERE обычно не очень эффективно из-за времени, которое требуется для первого анализа очень длинной строки запроса, и, кроме того, внутренней работы, выполняемой ядром базы данных для длинных списков OR
• Последняя добавляемая вами часть («ИЛИ 1 = 1») делает предложение WHERE ВСЕГДА ПРОВЕРЯЕМЫМ TRUE! Это то же самое, что вообще не иметь предложения WHERE
• Любое из вышеперечисленных действий может привести к тому, что ваш первый пример множества простых запросов займет меньше времени, чем один неэффективный большой запрос
• Вы должны рассмотреть одно из следующих действий для вашего варианта использования: 1) получите список идентификаторов, загрузив DataTable с помощью «select id from user», вызывая DataTable.Метод AsEnumerable(), а затем присоединение к вашему другому списку с помощью запроса LINQ, 2) сначала загрузка списка в таблицу базы данных, а затем использование SQL для присоединения пользовательской таблицы к вашему списку или 3) загрузка списка в список строк, разделенных запятыми, и использование SQL в операторе INвместо вашей конструкции name=x ИЛИ name=y ИЛИ name=z.

Используйте табличные параметры, если вы хотите передать список в SQL Server 2008. В вашей базе данных сначала вам нужно будет создать новый ТИП:

 CREATE TYPE dbo.ListOfInt AS TABLE(Value INT);
  

Затем вы можете использовать это в качестве параметра, что-то вроде:

 var table = new DataTable();
table.Columns.Add("Value", typeof(int));

for (int i = 0; i < liste.Count; i  )
{
    var row = table.NewRow();
    row[0] = liste[i];
    table.Rows.Add(row);
}

string sql = "SELECT ID FROM [User] WHERE ID IN (SELECT Value FROM @Liste)";

using (var connection = new SqlConnection("Your connection String"))
using (var command = new SqlCommand(sql, connection))
{
    connection.Open();
    var tvp = new SqlParameter("@Liste", SqlDbType.Structured).TypeName = "ListOfInt";
    tvp.Value = table;
    command.Parameters.Add(tvp);
    using (var reader = command.ExecuteReader())
    while (reader.Read())
    {
        outliste.Add(reader.GetInt("ID"));
    }
}