Şub
24
2014

Basit SQL Tuning/SQL Optimization

1) Sql sorgusunda ‘*’ yerine gerçek kolon adları kullanmak daha hızlı sonuçlar vericektir.

SELECT id, first_name, last_name, age, subject FROM student_details;
——-
SELECT * FROM student_details;

2) HAVING tüm satırlar seçildikten sonra filtreleme yapar. Gereksiz kullanımdan kaçınmalıyız.

SELECT subject, count(subject)
FROM student_details
WHERE subject != ‘Science’
AND subject != ‘Maths’
GROUP BY subject;
————
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= ‘Vancouver’ AND subject!= ‘Toronto’;

3)Bazen iç içe sorgular yazmamız gerekebilir.Mümkün olduğunca az iç sorgu kullanmak performans açısından önemlidir.

SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = ‘Electronics’;
——————–
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = ‘Electronics’;

4) IN Yerine EXISTS kullanmaya çalışmalıyız.
a) Genellikle IN performansı yavaşlatır.
b) Filtre kriterlerin en alt sorguda olduğunda IN etkilidir.
c) Filtre kriterlerin çoğu ana sorguda olduğunda EXISTS etkilidir.

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
——————————
Select * from product p
where product_id IN
(select product_id from order_items)

5) DISTINC yerine EXISTS kullanmaya çalışmalıyız.

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT ‘X’ FROM employee e WHERE e.dept = d.dept);
——————————
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) UNION Yerine UNION ALL kullanımına önem göstermeliyiz.

SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
——————–
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7) Where kuşulundaki durumlara dikkat etmeliyiz..
For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;
———————–
SELECT id, first_name, age FROM student_details WHERE age != 10;

****************************************

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE ‘Chan%’;
————————-
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = ‘Cha’;

****************************************

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, ‘%’);
————————-
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

****************************************

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
————————-
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

****************************************

SELECT id, name, salary
FROM employee
WHERE dept = ‘Electronics’
AND location = ‘Bangalore’;
————————-
SELECT id, name, salary
FROM employee
WHERE dept || location= ‘ElectronicsBangalore’;

****************************************

SELECT id, name, salary
FROM employee
WHERE salary < 25000;
————————-
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

****************************************

SELECT id, first_name, age
FROM student_details
WHERE age > 10;
————————-
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Gereksiz DECODE kullanımından kaçınmalıyız.

SELECT id FROM employee
WHERE name LIKE ‘Ramesh%’
and location = ‘Bangalore’;
————————-
SELECT DECODE(location,’Bangalore’,id,NULL) id FROM employee
WHERE name LIKE ‘Ramesh%’;

9) Büyük ikili nesneleri depolamak için ilk dosya sistemine koyun ve veritabanı dosya yolunu ekleyin.

10) Sorguları yazarken genel SQL standart kurallarına uygun yazmaya çalışmalıyız.

a-)SQL terimlerine yeni satırda başlayın.

b-)Tüm kelimeleri tek boşlukta ayırın.

c-)İlk sql kelimesindeki hizaya göre sağa ve sola hizalamaya özen gösterin.

 

Mustafa Yılmaz

Mustafa Yılmaz

2011 yılında Trakya Üniversitesi Bilgisayar Mühendisliğinden Mezun
Oldu.
4 yıldan fazla bir zamandır Yazılım,3 yıldır Oracle E-Business Suite Uygulama Yazılımı
üzerinde Teknik Danışman Olarak profesyonel kariyerine devam
etmektedir.

Mail: mustafayilmaz56@gmail.com
Mustafa Yılmaz

Latest posts by Mustafa Yılmaz (see all)



Yorum yapın

*