Test rychlosti SQL procedur: MySql a MS SQL 2008

Na základě připomínky k mému článku Rychlejší je zápis do souboru nebo do databáze? jsem se rozhodl otestovat z manuálů známé tvrzení, že je rychlejší používat vložené SQL procedury proti přímému volání SQL příkazů. Vytvořil jsem tabulky a plnil ji 100000 záznamy. Nejprve byla tabulka bez indexů a pak s indexy. Tabulku jsme plnil nejdříve klasickým SQL INSERT příkazem, potom pomocí SQL procedury.

Použil jsem tabulku:

CREATE TABLE `tableinsert2` (
 `cislo` int(11) NOT NULL,
 `datum` datetime NOT NULL,
 `retezec` varchar(255) NOT NULL
)  

Pro přístup k MySql 5.1 byl použit .NET connector od MySql verze 5.2.3. Databáze MySql je verze 5.1.25. MS SQL 2008 Express je verze 10.0.1600.22. Protože jsem testoval jak MySql, tak MS SQL, je tedy opět možné porovnat rychlost těchtodvou databází.

MySql procedura

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`insert2tableinsert2`$$
CREATE DEFINER=``@`` PROCEDURE `insert2tableinsert2`(IN pcislo INT, IN pdatum DATETIME, IN pretezec VARCHAR(255))
BEGIN
 insert into TableInsert2 (cislo,datum,retezec) values (pcislo,pdatum,pretezec);
END$$
DELIMITER ;

MS SQL procedura

ALTER PROCEDURE [dbo].[insert2tableinsert2]
 @pCislo int,
 @pDatum datetime,
 @pRetezec nvarchar(255)
AS
BEGIN
 SET NOCOUNT ON;
 insert into dbo.TableInsert2 (cislo,datum,retezec) values (@pCislo, @pDatum,@pRetezec);
END

Vlastní testovací kód

public class TestRychlostiSqlProcedur {
public void RychlostSqlProcedur() {
const uint POCET_ZAZNAMU = 100000;
zaznam z;
DateTime cas;
System.TimeSpan sts;

// ulozeni do databaze MySql
/*
MySqlConnection mc = new MySqlConnection("Database=test;Data Source=localhost");

MySqlCommand commInsert = new MySqlCommand();
commInsert.Connection = mc;
mc.Open();

// vymazani obsahu tabulky z predchozich testu
commInsert.CommandText = "TRUNCATE TABLE tableinsert2";
commInsert.ExecuteNonQuery();

// Ulozeni do databaze primo
cas = System.DateTime.Now;

for (uint k = 0; k < POCET_ZAZNAMU; k++) {
 z = novyZaznam(k);
 commInsert.CommandText = "INSERT INTO tableinsert2 (cislo,datum, retezec) Values ("
  + z.cislo + ",'"
  + OtherMetod.DateTime2IsoDateTime(z.datum) + "','"
  + z.retezec + "')";
 commInsert.ExecuteNonQuery();
}
sts = DateTime.Now - cas;
Console.WriteLine(cas + " --> " + DateTime.Now);
Console.WriteLine("Ukladani do databaze MySql primo (ms): " + sts.TotalMilliseconds);

// vymazani obsahu tabulky z predchozich testu
commInsert.CommandText = "TRUNCATE TABLE tableinsert2";
commInsert.ExecuteNonQuery();

cas = System.DateTime.Now;

// Priprava procedury
commInsert.CommandText = "insert2tableinsert2";
commInsert.CommandType = System.Data.CommandType.StoredProcedure;

for (uint k = 0; k < POCET_ZAZNAMU; k++) {
 z = novyZaznam(k);
 commInsert.Parameters.Clear();
 commInsert.Parameters.AddWithValue("@pcislo",z.cislo);
 commInsert.Parameters.AddWithValue("@pdatum", OtherMetod.DateTime2IsoDateTime(z.datum));
 commInsert.Parameters.AddWithValue("@pretezec", z.retezec);
 commInsert.ExecuteNonQuery();
}

sts = DateTime.Now - cas;
Console.WriteLine(cas + " --> " + DateTime.Now);
Console.WriteLine("Ukladani do databaze MySql pres proceduru (ms): " + sts.TotalMilliseconds);

mc.Close(); // uzavreni databaze
*/


// ulozeni do databaze MS SQL pres INSERT -----------------------------------------------------------------------
SqlConnection conn = new SqlConnection("server=CORE2-DUO-18\\SQLEXPRESS2008;database=test;trusted_connection=yes");
conn.Open();
SqlCommand sqlCom = new SqlCommand();
sqlCom.Connection = conn;
// vymazani obsahu tabulky z predchozich testu
sqlCom.CommandText = "DELETE FROM tableinsert2";
sqlCom.ExecuteNonQuery();

cas = System.DateTime.Now;
for (uint k = 0; k < POCET_ZAZNAMU; k++) {
 z = novyZaznam(k);
 sqlCom.CommandText = "INSERT INTO tableinsert2 (cislo,datum, retezec) Values ("
  + z.cislo + ",'"
  + OtherMetod.DateTime2IsoDateTime(z.datum) + "','"
  + z.retezec + "')";
 sqlCom.ExecuteNonQuery();
}
sts = DateTime.Now - cas;
Console.WriteLine(cas + " --> " + DateTime.Now);
Console.WriteLine("Ukladani do databaze MS SQL pres INSERT (ms): " + sts.TotalMilliseconds);

// ulozeni do databaze MS SQL pres proceduru -----------------------------------------------------------------

// vymazani obsahu tabulky z predchozich testu
sqlCom.CommandText = "DELETE FROM tableinsert2";
sqlCom.ExecuteNonQuery();

cas = System.DateTime.Now;
// Priprava procedury
sqlCom.CommandText = "insert2tableinsert2";
sqlCom.CommandType = System.Data.CommandType.StoredProcedure;

for (uint k = 0; k < POCET_ZAZNAMU; k++) {
 z = novyZaznam(k);
 sqlCom.Parameters.Clear();
 sqlCom.Parameters.AddWithValue("@pCislo", (int) z.cislo);
 sqlCom.Parameters.AddWithValue("@pdatum", OtherMetod.DateTime2IsoDateTime(z.datum));
 sqlCom.Parameters.AddWithValue("@pretezec", z.retezec);
 sqlCom.ExecuteNonQuery();
}

sts = DateTime.Now - cas;
Console.WriteLine(cas + " --> " + DateTime.Now);
Console.WriteLine("Ukladani do databaze MS SQL pres proceduru (ms): " + sts.TotalMilliseconds);

conn.Close();

}

struct zaznam {
 public DateTime datum;
 public uint cislo;
 public string retezec;
}

private zaznam novyZaznam(uint cislo) {
 zaznam nzaznam = new zaznam();
 nzaznam.datum = DateTime.Now.AddHours(cislo);
 nzaznam.cislo = cislo;
 nzaznam.retezec = nzaznam.datum.ToString() + "|" + cislo + "|" + nzaznam.datum.ToString();

return nzaznam;
}
}

