MS SQL Server arşivleri - Fatihsoysal.com

En iyi veritabanı optimizasyon teknikleri nelerdir?

Merhaba sevgili okurlar,

Veritabanlarını optimize etmek, bir arabanın en iyi performansa göre ince ayarını yapmaya benzer. İşte süper etkili bulduğum bazı püf noktaları:

Index Oluşturma: Indexleri, veritabanınızın bir şeyleri hızlı bir şekilde bulmak için kullandığı kısayollar olarak düşünün. İyi organize edilmiş bir dosyalama sistemine sahip olmak gibidirler; bu bir kayıtları bulmayı kolaylaştıracaktır. İlk sırayı ixdexleme alıyor.
Sorgu Optimizasyonu: Sorgu yazmak bir sanattır. SELECT * kullanmak gibi tembel alışkanlıklardan kaçınmak işleri gerçekten hızlandırabilir.
Normalleştirme: Her şey verilerinizi düzenli tutmak ve gereksiz karmaşıklıktan kaçınmak arasında doğru dengeyi bulmakla ilgilidir.
Bölümleme: (Partitioning) Büyük tabloları daha küçük parçalara bölmek, veritabanınızın önemli olana odaklanmasına yardımcı olur. Bu, büyük bir pizzayı dilimlere bölmek gibidir; yönetimi ve servisi daha kolaydır.
Disk Optimize Etme: Çok hızlı bir sabit sürücüden ışık hızında bir SSD’ye yükseltmenin bilgisayarınıza turbo yüklemesi gibi, disk G/Ç’yi optimize etmek de veritabanınızın uçmasını sağlayabilir.
Önbelleğe Alma:(Caching) Sık erişilen verileri hafızada saklamak, en sevdiğiniz atıştırmalıkları kolunuzun ulaşabileceğiniz yerde tutmak gibidir. Her yemek istediğinde kilere (veya veri tabanına) koşmana gerek yok!
Bağlantı Havuzu: (Connection Pooling) Bağlantı havuzu oluşturmayı veritabanı bağlantıları için araç paylaşımı olarak düşünün. Sürüşü paylaşarak zamandan ve kaynaklardan tasarruf sağlar.
Düzenli Bakım: (Maintenance) Arabanızdaki yağı değiştirmek gibi, düzenli bakım da veritabanınızın sorunsuz çalışmasını sağlar. Bu en heyecan verici görev değil, ancak uzun vadede büyük fayda sağlıyor.

Performansı artırmada hangi veritabanı optimizasyon tekniklerini en etkili buluyorsunuz? Bu teknikleri projelerinizde uygulamaya ilişkin deneyimlerinizi ve öngörülerinizi yorumlar bölümünden paylaşabilirsiniz.

SQL sorgularını 23x hızlandırmak mümkün mü?

Merhaba, Senaryomuzda 700.000’den fazla kaydın bulunduğu gönderim tablomuz korkunç bir performans sergiliyor. Tablodan veri almak için gereken maksimum süre 6 saniyeden fazla.

Sorgu oldukça basitti. Elimizde olan tek şey 4 join, birkaç where cümleciği (yaklaşık 4), order( created_at time alanında DESC), sınırlama

Sorun kaynağı çözümlemesi..

Gönderim tablomuzda karşılaştığımız büyük yavaşlamaya neden olan birkaç nokta şunlar:

Çözüm 1: Index oluşturma

İnsanlar milyonlarca satırla boğuşuyor, bizimki bunun sadece basit bir örnek, yani yanlış yaptığımız bir şeyler olmalı. Doğru indeksleme size sorgularınızda hız açısından çok yardımcı olacaktır. İlk düşüneceğimiz nokta bu.

Herhangi bir optimizasyon olmadan her şeyin çalıştırılması index ekledikten sonra ortalama 2,3 saniye‘ye düştü. (elbette bu süre yalnızca sorgunun veritabanı üzerinde çalıştırılma süresini içermez, aynı zamanda verilerin ağ üzerinden yayılması süresini de içerir)

Sütunlara indekslemeyi ekledikten sonra, sürenin kısaldığını öğrencik, ancak bu yeterli değil. Hâlâ 2 saniye civarında ve çoğu zaman bundan daha fazla sürede yanıt dönüyor.

Çözüm 2: Boş Dize (Empty string) hatası

Kullanıcı herhangi bir girişe herhangi bir şey yazmadıysa, bu çoğunlukla sayfanın ilk yüklendiği ve API çağrısının herhangi bir filtreleme olmadan yalnızca en son verileri almak için yapıldığı sırada gerçekleşir.

Yani kısacası string alan tipi içerisindeki “” değerlerini empty/null gibi tanımladığımızda sorgu süresi 2,3 saniyeden 1,3 saniyeye indi.

Tamam, doğru yönde ilerliyoruz. Tam 1 saniyeyi kısalttık ama yine de 200/150 ms’nin altına inmek zorundayız, yani daha gidecek çok yolumuz var.

Çözüm 3: Gereksiz birleştirmeler (Join)

