New Rule suggestion : Optimize Database Queries

[tazemar]

New Rule : Optimize Database Queries

https://github.com/cnumr/best-practices/blob/main/chapters/BP_075_en.md

PR : https://github.com/green-code-initiative/ecoCode/pull/221

Use LIMIT Clause on SQL Queries. Other techniques are possible but we cannot verify with static analysis.

Rule complete description

Text

Databases are typically essential application components. As many queries are used to retrieve and store data, they end up having a significant impact on the solution's resource use when executed frequently.

With this in mind, it is important to pay attention for these queries and ensure, at least for the most expensive ones that they are well optimized.

The most common optimization tips are:

• Use less data and limit it to the bare minimum. For example, the LIMIT clause limits the number of result rows in relational databases. When possible, using the 'LIMIT' clause reduces the amount of transferred data. Performance gains will be even more significant if records contain a large number of voluminous fields.

• Only use necessary fields in the tables or documents in order to avoid needlessly transferring data that will not be used and to avoid using database server and application server resources to manipulate them.

• Add indexes on fields used as keys in your model. Adding them can completely change queries performance. Be careful: adding indexes makes writing longer as it must be updated for each added, modified or deleted document. This must be done if there are more reads than writes or if reads are particularly expensive.

• Use database management system tools to analyze queries in order to identify improvement areas, such as EXPLAIN for RDBMS.

• Cache the most expensive queries results as well as data that changes little or never (reference data).

• Optionally, modifying data models to be able to access information more easily without joins (denormalization)

Optimized SQL: Optimize Database Queries

Platform

OS	OS version	Langage
-	-	Java, Javascript, Php, Python

Main caracteristics

ID	Title	Category	Sub-category
X	Optimize Database Queries	Environment	Optimized SQL

Severity / Remediation Cost

Severity	Remediation Cost
Minor	Minor

Rule short description

Limit your Database Queries. You should use the "LIMIT" SQL element.

Rule complete description

Text

Use LIMIT on SQL request to control the size of your exchange.

HTML

<p></p>
<h2>Noncompliant Code Example</h2>
<pre>
 String sql = "SELECT user FROM myTable";
</pre>
<h2>Compliant Solution</h2>
<pre>
String sql = "SELECT user FROM myTable LIMIT 50";
</pre>

[dedece35]

Hi @tazemar, thank you for issue. It's a good idea. Please take into account my review feedback on your PR.