Which is faster for the following 2 SQL queries? Does it slow the calculation if SUM(Sales)
is used multiple times, or it will just be auto optimized?
SQL query #1:
SELECT
Category,
CASE
WHEN SUM(Sales) > 1000000 THEN SUM(Sales) / 1.3
ELSE SUM(Sales)
END AS Total
FROM
MyTable
GROUP BY
Category
SQL query #2:
SELECT
Category,
CASE
WHEN Sales > 1000000 THEN Sales / 1.3
ELSE Sales
END AS Total
FROM
(SELECT
Category, SUM(Sales) AS Sales
FROM
MyTable
GROUP BY
Category) AS p
1条答案
按热度按时间14ifxucb1#
Doesn't matter at all. A query language like SQL is not an execution language, but only a language that you write the "form" of data you want to have in the result set, not how to obtain it.
The algebrizer and the optimizer will write a program call "execution plan" in which all operations (steps) will be executed. The query engine, will write an "operating" tree compound of many mathematical operations of the relational algebrae ...
When you solve mathematical formulae you will first factor all expression that have the same content...
So the fact that you factor the "SUM(Sales)" has no importance at all. In good RDBMS this will be done systematically by the optimizer...