Výsledky

	Databáze	Operace	Čas (ms)	Koeficient
Indexy 3)	Mysql text 1)	INSERT	26 594	1,00
		procedura	31 141	1,17
	MySql parameters 2)	INSERT	27 109	1,02
		procedura	37 503	1,41
	MS SQL 2008	INSERT	55 447	2,08
		procedura	38 091	1,43
Bez indexů	Mysql text 1)	INSERT	22 360	1,00
		procedura	25 169	1,13
	MySql parameters 2)	INSERT	23 359	1,04
		procedura	31 816	1,42
	MS SQL 2008	INSERT	49 347	2,21
		procedura	36 616	1,64

1) parametry jsou proceduře předány vytvořením textového příkazu. např. commandtext="call nazevprocedury(10,'jedna'");. 2) parametry jsou proceduře předány pomocí volání metody Parameters.AddWithValue (doporučovaný přístup) 3) Indexy jsou vytvořeny nad každým sloupcem. Tedy celkem tři indexy nad tabulkou.

Výsledky jsou zajímavé. Je zde několik závěrů na které stojí za to upozornit:

• MS SQL 2008 je pomalejší než MySql. V některých případech je čas více jak dvojnásobný proti MySql.
• U MySql je rychlejší použít přímo příkaz INSERT než SQL proceduru. Naopak u MS SQL 2008 je rychlejší použít SQL proceduru. Domnívám se, že chování MS SQL je logičtější a MySql musí procedury ještě vylepšit.
• U MySql je k mému překvapení rychlejší proceduru volat pomocí textového volání než používat doporučovaný způsob volání Parameters.AddWithValue. Platí samozřejmě pro testovanou verzi. V příštích verzích databáze se toto může a mělo by upravit.
• Rozdíl mezi tabulkou s indexy a tabulkou bez indexů je mnohem menší, než jsem předpokládal.
• U MySql jsem použil tabulky typu MyISAM. Podle mého minulého testu jsou totiž tabulky InnoDb při vkládání záznamů neuvěřitelně pomalé. Proto jsem je v tomto případě vůbec netestoval.

Moje interpretace výsledků je taková, že MySql je rychlejší a není moc podstatné, zda použijete u obou databází uložené procedury nebo budete SQL příkazy zadávat přímo. Sice existují určité rozdíly mezi těmito dvěmi způsoby, ale nejsou podstatné. Je nutné vzít v úvahu, že šlo o test, kde se zadávalo 100000 záznamů. Tímto testech nechci nijak naznačovat, že SQL procedury není vhodné používat. Jenom argumentace, že jsou jednoznačně rychlejší není správná. Rychlejší jsou podle tohoto testu u MS SQL 2008. Navíc rozdíl v rychlosti není nijak závratný. U plně indexované tabulky 0,69x a u neidexované tabulky 0,74x. V průměru tedy jsou u aktuální MS SQL databáze SQL procedury rychlejší 0,72x.