Can a worse cost query be a better performance query?

Asked

Viewed 815 times

1

Context

Here at 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 on Sqlserver.

The performance of this query was bad and with the growth of the tables, the DBA warned us to reevaluate the query because it is becoming very expensive for the bank.

When I started the query had a cost [Estimated Subtreecost = 19,891], from there I started to evaluate and remove some optional fields, left unnecessary joins, subselects of the projection and the cost increased instead of decreasing [Estimated Subtreecost = 20,232]

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

Question

I am not very experienced in evaluating plans of execution of queries and from there came a doubt, the cost is based on the amount of data returned? at run time? at cost to the bank?

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

Note: I cannot share the query because it is confidential data and the query is very complex and it would be difficult to create a similar fake query

sql-server query performance
  • 2

    "There are some common false assumptions about the estimated cost of execution. In particular, people often assume that the estimated cost of execution is a good indicator of how long the query takes to be executed and that this estimate allows differentiating good plans from bad plans. But this is not true.": http://technet.microsoft.com/pt-br/magazine/2007.11.sqlquery.aspx The article is very informative. The cropped part is just a decoy :-)

2 answers

2


The Estimated Subtreecost is individual and exists for each operator created in the execution plan, so the increase in the estimate of this specific operator does not mean a heavier query, but only an increase in the consumption of the substree of this specific index. It may be, for example, that removing the optional parts increases the amount of data obtained by this subtree, but generally reduces the total cost of the query for the database.

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

2

The cost is just an estimate... Personally I do not care much for 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 that I pay attention to when I want to decrease the execution time, which is the "Estimated Operator Cost" of joins and Dexes. Decreasing the cost of these operators has an almost always direct impact on the decrease of the execution time. I hope I’ve helped.

Browser other questions tagged sql-server query performance

You are not signed in. Login or sign up in order to post.