19 Sep 2014

Prepared Statements: Performance, Security, Tips

NB: in this article we talk mostly about PreparedStatement, but most of the information is suitable for CallableStatement as well.
A lot of us know that JDBC can operate with different kinds of Statements including simple Statement, PreparedStatement and CallableStatement. Let’s talk about their purpose and nuances.

First of all it’s worth mentioning that when databases receive an SQL query they check its syntax, parse it (soft parse), optimise it (hard parse) and create a query plan for it. Query Plan is a DB-level instruction on how exactly it’s going to execute the query.

DBs can cache queries they execute, thus if we’re doing something like this: select * from books and then invoke it several times, then at some point database will start caching it. In most systems a cache is just a map, which means it has a key and a value. In our case the key is the query (well, its hash) and the value is the Query Plan. When DB receives next identical query, it checks whether there is a Query Plan already prepared waiting to be fetched from the cache. If it’s there, then database won’t need to parse it again and we’ll get a performance boost (though it still re-builds the Query Plan in some cases when it wants to optimise the query even more).

It’s worth noticing one more time that the key in the cache map is a query. This means that this SQL: select * from books where id=1 and this one: select * from books where id=2 are different, they both will be compiled. Thus we can’t cache the same query just because it has different parameters! Well, that’s exactly the case PreparedStatement can cope with. It’s possible to rewrite the query to: select * from books where id=?. Then each time we want to execute it, database will take the Query Plan from the cache, the only thing left is just to pass parameters which will replace the question mark. Of course every database wants to be the best and it works with cache in its own way: some of DBs might invalidate cache entries quickly, others will wait longer, the decision may depend on statistics after all.
Now what happens when we use JDBC:
1. We start with connection.prepareStatement("some query"), JDBC Driver asks database to prepare the statement*. DB answers with the identifier of the query (most probably - its hash) and additional data like number of params in the query.
2. Next we invoke executeQuery() and JDBC Driver sends the identifier of the query and params. Database finds the query by its ID and simply uses its Query Plan without the need of parsing the query again.

Some of you may notice, that next time we invoke connection.prepareStatement() there will be another communication to the database in order to prepare the query, and then yet another communication for actual query execution. First of all there are 2 extra network requests which is bad from performance point. Second, why the heck do we prepare it again while we already have query ID? Wouldn’t it be simpler to reuse the same PreparedStatement? Well, that’s exactly what happens under the hood!

Connection#prepareStatement(String sql) - this is where magic takes place. JDBC Driver checks the object in its internal cache (not a DB cache!) by the query we passed to the method. And if it’s there, then old PreparedStatement is returned. If not, a new object is created**. This is called implicit caching***.

Next stop: PrepareStatement#close() - this method doesn’t quite stand for its name, it doesn’t actually close the statement but rather places it to the internal cache. Physically PreparedStatment gets closed only in cases a) if Connection is closed b) when cache reaches its max capacity and we need to empty it from old and rarely-used statements c) if cache is switched off d) if cache is not supported by JDBC Driver.

Finally, here are some points related to MySQL (well, most of the stuff will still be common to other databases):

  • SQLs have to be identical (queries with words USERS and users are different!) - that’s true for all the databases****

  • PreparedStatements are not always cached the first time they’re executed, sometimes you need to query database multiple times.

  • Connections to different MySQL Servers, or Connections using different protocols, or even two Connections with different encodings - they all will use different caches.

  • Query shouldn’t start with spaces (well, to be true I’m not quite sure on this, but I’m tired of reading docs already :) For PostgreSQL this is true).

  • Sub-queries and queries with UNION are not cached.

  • Queries inside stored procedures are not cached.

  • MySQL Server < 5.1.17 doesn’t cache the queries, higher versions have their own “ester eggs” which sometimes do not allow caching the queries, so read docs carefully!

  • You should set cachePrepStmts to true, it’s switched off by default. Use connection params like prepStmtCacheSize and prepStmtCacheSqlLimit for MySQL configuration.

Security benefits of Prepared Statements?

Besides performance-related features PreparedStatements secures us from SQL Injections. In order to make explanation short, the example will be silly. Let’s say we have a forum engine and functionality “Remove User”. We specify the username on UI and Submit the form. On the back end we have a code like this:

