Подсказки при оптимизации ваших запросов
Следующие несколько параграфов попытаются дать вам несколько элементарных правил для ускорения ваших запросов в целом, и особенно как на SQLite оказывают негативное влияние виды поведений SQL, которые вы, возможно, считали само собой разумеющимся в других СУБД. Это ни в коем случае не полный гид по оптимизации. Для получения дополнительной информации об оптимизации ваших запросов посетите sqlite.org.
Важность транзакций
Если вы вставляете данные в SQLite без запуска транзакции: НЕ ДЕЛАЙТЕ ЭТОГО! Вызовите прямо сейчас BeginTransaction() с закончите с Commit()! Если вы думаете, что я шучу, думайте снова. Дизайн A.C.I.D. в SQLITE означает, что каждый раз, когда вы вставляете любые данные вне транзакции, неявная транзакция построена, вставка сделана и транзакция закрыта. КАЖДЫЙ РАЗ. Если вы задаетесь вопросом, почему ваши вставки берут в 100x дольше, чем они должны, то вот поэтому.
Подготовленные запросы
Посмотрите на на следующий кодекс и оцените его производительность:
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { int n; for (n = 0; n < 100000; n ++) { mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1); mycommand.ExecuteNonQuery(); } }
Этот код кажется довольно хорошим, но если вы думаете, что он работает быстро, вы неправы. Вот что с ним не так:
- Я не начал транзакцию сначала! Эта вставка медленная!
- CLR вызовет "new" примерно 100000 раз, так как я форматирую строку в цикле для каждой вставки.
- Так как SQLite предварительно собирает SQL-операторы, движок строит и разрушает 100000 SQL-операторов и выполняет распределение/освобождение памяти для них.
- Все это вовлекают еще приблизительно на 300000 вызовов interop больше, чем оптимизированная вставка.
Немного перепишем код:
using (SQLiteTransaction mytransaction = myconnection.BeginTransaction()) { using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { SQLiteParameter myparam = new SQLiteParameter(); int n; mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)"; mycommand.Parameters.Add(myparam); for (n = 0; n < 100000; n ++) { myparam.Value = n + 1; mycommand.ExecuteNonQuery(); } } mytransaction.Commit(); }
Теперь это сверкающая быстрая вставка для любого ядра базы данных, не просто для SQLite. SQL-оператор подготовлен однажды, при первом вызове ExecuteNonQuery(). После того, как подготовлен, ему никогда не нужна переоценка. Кроме того, мы не ассигнуем память в цикле и делаем очень минимальное количество переходов interop. Окружите всю конструкцию транзакцией и исполнение этой вставки будет сильно быстрее, чем оригинал.
Каждое достойное ядро базы данных использует подготовленные запросы. Если вы не кодируете это, вы не пишете оптимизированный SQL, а зря.