Featured Post

SQL Query: 3 Methods for Calculating Cumulative SUM

SQL provides various constructs for calculating cumulative sums, offering flexibility and efficiency in data analysis. In this article, we explore three distinct SQL queries that facilitate the computation of cumulative sums. Each query leverages different SQL constructs to achieve the desired outcome, catering to diverse analytical needs and preferences. Using Window Functions (e.g., PostgreSQL, SQL Server, Oracle) SELECT id, value, SUM(value) OVER (ORDER BY id) AS cumulative_sum  FROM your_table; This query uses the SUM() window function with the OVER clause to calculate the cumulative sum of the value column ordered by the id column. Using Subqueries (e.g., MySQL, SQLite): SELECT t1.id, t1.value, SUM(t2.value) AS cumulative_sum FROM your_table t1 JOIN your_table t2 ON t1.id >= t2.id GROUP BY t1.id, t1.value ORDER BY t1.id; This query uses a self-join to calculate the cumulative sum. It joins the table with itself, matching rows where the id in the first table is greater than or

Five top SQL Query Performance Tuning Tips

SQL query runs faster when you write it in a specific method. You can say it as tuning. There are five tuning tips:

List of Performance Tuning Tips

  1. use index columns,
  2. use group by,
  3. avoid duplicate column in SELECT & Where,
  4. use Left Joins
  5. use a co-related subquery.
Five top SQL Query Performance Tuning Tips
Five top SQL Query Performance Tuning Tips

SQL Performance Tuning Tip: 01

Use indexes in the where clause of SQL. Let me elaborate more on that. Be sure the columns that you are using in the WHERE clause should be already part of the Index columns of that database Table.

An example SQL Query:

FROM emp_sal_nonppi
WHERE dob <= 2017-08-01;

SQL Performance Tuning Tip: 02

Use GROUP BY. Some people use a DISTINCT clause to eliminate duplicates. You can achieve this by GROUP BY.

An example SQL Query:

SELECT E.empno, E.lastname
FROM emp E,emp_projact EP
WHERE E.empno = EP.empno
GROUP BY E.empno, E.lastname;

SQL Performance Tuning Tip: 03

Avoid using duplicates in the Query. Some people use the same column name in both WHERE & SELECT statements, which is not a good practice since it takes more CPU time.

An example SQL Query:

SELECT dept-no, dept-name
FROM dept
WHERE (admrdept = 'E01' OR deptno = 'D01')
AND deptname = 'MAIN BRANCH';

SQL Performance Tuning Tip: 04

There are occasions you need data from more than one Table. You can achieve this by joining two or more tables.

Left Join is much faster than Right join. See the Sets concept. There are many kinds of joins. Out of these LEFT and RIGHT outers popular in SQL. The SQL parser converts all types of joins to the left outer joins. So to avoid extra CPU time prefer to use LEFT Joins, which are faster.

An example SQL Query: 

SELECT column_names FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Tuning tips
Tuning tips

SQL Performance Tuning Tip: 05

This idea is from SQL gurus that always use index columns in correlated-Subquery. In any SQL query, you can use normal sub-queries and Correlated-subqueries. 

If a sub-query is referring to the columns of the Outer query, then you can say that query as correlated-subquery. Otherwise, it is a normal sub-query. The columns referenced in correlated-subquery should be index columns.

An example SQL Query:

SELECT last_name, first_name
FROM employee e1
FROM payment_history ph
WHERE ph.employee_id = e1.employee_id
AND ph.payment_type = 'award';


Popular posts from this blog

How to Fix datetime Import Error in Python Quickly

Explained Ideal Structure of Python Class

How to Check Kafka Available Brokers