What is SQL Optimization ? SQL optimization refers to the process of improving the performance of SQL (Structured Query Language) queries by identifying and addressing issues such as slow running queries, high resource consumption, and poor indexing. This can be achieved through techniques such as indexing, partitioning, and rewriting queries to make them more efficient. Additionally, database administrators can use tools such as explain plans and performance monitoring software to identify and analyze SQL performance issues and determine the best course of action for optimization.
Example of a query that can be optimized is a simple SELECT statement that retrieves all rows from a large table without an index on the column(s) used in the WHERE clause
SELECT * FROM orders WHERE customer_id = '12345';
This query retrieves all rows from the "orders" table where the "customer_id" column is '12345'. Without an index on the "customer_id" column, the database must scan the entire "orders" table, which can take a long time if the table is large.
An optimized version of this query would be to create an index on the "customer_id" column:
CREATE INDEX idx_customer_id ON orders (customer_id);
With this index in place, the query will now use the index to quickly locate the rows matching the WHERE clause, rather than scanning the entire table. This can greatly improve the performance of the query.
Another optimization that can be done is instead of SELECT * use selective columns name that you are looking for,
SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = '12345';
Use
regexp_like to replace
Like
SELECT *
FROM
tablel
WHERE
lower (item_name) LIKE '%samsung%' OR
lower(item_name) LIKE '%xiaomi%' OR
lower (item_name) LIKE '%iphone%' OR
lower(item_name) LIKE '%huawei%'
--and so on | SELECT) *
FROM
tablel
WHERE
REGEXP_LIKE (lower (item_name),
'samsung| xiaomi | iphone | huawei') |
Use
'regexp_extract' to replace
'Case-when Like'
SELECT
CASE
WHEN concat(' ',item_name,' ') LIKE '%acer%' then 'Acer'
WHEN concat(' ',item_name,' ') LIKE '%advance%' then 'Advance'
WHEN concat(' ',item_name,' ') LIKE '%alfalink%' then 'Alfalink'
AS brand
FROM item_list | SELECT
regexp_extract(item_name, '(asus|lenovo|hp|acer|dell|zyrex|...)')
AS brand
FROM item_list
|
Use Convert long list of IN clause into a temporary table
SELECT *
FROM Tablel as t1 WHERE itemid in(3363134,5189076) | SELECT * FROM Tablel as t1
JOIN (SELECT itemid FROM (SELECT
split('3363134, 5189076')
as bar)CROSS JOIN
UNNEST (bar) AS t(itemid)) AS Table2 as t2
ON
tl.itemid = t2.itemid
|
Use Always order your JOINs from largest tables to
SELECT * FROM small_table JOIN large table
ON small _table.id = large_table.id | SELECT * FROM large_table JOIN small_table
ON small _table.id = large_table.id
|
Use
simple equi-joins
Two tables with date string e.g., '2023-03-17', but one of the tables only has columns for year, month,day
SELECT * FROM tablel a JOIN table2 b ON a.date = CONCAT(b.year, '-', b.month, '-', b.day) | SELECT * FROM tablel a JOIN(Select name, CONCAT(b.year, '-', b.month, '-', b.day) as date from table2 b)new
ON a.date = new.date
|
Use Always "GROUP BY" by the attribute/column with the largest number of unique entities/values
select
main_category,
sub_category,
itemid,
sum(price)
from
tablel
group by
main_category, sub_category, itemid | select
main_category,
sub_category,
itemid,
sum(price)
from
tablel
group by
itemid, sub_category, main_category
|
Use
Avoid subqueries in WHERE clause
select
sun(price)
from
tablel
where
itemid in(
select itemid
from table2) | with t2 as (
select itemid
from table2
)
select
sum(price)
from
tablel as t1
join
t2
on tl.itemid = t2.itemid
|
Use
Max instead of Rank
SELECT * from(select userid,
rank() over (order by prdate desc) as rank
from tablel
)
where ranking = 1
| SELECT userid, max(prdate)
from tablel
group by 1
|
Other Tips
Use approx_distinct() instead of count(distinct) for very large
datasets .
Use approx_percentile(metric, 0.5) for median
Avoid UNIONs where possible.
Use WITH statements vs. nested subqueries.
Please visit for More info about
Improve SQL Server Database Performance
Example of a query that can be optimized is a simple SELECT statement that retrieves all rows from a large table without an index on the column(s) used in the WHERE clause
No comments: