- Messages
- 98
- Reaction score
- 1
- Points
- 28
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’
#2 Always order your JOINs from largest tables to smallest tables
#3 Use ‘regexp_like’ to replace ‘LIKE’ clauses
#4 Always "GROUP BY" by the attribute/column with the largest number of unique entities/values
#5 Avoid using SUBQUERIES in your WHERE clause
Syempre madami pang iba. If may gusto kayong i-share na best practice, feel free to comment on this thread na din
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
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