Gönderimleri sorgularken zorunlu olmayan tablolarla birleştirmeler yapıyoruz. Bu yüzden koda tüm bunlar eklendiğinde onu kaldırdık ama bu, inceleyenlerin pek fazla dikkat etmediğini gösteriyor

Çözüm 4: ORM

TypeORM’in geliştiricilere JSON benzeri nesneleri kullanarak SQL sorguları oluşturmamızı sağladığı aktif kayıt modeli adı verilen bir şey var, bir örnek ile anlatmak gerekirse,

model.find({
      select: { userName : true, firstName : true },
      where: { userName : “SomeUsername” },
      relations: { user : true, contest: true, problem: true },
      order: { created_at : “ASC/DESC” ,
      skip: 0,
      take: 10,
})

model.find({

select: { userName : true, firstName : true },

where: { userName : “SomeUsername” },

relations: { user : true, contest: true, problem: true },

order: { created_at : “ASC/DESC” ,

skip: 0,

take: 10,

})

Bu geliştirmeyi hızlı ve kolay hale getirir ve ham SQL sorguları yazma konusunda iyi olmayan bir geliştirici için çok sezgisel hissettirir, bu yöntem ile kelimenin tam anlamıyla SQL sorguları oluşturmak için JSON nesneleri oluşturuyorsunuz.

Bu yaklaşım iyi görünüyor ve çoğu zaman işe yarıyor ama bizim durumumuzda çok aptalca bir şey yapıyordu, sorguyu kendiniz görebilmeniz için ne yaptığını yazmayacağım.

Kısaca iki sorgu çalıştırıyordu, öncelikle bu durumda hiç ihtiyaç duyulmuyordu, daha sonra yazıp test ettiğim basit ve tek bir sorguyla kolaylıkla yapılabilirdi.

Yalnızca iki ayrı sorgu çalıştırmakla kalmıyordu, aynı zamanda dört tabloyu her iki sorgu için de iki kez birleştiriyor ve ardından da sıralıyordu. her ikisinde de iki kez. (Bu kelimenin tam anlamıyla hiçbir anlam ifade etmiyor)
Performansın en çok darbe aldığı yer burasıydı. Aşağıdaki sorgulara kendiniz bir göz atın.

SELECT DISTINCT 
`distinctAlias`.`Submission_id` AS `ids_Submission_id`, `distinctAlias`.`Submission_created_at` 
FROM (SELECT `Submission`.`id` AS `Submission_id`, ... more selects 
FROM `submission` `Submission` 
LEFT JOIN `problem` `SubmissionSubmission_problem` 
ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId`  LEFT JOIN 
`user` `SubmissionSubmission_user` 
ON `Submission_Submission_user`.`id`=`Submission`.`userId`) `distinctAlias` 
ORDER BY `distinctAlias`.`Submission_created_at` DESC, `Submission_id` 
ASC LIMIT 10

SELECT DISTINCT

`distinctAlias`.`Submission_id` AS `ids_Submission_id`, `distinctAlias`.`Submission_created_at`

FROM (SELECT `Submission`.`id` AS `Submission_id`, ... more selects

FROM `submission` `Submission`

LEFT JOIN `problem` `SubmissionSubmission_problem`

ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId` LEFT JOIN

`user` `SubmissionSubmission_user`

ON `Submission_Submission_user`.`id`=`Submission`.`userId`) `distinctAlias`

ORDER BY `distinctAlias`.`Submission_created_at` DESC, `Submission_id`

ASC LIMIT 10

SELECT `Submission`.`id` 
AS `Submission_id`, `Submission`.`language` 
AS `Submission_language`, `Submission`.`verdictCode` 
AS `Submission_verdictCode`, `Submission`.`tokens`
... shit ton of selects 
FROM `submission` `Submission` 
LEFT JOIN `problem` `SubmissionSubmission_problem` 
ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId`  LEFT JOIN `user` `SubmissionSubmission_user` 
ON `Submission_Submission_user`.`id`=`Submission`.`userId` WHERE `Submission`.`id` 
IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
ORDER BY `Submission`.`created_at` 
DESC

SELECT `Submission`.`id`

AS `Submission_id`, `Submission`.`language`

AS `Submission_language`, `Submission`.`verdictCode`

AS `Submission_verdictCode`, `Submission`.`tokens`

... shit ton of selects

FROM `submission` `Submission`

LEFT JOIN `problem` `SubmissionSubmission_problem`

ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId` LEFT JOIN `user` `SubmissionSubmission_user`

ON `Submission_Submission_user`.`id`=`Submission`.`userId` WHERE `Submission`.`id`

IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

ORDER BY `Submission`.`created_at`

DESC

2 ayrı sorguyla yapmaya çalışılan şeyin aynısını yapmak için basit bir tek SQL sorgusu yazıldı

SELECT
  Submission.id,
  Submission.language,
  Submission.verdictCode,
...
FROM
  submission AS Submission
  LEFT JOIN problem ...
  LEFT JOIN user ...
ORDER BY
  Submission.created_at DESC
LIMIT 10

SELECT

Submission.id,

Submission.language,

Submission.verdictCode,

...

FROM

submission AS Submission

LEFT JOIN problem ...

LEFT JOIN user ...

ORDER BY

Submission.created_at DESC

LIMIT 10

Ve bu sorguyu çalıştırdığımızda sadece 100 ms’de çalıştı.

Böylece 1,3 saniyeden 100 ms’ye , Genel olarak 2,3 saniyeden 100 ms’ye indik.

Bu da 23 kattan fazla performans artışı anlamına geliyor .

Okuduğunuz için teşekkür ederim.

PHP ile MsSQL Server Veritabanı Bağlantısı (XAMPP)

Merhaba. Linux sistem uygulamaları ile windows teknoloji uyumsuzlukları uzun zaman gündemi çokça meşgul etti. Biz developerları da epeyce yordu. Ama artık bu problemler büyük orada aşıldı. Platform bağlantıları için güçlü uygulamalar geliştirildi. Bunların en güzel örneklerinden biri XAMPP.

Bildiğiniz üzere PHP ile birlikte kullanılan veritabanlarından en yaygın olarak kullanılanı MySQL. Fakat geçtiğimiz günlerde PHP üzerinden MsSQL Server bağlantısı kurmam gerekti. Yoğun bir araştırma sürecinden sonra bağlantıyı başarılı biçimde kurmuş oldum.

Gelin hep beraber adım adım PHP – MsSQL bağlantısını gerçekleştirelim.

1- Microsoft® ODBC Driver 11 for SQL Server® – Windows kurulumu

Aslına bakarsanız bu kurulumu en son da yapabilirsiniz. Ama sıra ile gitmemizde yarar var. Microsoft® ODBC Driver bizim için MsSQL ve PHP arasında köprü görevi görecek. Bağlantımız için kurulumu yapmamız çok önemli. Minik bir install paketi halinde indireceğimiz kurulum dosyasını hızlıca kurmamız başlangıç için yeterli. Buradan indirebilirsiniz.

2- Microsoft Drivers for PHP for SQL Server

Bu aşamada öncelikle kullandığımız PHP sürümünü öğrenmeliyiz. XAMPP kullandığınızı varsayarsak XAMPP dashboard üzerinden phpinfo sekmesine gelerek PHP sürümünüzü öğrenebilirsiniz. XAMPP harici bir derleyici kullanıyorsanız aşağıdaki PHP kod betiğini çalıştırarak sürümünüzü öğrenebilirsiniz.

<?php
$version = phpversion();
print $version;
?>

<?php

$version = phpversion();

print $version;

PHP sürümünü öğrendikten sonra buradan sürüme uygun olan PHP driver paketimizi indirelim.

Dikkat: Burada 5 çeşit driver paketini göreceksiniz. PHP sürümünüze uygun olan paketi indirmelisiniz. Örnek olarak ben PHP 5.6 sürümünü kullanıyorum. Buna uygun olarak içersinde 5.6 .dll dosyalarını barındıran SQLSRV32 driver paketini indirmem gerekir.

Benim için pakette gerekli olan dosyalar:

php_pdo_sqlsrv_56_ts.dll ve php_sqlsrv_56_ts.dll

Bu iki dosyayı kopyalıyorum. Ve xampp/php/ext dizinine yapıştırıyorum. Son olarak xammp/php dizininde php.ini dosyasını herhangi bir metin editörü ile açıyoruz. ;extension kelimesini aratıyoruz. Burada yüklü extension dosyalarını görebilirsiniz. Aşağıdaki kodları buraya ekleme yapıyoruz ve dosyamızı kaydediyoruz. Tabi kendi sürümünüzün dll bilgilerini eklemeniz gerekiyor. Burası çok önemli. Örn: PHP 7.0 kullanıyorsanız 7 sürümünün dll dosyalarını edinip, ilgili klasöre atıp, php.ini dosyasını bir güncelleme yapmalısınız.

extension=php_sqlsrv_56_ts.dll
extension=php_pdo_sqlsrv_56_ts.dll

İşlemlerimiz bu kadar.

Bonus: MsSQL – PHP Bağlantı kodlarımız

<?php

//mssql connection için gerekli kodlar  

$serverName = "DESKTOP-QJPEMQ9"; //mssql server name buraya gelecek.

$connectionInfo = array( "Database"=>"db_deneme"); //db name buraya gelecek.
$conn = sqlsrv_connect($serverName, $connectionInfo);


if ($conn) {
	echo "Connection Successfull <hr>";
}else{
	echo "Connection Failed! <hr>";
	die(print_r(sqlsrv_errors(), true));
}

?>

<?php

//mssql connection için gerekli kodlar

$serverName = "DESKTOP-QJPEMQ9"; //mssql server name buraya gelecek.

$connectionInfo = array( "Database"=>"db_deneme"); //db name buraya gelecek.

$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn) {

echo "Connection Successfull <hr>";

}else{

echo "Connection Failed! <hr>";

die(print_r(sqlsrv_errors(), true));

}