Can a worse-cost query be a better-performing query?

Question

Can a worse-cost query be a better-performing query?

Navigation

#1 by (2 votes)
#2 by (2 votes)

1

Context

Here in the company we have a very complex query that encompasses several business rules, for presentation in a WorkFlow and for now I am evaluating it in SQLServer .

The performance of this query was poor and as the tables grew, the DBA warned us to re-evaluate the query as it is getting very costly for the database.

When I started the query it had a cost [Estimated SubTreeCost = 19.891] , from there I started evaluating and removing some optional fields, unnecessary left joins, projection subselects and cost increased instead of Decrease [Estimated SubTreeCost = 20.232]

When I run the query reduced, I had the result in 23s and the original query (with lower cost) 1min18s

Question

I'm not very experienced with evaluating plans for executing queries and hence came a doubt, the cost is based on the amount of data returned? at run time? in the cost to the bank?

Can the higher-cost query be better than the lower-cost original?

I can not share the query because it is a sensitive data and the query is very complex and it would be difficult to try to create a similar fake query

desempenho sql-server query

asked by anonymous 25.09.2014 / 22:59

2 answers

2

Cost is just an estimate ... Personally I do not care much about this cost but rather the execution time. For me the most important thing is to return the information as soon as possible. However, there is a cost to which I pay attention when I want to reduce the execution time, which is the "Estimated Operator Cost" of joins and indexes. Decreasing the cost of these operators has an almost always direct impact on reducing runtime. I hope I have helped.

26.09.2014 / 13:03

Histogram with a text column [pending] javascript syntax error

score 2 · Accepted Answer

The Estimated SubTreeCost is individual and exists for each operator created in the execution plan, so the increase in the estimate of this particular operator does not mean a heavier query, but only an increase in the substance's consumption of this particular index. It may be, for example, that removing the optional parts increases the amount of data obtained by this subtree, but generally decreases the total cost of the query to the bank.

If possible, always run the query, marking to include the current data plan (query -> Include Actual Execution Plan). The estimate often does not match what SQL will actually use.