EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

Documenting constants in query

We all know that using magic numbers in code is a bad practice. So what's a best way to give a meaning to some constant number in query ? Usual way would be to just write a comment like so:

select * from
(
    select n, n % 2 rem from
    (
        select 1 n
        union all
        select 2
        union all
        select 3
        union all
        select 4
    ) tmp
) tmp2
where tmp2.rem = 0 /* Only Even Numbers */

But we can also do like that:

declare @onlyEvenNumbers int 
set @onlyEvenNumbers = 0

select * from
(
    select n, n % 2 rem from
    (
        select 1 n
        union all
        select 2
        union all
        select 3
        union all
        select 4
    ) tmp
) tmp2
where tmp2.rem = @onlyEvenNumbers

Or simply:

select * from
(
    select n, n % 2 rem from
    (
        select 1 n
        union all
        select 2
        union all
        select 3
        union all
        select 4
    ) tmp
) tmp2
where tmp2.rem = (select 0 as onlyEvenNumbers)

Given that sometimes queries can be very long and one line can consist of many constant numbers - What's a best way to document these magic numbers so that usual developer could understand query faster ?

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Avoid Subqueries (query line: 23): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.

The optimized query:

SELECT
        * 
    FROM
        (SELECT
            n,
            n % 2 rem 
        FROM
            (SELECT
                1 n 
            UNION
            ALL SELECT
                2 
            UNION
            ALL SELECT
                3 
            UNION
            ALL SELECT
                4
        ) tmp
) tmp2 
WHERE
tmp2.rem = (
    SELECT
        0 AS onlyEvenNumbers
)

Question 2

EverSQL Database Performance Knowledge Base

Documenting constants in query

How to optimize this SQL query?

The optimization process and recommendations:

The optimized query:

Related Articles