特定查询直接运行良好，或者在对其进行任何更改时运行良好，但在当前状态下需要更长的时间才能运行

发布于 2024-10-14 08:54:13 字数 1162 浏览 1 评论 0原文

经过几天的调试后的主要更新：

我运行了一些类似于以下的查询：

SELECT RTRIM(part) as part 
FROM tableP pm 
LEFT join tableS s on pm.id = s.id 
INNER JOIN tableC cm ON cm.id = pm.id 
WHERE name = 'NGW'  AND  status NOT IN ('NL', 'Z') 
GROUP BY RTRIM(part), isnull(s.value,0) 
ORDER BY isnull(s.value,0)

它在 Java 中运行如下：

    PreparedStatement select = con.prepareStatement(
        "SELECT RTRIM(part) as part" +
        "FROM tableP pm " +
        "LEFT JOIN tableS s ON pm.id= s.id " +
        "INNER JOIN tableC cm ON cm.id= pm.id " +
        "WHERE name =? AND  status NOT IN ('NL', 'Z') " + 
        "GROUP BY RTRIM(part), isnull(s.value,0) " +
        "ORDER BY isnull(s.value,0) " );
    select.setString(1, name);
    ResultSet rs = select.executeQuery();
    while(rs.next()){
          ... Data is Loaded...

查询在 Java 应用程序内部运行良好。突然间，这种形式的 3 或 4 个查询从不到一秒变成了一分钟多。

我已经从 SQL Profiler 复制了精确的查询，当直接在数据库上运行时，它会在不到一秒的时间内执行。我开始对查询进行更改，发现对查询的任何更改都会将其恢复到 1 秒性能，甚至在语句之间添加一个空格。但一旦我将其恢复到原来的精确状态，就需要 60 多秒的时间。

核心问题： 所以，我有一个解决方案，但是即使只有空格这样小的更改，什么也会导致查询以不同的方式运行？

原文

Major Update after a couple days of debugging:

I run a few queries similar to :

SELECT RTRIM(part) as part 
FROM tableP pm 
LEFT join tableS s on pm.id = s.id 
INNER JOIN tableC cm ON cm.id = pm.id 
WHERE name = 'NGW'  AND  status NOT IN ('NL', 'Z') 
GROUP BY RTRIM(part), isnull(s.value,0) 
ORDER BY isnull(s.value,0)

It is run in Java like so:

    PreparedStatement select = con.prepareStatement(
        "SELECT RTRIM(part) as part" +
        "FROM tableP pm " +
        "LEFT JOIN tableS s ON pm.id= s.id " +
        "INNER JOIN tableC cm ON cm.id= pm.id " +
        "WHERE name =? AND  status NOT IN ('NL', 'Z') " + 
        "GROUP BY RTRIM(part), isnull(s.value,0) " +
        "ORDER BY isnull(s.value,0) " );
    select.setString(1, name);
    ResultSet rs = select.executeQuery();
    while(rs.next()){
          ... Data is Loaded...

The queries have been running fine inside of a Java application. Suddenly 3 or 4 queries of this form went from taking less then a second to over a minute.

I have copied the exact query from SQL Profiler and when run directly on the database it preforms in less then a second. I started to make changes to the query and found any change to the query would return it to 1 second performance even adding a single space between a statement. But as soon as I returned it to its original exact state it would take 60+ seconds.

Core Question:
So, I have a fix, but what could cause a query to run differently even with just a change as small as whitespace?

分享到QQ

分享到微博