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.
- 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.
Záznamů
