我有一个数据库，其中的表包含大约2.500.000条记录。我正在获取大约150.000条记录，并使用2个不同的查询进行测试。第一个返回的结果在30秒到1分钟之间。但是第二个响应在3-4分钟之间响应，这很奇怪。唯一的变化是，第一个不使用参数，但第二个使用参数。我都从C＃运行。对于安全性问题，我想使用带参数的参数，但是我不明白为什么要花这么长时间。任何帮助将不胜感激。
第一个查询：
DECLARE @page INT=3
DECLARE @pagesize INT=300
string sql = "SELECT Col1,Col2,Col3 FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Col1) AS rownumber,Col1,Col2,Col3";
sql += " FROM my_table WHERE Col1 LIKE '" + letter + "%') as somex
WHERE rownumber >= (@page-1)*(@pagesize)";
sql += "AND rownumber <=(@page)*@pagesize;
SELECT COUNT(*) FROM my_table WHERE col1 LIKE '" + letter + "%'";
第二个查询：
DECLARE @page INT=3
DECLARE @pagesize INT=300
DECLARE @starting VARCHAR(10)='be'
string sql = "SELECT Col1,Col2,Col3FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Col1) AS rownumber,Col1,Col2,Col3";
sql += " FROM my_table WHERE Col1 LIKE @letter+'%') as somex
WHERE rownumber >= (@page-1)*(@pagesize)";
sql += "AND rownumber <=(@page)*@pagesize; SELECT COUNT(*)
FROM my_table WHERE col1 LIKE @letter+'%'";
我的服务器是16GB Ram，4个真正的4个虚拟CPU，Sata磁盘。
编辑： Col1是群集和非群集索引。
进度：事实证明，这些查询在另一台服务器上运行良好。但是，这让我更加困惑。可能是SQL Server的某些设置吗？
正如我在评论中说的那样，这听起来像参数嗅探，但是出于帮助的目的，我认为我会对此进行扩展。Web上有许多文章比我要详细得多，但是参数嗅探的长短是SQL-Server缓存了基于不产生参数的参数值的执行计划。当前值的最佳执行计划。
假设其上Col1具有非聚集索引，但不包含col2或col3作为非关键列，则SQL-Server具有两个选项，它可以对聚集索引进行扫描My_Table以获取其中的所有行Col1 LIKE @letter+'%'，或者可以对进行搜索，Col1然后执行在聚集索引上进行书签查找，以获取索引返回的每一行的值。我不太想起SQL-Server基于估计的行数在哪一点之间切换的那一点，它的百分比非常低，因此，我相当确定如果您要返回150,000条记录对于2,500,000个优化器，它将进行聚簇索引扫描。但是，如果只返回几百行，则最好使用书签查找。
当您不使用参数时，SQL-Server将在每次执行时创建一个新的执行计划，并为该参数生成最佳执行计划（假设您的统计信息是最新的）（当您第一次使用参数时）他们查询运行sql-server基于该特定参数值创建一个计划，并存储该计划以供以后使用。以后每次运行查询时，sql-server都会识别该查询是相同的，因此不会重新编译它。这意味着，如果第一次运行查询是针对返回较少行数的参数，则将存储书签查找计划。然后，如果下一次运行查询，则传递一个返回大量行的值（其中最佳计划是聚簇索引扫描），那么该查询仍将使用次优书签查找执行，这将导致更长的执行时间。相反，这当然也是正确的。解决参数嗅探的方法有很多种，但是由于您的查询不是很复杂，因此编译时间不会很长，尤其是与您说此查询即使在最佳状态下要花30秒运行相比，也是如此。会使用OPTION RECOMPILE 查询提示：
SELECT Col1, Col2, Col3
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Col1) AS rownumber,Col1,Col2,Col3
FROM my_table
WHERE Col1 LIKE @letter+'%'
) as somex
WHERE rownumber >= (@page-1)*(@pagesize)
AND rownumber <= (@page) * @pagesize
OPTION (RECOMPILE);
SELECT COUNT(*)
FROM my_table
WHERE Col1 LIKE @letter+'%'
OPTION (RECOMPILE);
当您在新服务器上尝试执行此命令时，它执行得很好的原因是，它第一次在新服务器上运行时，必须编译参数化查询，并且生成的计划适合提供的参数值。
最后一点，如果您使用的是SQL_Server 2012，则可以使用OFFSET / FETCH进行分页：
SELECT Col1, Col2, Col3
FROM My_table
WHERE Col1 LIKE @letter+'%'
ORDER BY Col1 OFFSET (@page-1) * (@pagesize) ROWS FETCH NEXT @pagesize ROWS ONLY;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句