Database servers have to resolve schema fields when using asterisk symbol (*). Knowing and using the schema saves CPU cycles and network transfer.

Non compliant Code Example

public void foo() {
    ...
    String baseQuery = "SELECT * FROM users"; // Noncompliant
    ...
}

Compliant Solution

public void foo() {
    ...
        String query = "SELECT id, name, address FROM users ";
    ...
}

Our Analysis

The following results were obtained through local experiments.

Configuration

  • SQLite Database: 5-6 GB

  • Processor: Intel® Core™ Ultra 5 135U, 2100 MHz, 12 cores, 14 logical processors

  • RAM: 16 GB

  • CO2 Emissions Measurement: Using CodeCarbon

Context

SQL also includes many optimization mechanisms, for example, if a table/column is queried frequently, then this column will be indexed and subsequent queries will perform better. The results of the study would show that using SELECT * SQL will disable optimization mechanisms.

Impact Analysis

First, let’s analyze the impact that the number of selected columns has on RAM consumption, execution time, and CO2 emissions.

For the same number of rows, for the following query: SELECT {selected_cols} FROM my_table where col10=random_word

CO2 Emissions

tracking 1

Memory Usage

tracking 2

Execution Time

tracking 3

We can conclude that it is not recommended to use SELECT *, even if we want all the columns. We need to specify them because when we use *, SQL generally avoids using the already created indexes, which greatly increases the computation time.