休眠 SQL In 子句使 CPU 使用率达到 100%

performance sql-server java hibernate ejb

2022-09-02 12:56:39

在我的Java应用程序中，我使用的是.当我尝试执行选择查询时，数据库服务器 CPU 使用率达到 100%。但是当我尝试在中运行相同的查询时，查询在没有任何 CPU 峰值的情况下运行。应用程序服务器和数据库服务器是两个不同的机器。我的表具有以下架构，SQL server and Hibernate3 with EJBwith In clauseSQL management studio

CREATE TABLE student_table (
       Student_Id BIGINT NOT NULL IDENTITY
     , Class_Id BIGINT NOT NULL
     , Student_First_Name VARCHAR(100) NOT NULL
     , Student_Last_Name VARCHAR(100)
     , Roll_No VARCHAR(100) NOT NULL
     , PRIMARY KEY (Student_Id)
     , CONSTRAINT UK_StudentUnique_1 UNIQUE  (Class_Id, Roll_No)
);

该表包含大约 1000k 条记录。我的查询是

select Student_Id from student_table where Roll_No in ('A101','A102','A103',.....'A250');

In 子句包含 250 个值，当我尝试在 SQL 管理工作室中运行上述查询时，结果将在 1 秒内检索到，并且没有任何 CPU 峰值。但是，当我尝试通过休眠运行相同的查询时，CPU峰值在大约60秒内达到100%，结果在大约60秒内被检索。休眠查询为：

Criteria studentCriteria = session.createCriteria(StudentTO.class);
studentCriteria.add(Restrictions.in("rollNo", rollNoLists)); //rollNoLists is an Arraylist contains 250 Strings
studentCriteria.setProjection(Projections.projectionList().add(Projections.property("studentId")));
List<Long> studentIds = new ArrayList<Long>();
List<Long> results = (ArrayList<Long>) studentCriteria.list();
if (results != null && results.size() > 0) {
   studentIds.addAll(results);
}
return studentIds;

为什么会这样，问题是什么。如果同一查询正在管理工作室中运行，则检索结果时不会出现任何峰值，并在 1 秒内检索结果。任何解决方案???

编辑1：我的休眠生成的查询是，

select this_.Student_Id as y0_ from student_table this_ where this_.Roll_No in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

编辑2：我的执行计划这是在索引roll_no

CREATE INDEX i_student_roll_no ON student_table (Roll_No)

My execution plan ,

答案 1

从控制台运行的查询很容易缓存，这就是响应是即时响应的原因。如果查看查询，您将看到所有参数都嵌入在查询中，因此查询计划程序可以检测到没有变化，并且所有执行将始终转到同一计划和相同的缓存结果。

使用 Hibernate 运行的查询，即使它是本机查询，它也使用 a 和参数在查询执行时绑定，并引用索引的最佳作者之一：PreparedStatement

这与绑定参数有什么关系？

DB2、Oracle 和 SQL Server 的共享执行计划高速缓存使用文本 SQL 字符串的哈希值作为高速缓存的键。如果 SQL 包含随每次执行而变化的文本值，则找不到缓存的计划。

占位符（绑定参数）统一语句，以便在使用不同值执行 SQL 字符串时相同，从而提高缓存命中率。

要解决此问题，您需要在两个（，）列上添加一个索引，以便查询成为仅索引扫描。Roll_NoStudent_Id

SQL Server 默认使用群集索引，这会将每个表限制为一个聚集索引，因此您可能希望将此表转换为堆表，并专注于仅索引扫描。

答案 2

若要回答“为什么休眠时速度很慢”的问题，需要查看服务器在运行休眠代码时使用的实际执行计划，而不是服务器从 SSMS 运行查询时使用的执行计划。问题中包含的执行计划的屏幕截图看起来与运行休眠代码时获得的实际计划不同。获得该计划后，可以将其与从 SSMS 获得的计划进行比较，这种差异很可能会解释为什么在一种情况下它很慢，而在另一种情况下速度很快。

Erland Sommarskog有一篇非常好的文章，它专注于所谓的“参数嗅探”，这可能是这里出现问题的原因，但不太可能。在本文中，对我们有用的是，他解释了如何从缓存中提取执行计划以进行检查。

没有这些信息，你只能猜测。一种猜测是，您将参数作为传递，但索引字段是，因此不使用索引。服务器将您的列转换为进行比较，这意味着索引不能使用 =>它很慢，并且转换可能是 CPU 使用率高的原因。http://sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/nvarcharRoll_Novarcharvarcharnvarchar

这不是您问题的答案，而是问题的可能解决方案。不要将 250 个单独的参数传递给子句的查询，而是使用表值参数并将值作为表传递。在查询中使用代替。在您的评论之后尤其如此，您将拥有100K个参数（这意味着您希望运行查询400次）。事实上，即使对于表值参数，100K也有点太多了，所以我会考虑有一个永久或临时的帮助器表，它将使用适当的索引来保存这些帮助器。主要查询将针对它。像这样：INJOININRoll_NoJOIN

CREATE TABLE RollNumbers (
     Roll_No VARCHAR(100) NOT NULL
     ,PRIMARY KEY (Roll_No)
);

确保上的表中有索引。确保上的表中有索引。首先将 100K 值放入，然后在主查询中使用它们：RollNumbersRoll_Nostudent_tableRoll_NoINSERTRollNumbers

SELECT Student_Id 
FROM
    student_table
    INNER JOIN RollNumbers ON RollNumbers.Roll_No = student_table.Roll_No

根据整个系统的不同，表可以是永久表、临时表或表变量。RollNumbers