c# - SQL 大量的 ID 选择——如何让它更快？

Question

我有一个包含大量 ID 的数组，我想从数据库中选择出来。

通常的方法是select blabla from xxx where yyy IN (ids) OPTION (RECOMPILE)。(需要选项重新编译，因为 SQL 服务器不够智能，看不到将此查询放入其查询缓存中是对内存的巨大浪费)

但是，当 ID 数量很多时，SQL Server 对这种类型的查询很糟糕，它使用的解析器太慢了。让我举个例子:

SELECT * FROM table WHERE id IN (288525, 288528, 288529,<about 5000 ids>, 403043, 403044) OPTION (RECOMPILE)

执行时间:~1100 毫秒(在我的示例中返回大约 200 行)

对比:

SELECT * FROM table WHERE id BETWEEN 288525 AND 403044 OPTION (RECOMPILE)

执行时间:~80 毫秒(在我的示例中返回大约 50000 行)

因此，即使我返回的数据多了 250 倍，它的执行速度也快了 14 倍...

所以我构建了这个函数来获取我的 ID 列表并构建一些东西来返回两者之间的合理折衷(一些东西不会返回 250 倍的数据，但仍然可以更快地解析查询)

  private const int MAX_NUMBER_OF_EXTRA_OBJECTS_TO_FETCH = 5;
  public static string MassIdSelectionStringBuilder(
       List<int> keys, ref int startindex, string colname)
  {
     const int maxlength = 63000;
     if (keys.Count - startindex == 1)
     {
        string idstring = String.Format("{0} = {1}", colname, keys[startindex]);
        startindex++;
        return idstring;
     }
     StringBuilder sb = new StringBuilder(maxlength + 1000);
     List<int> individualkeys = new List<int>(256);
     int min = keys[startindex++];
     int max = min;
     sb.Append("(");
     const string betweenAnd = "{0} BETWEEN {1} AND {2}\n";
     for (; startindex < keys.Count && sb.Length + individualkeys.Count * 8 < maxlength; startindex++)
     {
        int key = keys[startindex];
        if (key > max+MAX_NUMBER_OF_EXTRA_OBJECTS_TO_FETCH)
        {
           if (min == max)
              individualkeys.Add(min);
           else
           {
              if(sb.Length > 2)
                 sb.Append(" OR ");
              sb.AppendFormat(betweenAnd, colname, min, max);
           }
           min = max = key;
        }
        else
        {
           max = key;
        }
     }
     if (min == max)
        individualkeys.Add(min);
     else
     {
        if (sb.Length > 2)
           sb.Append(" OR ");
        sb.AppendFormat(betweenAnd, colname, min, max);
     }
     if (individualkeys.Count > 0)
     {
        if (sb.Length > 2)
           sb.Append(" OR ");
        string[] individualkeysstr = new string[individualkeys.Count];
        for (int i = 0; i < individualkeys.Count; i++)
           individualkeysstr[i] = individualkeys[i].ToString();
        sb.AppendFormat("{0} IN ({1})", colname,  String.Join(",",individualkeysstr));
     }
     sb.Append(")");
     return sb.ToString();
  }

然后像这样使用:

 List<int> keys; //Sort and make unique
 ...
 for (int i = 0; i < keys.Count;)
 {
    string idstring = MassIdSelectionStringBuilder(keys, ref i, "id");
    string sqlstring = string.Format("SELECT * FROM table WHERE {0} OPTION (RECOMPILE)", idstring);

不过，我的问题是……有谁知道更好/更快/更智能的方法吗？

Answer 1

根据我的经验，最快的方法是将二进制格式的数字打包到图像中。我发送了多达 10 万个 ID，效果很好:

Mimicking a table variable parameter with an image

但那是很久以前的事了。 Erland Sommarskog 的以下文章是最新的:

Arrays and Lists in SQL Server