Specific Query runs fine directly or when any change to it is made but in current state takes longer to run
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 li开发者_如何学Cke 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?
Is it possible that the execution plan is corrupted? Can you try explicitly clearing the plan cache? http://msdn.microsoft.com/en-us/library/aa175244(v=sql.80).aspx
精彩评论