Tips para bumilis ang mga queries sa SQL/PLSQL

[avatara07]

Hello mga ka-Symb!
Note na madaming ways upang bumilis ang ating mga queries, kabilang na jan ang tamang paggamit ng index, normalization, etc. Pero mas makatutulong din na mapanatili ang optimal performance ng ating mga queries kung magagamit din natin nang maayos ang mga commands lalo na sa sequence ng queries. Narito ang ilan sa mga best practices para sa mga queries na ginagamit natin sa SQL/ PLSQL. Sana makatulong sa inyo

#1 Use ‘regexp_extract’ to replace ‘Case-when Like’

Instead of: 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

Use: SELECT regexp_extract(item_name,'(asus|lenovo|hp|acer|dell|zyrex|...)') AS brand FROM item_list

#2 Always order your JOINs from largest tables to smallest tables

Instead of: SELECT * FROM small_table JOIN large_table ON small_table.id = large_table.id

Use: SELECT * FROM large_table JOIN small_table ON small_table.id = large_table.id

#3 Use ‘regexp_like’ to replace ‘LIKE’ clauses

Instead of: SELECT * FROM table1 WHERE lower(hero_name) LIKE '%layla%' OR lower(hero_name) LIKE '%miya%' OR lower(hero_name) LIKE '%hanabi%' OR lower(hero_name) LIKE '%zhask% ....

Use: SELECT * FROM table1 WHERE REGEXP_LIKE(lower(hero_name),'layla|miya|hanabi|zhask')

#4 Always "GROUP BY" by the attribute/column with the largest number of unique entities/values

Instead of: select main_category, sub_category, itemid, sum(price) from table1 group by main_category, sub_category, itemid

Use: select main_category, sub_category, itemid, sum(price) from table1 group by itemid, sub_category, main_category

#5 Avoid using SUBQUERIES in your WHERE clause

Instead of: select sum(price) from table1 where itemid in ( select itemid from table2 )

Use: with t2 as ( select itemid from table2 ) select sum(price) from table1 as t1 inner join t2 on t1.itemid = t2.itemid

Syempre madami pang iba. If may gusto kayong i-share na best practice, feel free to comment on this thread na din