CODEWITHSUNDEEP
sqldb2sql-execution-plan
T.A
T.A

Reputation: 65

SQL Query with lower costs runs slower than queries with way higher cost

I ran the execution plan on a SQL query on a DB2 database for two queries. The first query has a cost of about 380000. After modifying the query by replacing some inner joins with subqueries, the cost reduced to 312 (note: not 312000, just 312)

However, after running each query multiply times, the larger query runs quicker on average. What could be the reason for this?

Upvotes: 1

Views: 2306

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

For any database that uses a cost-based optimizer, cost is an estimate. If the estimate is correct, there should be a correlation between the cost of the query and the runtime. Sometimes, however, the estimate is way off. And in general you're far more likely to be looking at a query where the optimizer's estimates are way off because when the estimates are way off the plan is very likely to be bad, the query is going to be running slowly, and someone is going to complain. People generally don't look at the 99% of queries where the optimizer's estimates are correct.

It sounds like the optimizer's cost estimate is way off in this case. Most likely, that is a result of statistics being incorrect on some table, index, or column. Of course, there could be other issues-- it might expect that the fraction of rows in cache is wildly off or that disk I/O is much more or less expensive than it really is (i.e. your I/O subsystem is under heavy pressure and is slow or you have everything on solid state disk so I/O is really fast). But I'd always start with looking at statistics.

Upvotes: 2

Related Questions