String query = "delete from users where username=" + username;`

You should remember for your entire life - this is nasty! If a bad guy would pass on UI something like this: smith ' or 'a'='a then we’ll get such a query: delete from users where username='smith' or 'a'='a'. Because a always equals to a our where statement will always be true for all the records in the table. And all of them will be deleted. In order to be safe in this case we would need to escape the string. This means that all the symbols that are meaningful for the database (like quote symbol) should be replaced with some other char sequence. If you do it yourself, it would look like this:
delete from users where username='smith\' or \'a\'=\'a'

Because all the quotes are replaced with \’ which means ‘interpret the quote as a string, not as a command’, we’re safe now. But bad guys have a large arsenal, they will always win unless you have a bullet-proof solution. PreparedStatement is is this solution. Because in case of PreparedStatements queries look like this: delete from users where username=? and we don’t construct queries with parameters ourselves: preparedStatement.setString(1, username), we’re protected from any kind of SQL Injections - all the escaping is managed by the database itself.

* JDBC Drivers that don’t support pre-compilation (Prepared Statements) send queries only on the executeQuery() step.
** Notice that when we create usual Statement we don’t pass strings which means every time a new object is instantiated.
*** Actually some JDBC Drivers (like Oracle) can cache usual Statements as well. In case of Oracle JDBC Driver you’d need to work with implementation-specific API and it still won’t be that effective. That’s called Explicit Caching.
**** Of course I didn’t look at every single database, but that’s true for 3 of the most popular drivers I’ve looked into.

Read more
19 May 2013

Ошибки, снижающие производительность и их устранение

Добрый день, уважаемый читатель.

В этой статье я расскажу тебе, как избежать некоторых ошибок при написании программ и повысить их быстродействие. В статье будут приведены три примера.

1. Борьба с инвариантами
Итак, что же такое инвариант и почему с ними стоит бороться? Говоря сухим академическим языком, инвариантом называется логическое выражение, истинное перед началом выполнения цикла и после каждого прохода тела цикла. Другими словам, это условие (или величина), неизменное во время выполнения цикла.

Рассмотрим следующий пример:

String string = “некоторая строка”;
for (int i = 0; i < string.length(); i++){        //string.length() вызывается при каждом проходе цикла
        //делаем что-то

Это чрезвычайно распространённая ошибка, которая может существенно ударить по производительности, когда при проверке истинности выполняется сложное действие или когда количество проходов цикла очень велико. Причина в том, что метод length() никак не меняет строку, следовательно, его вызов при каждом проходе является бесполезным. Думаю, читатель уже догадался, как можно оптимизировать цикл всего одной строчкой и сделать наш код более красивым и правильным:
String string = “некоторая строка”;
int stringLenght = string.length();        //длинна строки вычисляется лишь однажды
for (int i = 0; i < stringLenght; i++){
        //делаем что-то

Сразу же отмечу, что если исходная строка всё же изменяется в теле цикла, то данный метод бесполезен.

2. Неявный инвариант
Рассмотрим другой пример, в котором мной была допущена ошибка, связанная с инвариантами. Ошибку я обнаружил случайно, поскольку в данном случае она не столь очевидна. Однажды я писал класс, один из методов которого сканировал большой текстовый файл в поисках строк, соответствующих определённому шаблону. Шаблон был неизменен для каждого файла, поэтому логично было бы сделать вот так:

public class Parser {
        private Pattern p;
        public Parser(String pattern){
                //создаём шаблон неизменный в течение всего поиска
                p = Pattern.compile(pattern);

        public void parseStrings(){
                BufferedReader br;
                //инициализируем br
                String s;
                while((s = br.readLine()) != null){
                        if (checkString(s))

        private boolean checkString(String s){
                return p.matcher(s).matches();

Однако, по непонятной причине в силу неопытности я сделал так:
public class Parser{
        private String pattern;

        public Parser(String pattern){
                this.pattern = pattern;

        public void parseStrings(){
                BufferedReader br;
                String s;
                while((s = br.readLine()) != null){
                        if (checkString(s))

        //неэффективная реализация метода
        private boolean checkString(String s){
                //шаблон создаётся каждый раз, хотя в этом нет необходимости
                Pattern p = Pattern.compile(pattern);
                return p.matcher(s).matches();

Как видите, в методе checkString() мной была допущена ошибка. Шаблон для проверки создавался при каждом вызове метода (а вызывался он при прохождении каждой строки), хотя необходимости в этом не было, поскольку шаблон неизменен в течение всего поиска.

3. Эффективное удаление из середины ArrayList.
Идея написать этот раздел родилась после прочтения поста хабражителя sphinks «Java собеседование. Коллекции». Отличная статья, которую я рекомендую всем как начинающим, там и более опытным разработчикам. Считаю, что каждый откроет для себя новые и интересные факты и станет более грамотным. Но вернёмся к нашим баранам спискам. Итак, меня заинтересовал вот этот фрагмент: «…удаление последнего элемента происходит за константное время. Недостатки ArrayList проявляются при вставке/удалении элемента в середине списка — это вызывает перезапись всех элементов размещённых «правее» в списке на одну позицию влево, кроме того, при удалении элементов размер массива не уменьшается, до явного вызова метода trimToSize().»
Вдумайтесь: если из списка произвольной длинны вам необходимо удалить n элементов, начиная с позиции m, то независимо от длинны списка, будут перебраны и смещены левее все элементы, начиная с позиции m+n. И это будет выполняться при каждом вызове remove(). Думаю, читатель уже прикинул, сколько это займёт времени.
sphinks также предлагает способ оптимизации удаления: «На самом деле все довольно просто и очевидно, когда знаешь, как происходит удаление одного элемента. Допустим нужно удалить n элементов с позиции m в списке. Вместо выполнения удаления одного элемента n раз (каждый раз смещая на 1 позицию элементы, стоящие «правее» в списке), нужно выполнить смещение всех элементов, стоящих «правее» n+m позиции на n элементов левее к началу списка. Таким образом, вместо выполнения n итераций перемещения элементов списка, все выполняется за 1 проход».
Я решил реализовать предложенный алгоритм и сравнить его быстродействие с простым вызовом remove().
Получился вот такой класс:

package collectionstudy;

import java.io.*;
import java.util.ArrayList;

public class Main {
    //позиция с которой удаляем
    private static int m = 0;
    //количество удаляемых элементов
    private static int n = 0;
    //количество элементов в списке
    private static final int size = 1000000;
    //основной список (для удаления вызовом remove() и его копия для удаления путём перезаписи)
    private static ArrayList<Integer> initList, copyList;
    public static void main(String[] args){
        initList = new ArrayList<>(size);
        for (int i = 0; i < size; i++)
        System.out.println("Список из 1.000.000 элементов заполнен");
        copyList = new ArrayList<>(initList);
        System.out.println("Создана копия списка\n");
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            System.out.print("С какой позиции удаляем? > ");
            m = Integer.parseInt(br.readLine());
            System.out.print("Сколько удаляем? > ");
            n = Integer.parseInt(br.readLine());
        } catch(IOException e){
        System.out.println("\nВыполняем удаление вызовом remove()...");
        long start = System.currentTimeMillis();
        for (int i = m - 1; i < m + n - 1; i++)
        long finish = System.currentTimeMillis() - start;
        System.out.println("Время удаления с помощью вызова remove(): " + finish);
        System.out.println("Размер исходного списка после удаления: " + initList.size());
        System.out.println("\nВыполняем удаление путем перезаписи...\n");
        start = System.currentTimeMillis();
        finish = System.currentTimeMillis() - start;
        System.out.println("Время удаления путём смещения: " + finish);
        System.out.println("Размер копии списка:" + copyList.size());
    private static void removeEfficiently(){
        /* если необходимо удалить все элементы, начиная с указанного,
         * то удаляем элементы с конца до m
        if (m + n >= size){
            int i = size - 1;
            while (i != m - 1){
        } else{
            //переменная k необходима для отсчёта сдвига начиная от места вставка m
            for (int i  = m + n, k = 0; i < size; i++, k++)
               copyList.set(m + k, copyList.get(i));
            /* удаляем ненужные элементы в конце списка
             * удаляется всегда последний элемент, так как время этого действия
             * фиксировано и не зависит от размера списка
            int i = size - 1;
            while (i != size - n - 1){
            //сокращаем длину списка путём удаления пустых ячеек

Список из 1.000.000 элементов заполнен
Создана копия списка

С какой позиции удаляем? > 600000
Сколько удаляем? > 20000

Выполняем удаление вызовом remove()...
Время удаления с помощью вызова remove(): 22359
Размер исходного списка после удаления: 980000

Выполняем удаление путем перезаписи...

Время удаления путём смещения: 62
Размер копии списка:980000
СБОРКА УСПЕШНО ЗАВЕРШЕНА (общее время: 33 секунды)

Как говориться, почувствуйте разницу! :)
Используя метод removeEfficiently() можно без особого труда написать свою, более эффективную реализацию ArrayList.

На сегодня это всё, надеюсь, что тебе было интересно и твой код станет более правильным и производительным. Комментарии, замечания и пожелания принимаются как в комментариях, так и в личку.

Успехов тебе!

Read more
18 Dec 2012

PreparedStatement (подготовленные запросы)

NB: хотя здесь и ведется повествование про PreparedStatement, большая часть оного правдива и для CallableStatement.
Как, наверное, большинству известно в JDBC можно создавать разные виды Statement’ов включая обычные Statement, PreparedStatement и CallableStatement. Здесь мы обсудим что такое PreparedStatement и зачем он нужен.
Итак, начнем с того, что СУБД при поступлении в них запросов, проверяют их синтаксис, разбирают (soft parse), оптимизируют (hard parse) и создают некий query plan – то как на самом деле уже СУБД будет выполнять запрос, какие операции она будет при этом делать.
СУБД умеют кешировать выполненные запросы, то бишь если взять запрос: select * from books. И выполнять его несколько раз, то на каком-то этапе СУБД начнет его кешировать. Кеш во многих системах – это мапа, что значит, что у нее есть ключ и значение. В данном случае в качестве ключа будет SQL запрос (а точнее его хеш), а в качестве значения – разобранный план. Когда СУБД получает очередной запрос с тем же текстом, она проверяет кеш и если там уже есть скомпилированный запрос, то она использует его вместо того, чтоб наново его разбирать (на самом деле периодически СУБД доделывают hard parse, чтоб оптимизировать запрос еще больше).
Заметьте, что в качестве ключа используется полностью тело запроса. Это значит, что запрос:
select * from books where id=1 и запрос select * from books where id=2 – не являются одинаковыми и оба будут компилироваться, что значит что мы не можем кешировать один и тот же запрос только из-за того, что параметры каждый раз разные! Эту проблему решает PreparedStatement, который имеет вид: select * from books where id=? В данном случае СУБД закеширует запрос и будет только лишь подставлять новые параметры вместо знака вопроса. Это позволяет существенно ускорить обработку запросов. Разные СУБД конечно же по-разному реализуют кеш и время, когда он будет задействован (что часто основывается на статистике или просто на указаном пользователем значении).
Что происходит под капотом:

  1. Когда выполняется connection.prepareStatement("some query") драйвер обращается к СУБД для подготовки запроса*, которая возвращает обратно идентификатор запроса (его хеш как правило) и еще некоторые данные, такие как количество параметров в запросе.
  2. При вызове executeQuery() драйвер отсылает лишь идентификатор запроса и параметры, СУБД по ID находит уже разобранный запрос и выполняет его.

Но прозорливый читатель сразу заметит, что при следующем выполнении connection.prepareStatement() снова произойдет сначала вызов prepare к БД, затем собственно вызов для выполнения конкретного запроса. Во-первых, это два вызова по сети, что не хорошо с точки зрения производительности. Во-вторых, хоть СУБД и закешировало запрос и не будет его второй раз разбирать, у нас уже есть ID запроса после первого раза, зачем нам снова лазить в БД за ним, если его можно где-то сохранить и переиспользовать? Собственно так это и реализовано в большинстве драйверов:
Connection#prepareStatement(String sql) – здесь и происходит вся магия, по переданной в метод строке драйвер проверяет объект в своем внутреннем (не СУБД!) кеше, и, если тот там есть, возвращает его; если нет – создает новый.** Это называется неявным кешированием (implicit cache)***.
Идем дальше: PreparedStatement#close() - собственно этот метод и не оправдывает своего имени в данном случае – он не закрывает на самом деле statement, а помещает его как раз таки в кеш. Физически PreparedStatement закрывается только в случае а) если соединение с БД было закрыто б) когда кеш достигает своего максимальной вместимости и нужно освобождать его от старых и малоиспользуемых statement’ов в) если кеш отключен г) если кеш не поддерживается драйвером :)
Но это еще не все. В большистве случаев приложения работают не напрямую с соединениями, создавая и закрывая их, а с пулами соединений (например, DBCP, C3P0), которые сами их создают и предоставляют вашему коду. Так вот, если в обычном случае PreparedStatements привязаны к одному соединению и не могут быть переиспользованы, то пулы позволяют каждому соединению использовать подготовленные запросы других соединений, что означает, что производительность вырастет еще больше.
Опишу пункты, которые нужно знать при работе с подготовленными запросами в MySQL (многое из этого подходит и для других СУБД):

  • Запросы должны точно совпадать (запросы со словами USERS и users - будут считаться разными) - это правда для всех СУБД****

  • Не всегда PreparedStatement кешируются с первого раза, часто их нужно выполнить по несколько раз.

  • Соединения к разным MySQL серверам, или использующие два разных протокола, или даже просто - два одинаковых соединения с разными кодировками по умолчанию, - они все будут использовать разные кеши, которые не будут переиспользоваться другими соедиениями.

  • Запрос не должен начинаться с пробелов (если честно, то не уверен, что это правда для MySQL, но уже сил нет читать документацию :) Для PostgreSQL это так).

  • Подзапросы и запросы с UNION не кешируются.

  • Запросы внутри хранимых процедур не кешируются.

  • MySQL (не драйвер, а сам сервер) до версии 5.1.17 не кеширует запросы, у версий выше есть тоже свои “пасхальные яйца”, из-за который невозможно кешировать запрос, поэтому читайте обязательно документацию.

  • Обязательно установите свойство cachePrepStmts в true, ибо по умолчанию оно отключено! Используйте параметры соединения, такие как prepStmtCacheSize и prepStmtCacheSqlLimit для конфигурации MySQL драйвера.

Какие еще плюшки нам дает PreparedStatement?

Кроме улучшения производительности, подготовленные запросы защищают от SQL Injections. Чтобы было совсем просто понять суть, пример будет очень простым и глупым. Допустим, есть функциональность на форуме, такая как “удалить пользователя”. Мы вводим в поле его имя и нажимаем на кнопку Submit. Передается запрос на сервер и мы работаем с обычным Statement, ну и для создания запроса используем конкатенацию:

Sring query = "delete from users where username=" + username;

Запомните раз и навсегда, что это плохо! Если какой-то злоумышленник в поле на форме введет следующую строку: vasia' or 'a'='a, это приведет к печальным последствиям. Результирующий запрос будет следующим:
delete from users where username='vasia' or 'a'='a'

Т.к. ‘a’ всегда ровняется ‘a’, то выражение в where всегда будет true и в результате запроса удалятся все записи из таблицы. Для избежания подобного, нужно заескейпить входящую строку. Это значит, что все входящие символы, если они представляют собой что-то, что для СУБД представялется значащим символом (например, кавычки), будут заменены на какую-то другую комбинацию символов. Делать это можно самому, можно использовать уже существующие методы/библиотеки, но в конце концов запрос выходит приблизительно такой:
delete from users where username='vasya\' or \'a\'=\'a'

То есть все кавычки в строке заменены на ', что для MySQL будет значит “воспринимай ковычку как часть строки”. Однако зачем нам это делать самим, когда все уже сделано для нас с помощью PreparedStatement? Используя его запрос будет выглядет следующим образом:
delete from users where username=?

А затем мы укажем параметр: preparedStatement.setString(1, username) И все переданное сюда будет восприниматься исключительно как текст, СУБД сама все заескейпит.

Пишите свои вопросы и комментарии, ибо чует душа - сложно и запутано написано, будем переписывать если так оно и окажется.

* Некоторые драйвера, иже не поддерживают пред-компиляцию, отсылают запрос только на этапе executeQuery().
** Заметьте, что при создании обычного Statement, никакой строки в объект соединения не передается, что значит, что они создаются каждый раз наново.
*** На самом деле некоторые JDBC драйвера (такие как Oracle) могут кешировать и обычные statement’ы. В случае Oracle JDBC Driver для этого нужно дергать implementation-specific API и оно не будет столь же эффективно, ну и плюс там есть свои заморочки. Это называется explicit statement caching.
**** Конечно может и не для всех, все я не смотрел, но для 3 СУБД из проверенных - это правда.

PS: огромной спасибо нашему Vlad’у за его блог пост по заказу, который очень помог в написании данной статьи.
PPS: Обсуждение на тему: PreparedStatements. Both DBMS & Java caching

Read more