Поделиться через

Производительность параметров подготовленных запросов для драйвера JDBC

Скачать драйвер JDBC

В этой статье объясняется, как подготовленные параметры инструкции влияют на производительность на стороне сервера в драйвере Microsoft JDBC driver for SQL Server и содержат рекомендации по оптимизации использования параметров.

Понимание параметров подготовленного запроса

Подготовленные инструкции обеспечивают значительные преимущества производительности, позволяя SQL Server анализировать, компилировать и оптимизировать запрос один раз, а затем повторно использовать план выполнения несколько раз. Однако способ указания параметров может значительно повлиять на это преимущество производительности.

При создании подготовленной инструкции SQL Server создает план выполнения на основе метаданных параметра, в том числе:

  • Тип данных
  • Точность (для числовых типов)
  • Масштабирование (для десятичных типов)
  • Максимальная длина (для строковых и двоичных типов)

Эти метаданные важны, так как SQL Server использует его для оптимизации плана выполнения запросов. Изменения любой из этих характеристик параметров могут заставить SQL Server сбросить существующий план и создать новый, что приводит к снижению производительности.

Изменение параметров влияет на производительность

Изменения типа параметров

При изменении типа параметра подготовленной инструкции между выполнением SQL Server должен повторно подготовить инструкцию. Эта подготовка заново включает в себя следующее:

  1. Повторное анализирование инструкции SQL.
  2. Компиляция нового плана выполнения.
  3. Кэширование нового плана (если кэширование включено).

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

String sql = "SELECT * FROM Employees WHERE EmployeeID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

// First execution with Integer
pstmt.setInt(1, 100);
pstmt.executeQuery();

// Second execution with String - causes re-preparation
pstmt.setString(1, "100");
pstmt.executeQuery();

В этом сценарии переключение от setInt к setString изменяет тип параметра с int на varchar, что заставляет SQL Server переподготовить команду.

Изменения точности и масштабирования

Для числовых типов, таких как decimal и numeric, изменения точности или масштабирования также активируют повторную подготовку:

String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

// First execution with specific precision
BigDecimal price1 = new BigDecimal("19.99"); // precision 4, scale 2
pstmt.setBigDecimal(1, price1);
pstmt.setInt(2, 1);
pstmt.executeUpdate();

// Second execution with different precision - causes re-preparation
BigDecimal price2 = new BigDecimal("1999.9999"); // precision 8, scale 4
pstmt.setBigDecimal(1, price2);
pstmt.setInt(2, 2);
pstmt.executeUpdate();

SQL Server создает различные планы выполнения для различных сочетаний точности и масштабирования, так как точность и масштабирование влияют на процесс обработки запроса ядром СУБД.

Рекомендации по использованию параметров

Чтобы максимально повысить производительность подготовленных инструкций, выполните следующие рекомендации.

Явное указание типов параметров

По возможности используйте методы явного задания, соответствующие типам столбцов базы данных:

// Good: Explicit type matching
pstmt.setInt(1, employeeId);
pstmt.setString(2, name);
pstmt.setBigDecimal(3, salary);

// Avoid: Using setObject() without explicit types
pstmt.setObject(1, employeeId); // Type inference might vary

Использование метаданных согласованных параметров

Поддержка согласованной точности и масштабирования для числовых параметров:

// Good: Consistent precision and scale
BigDecimal price1 = new BigDecimal("19.99").setScale(2);
BigDecimal price2 = new BigDecimal("29.99").setScale(2);

// Avoid: Varying precision and scale
BigDecimal price3 = new BigDecimal("19.9");    // scale 1
BigDecimal price4 = new BigDecimal("29.999");  // scale 3

Общие сведения о округлениях данных с числовыми типами

Использование неправильной точности и масштабирования для числовых параметров может привести к непреднамеренное округление данных. Точность и масштабирование должны соответствовать значению параметра и месту, используемому в инструкции SQL.

// Example: Column defined as DECIMAL(10, 2)
// Good: Matching precision and scale
BigDecimal amount = new BigDecimal("12345.67").setScale(2, RoundingMode.HALF_UP);
pstmt.setBigDecimal(1, amount);

// Problem: Scale too high causes rounding
BigDecimal amount2 = new BigDecimal("12345.678"); // scale 3
pstmt.setBigDecimal(1, amount2); // Rounds to 12345.68

// Problem: Precision too high
BigDecimal amount3 = new BigDecimal("123456789.12"); // Exceeds precision
pstmt.setBigDecimal(1, amount3); // Might cause truncation or error

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

// Good: Consistent precision and scale across executions
PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO Orders (OrderID, Amount) VALUES (?, ?)");

for (Order order : orders) {
    pstmt.setInt(1, order.getId());
    // Always use scale 2 for currency
    BigDecimal amount = order.getAmount().setScale(2, RoundingMode.HALF_UP);
    pstmt.setBigDecimal(2, amount);
    pstmt.executeUpdate();
}

// Avoid: Changing scale for each execution
for (Order order : orders) {
    pstmt.setInt(1, order.getId());
    // Different scale each time - causes re-preparation
    pstmt.setBigDecimal(2, order.getAmount()); // Variable scale
    pstmt.executeUpdate();
}

Чтобы сбалансировать правильность и производительность, выполните приведенные ниже действия.

  1. Определите соответствующую точность и масштаб для бизнес-требований.
  2. Нормализуйте все значения параметров для использования согласованной точности и масштабирования.
  3. Используйте явные режимы округления, чтобы управлять настройкой значений.
  4. Убедитесь, что нормализованные значения соответствуют определениям целевого столбца.

Замечание

Вы можете использовать опцию подключения calcBigDecimalPrecision для автоматической оптимизации точности параметров. При включении драйвер вычисляет минимальную точность, необходимую для каждого значения BigDecimal, что помогает избежать ненужных округлений. Однако этот подход может привести к большему количеству подготовки операторов, поскольку изменения данных требуют повторной подготовки из-за различных значений точности. Определение оптимальной точности и масштабирования в коде приложения вручную является оптимальным вариантом, так как он обеспечивает точность данных и согласованное использование инструкций.

Избегайте смешивания методов настройки параметров

Не переключайтесь между различными методами задания для одной и той же позиции параметра во время выполнения:

// Avoid: Mixing setter methods
pstmt.setInt(1, 100);
pstmt.executeQuery();

pstmt.setString(1, "100"); // Different method - causes re-preparation
pstmt.executeQuery();

Использование setNull() с явными типами

При установке значений NULL укажите тип SQL для поддержания согласованности:

// Good: Explicit type for null
pstmt.setNull(1, java.sql.Types.INTEGER);

// Avoid: Generic null without type
pstmt.setObject(1, null); // Type might be inferred differently

Обнаружение проблем с повторной подготовкой

Чтобы определить, вызывают ли изменения параметров проблемы с производительностью:

  1. Используйте SQL Server Profiler или Extended Events для мониторинга событий SP:CacheMiss и SP:Recompile.
  2. Проверьте sys.dm_exec_cached_plans DMV, чтобы проверить повторное использование плана.
  3. Анализ метрик производительности запросов для идентификации инструкций с частыми повторами.

Пример запроса для повторного использования плана:

SELECT 
    text,
    usecounts,
    size_in_bytes,
    cacheobjtype,
    objtype
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE text LIKE '%YourQueryText%'
ORDER BY usecounts DESC;

Счетчики производительности

Отслеживайте эти счетчики производительности SQL Server:

  • Статистика SQL: повторные компиляции SQL/сек - Показывает, как часто пересобираются инструкции.
  • Статистика SQL: SQL-компиляции в секунду - Это показывает, как часто создаются новые планы.
  • Кэш планов: коэффициент попадания в кэш — указывает на то, насколько эффективно планы используются повторно.

Дополнительные сведения о счетчиках и их интерпретации см. в разделе SQL Server, объект "Кэш плана".

Дополнительные рекомендации

Параметризованные запросы и загрязнение кэша планов

Загрязнение кэша планов происходит, когда варьирующаяся точность десятичных или числовых значений приводит к тому, что SQL Server создает несколько планов выполнения для одного и того же запроса. Эта проблема тратит память и снижает эффективность повторного использования плана:

// Avoid: Varying precision pollutes the plan cache
String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

for (int i = 0; i < 1000; i++) {
    // Each different precision/scale creates a separate cached plan
    BigDecimal price = new BigDecimal("19." + i); // Varying scale
    pstmt.setBigDecimal(1, price);
    pstmt.setInt(2, i);
    pstmt.executeUpdate();
}
pstmt.close();

Чтобы избежать загрязнения кэша планов, сохраните согласованную точность и масштаб для числовых параметров:

// Good: Consistent precision and scale enables plan reuse
String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

for (int i = 0; i < 1000; i++) {
    // Same precision and scale - reuses the same cached plan
    // Note: Round or increase to a consistent scale that aligns with your application data needs.
    BigDecimal price = new BigDecimal("19." + i).setScale(2, RoundingMode.HALF_UP);
    pstmt.setBigDecimal(1, price);
    pstmt.setInt(2, i);
    pstmt.executeUpdate();
}
pstmt.close();

Изменения длины строк и целочисленных значений не вызывают загрязнения кэша планов— это проблема возникает только в результате изменения точности и масштабирования числовых типов.

Свойства строки подключения

Драйвер JDBC предоставляет свойства подключения, влияющие на поведение и производительность подготовленной инструкции:

  • enablePrepareOnFirstPreparedStatementCall (По умолчанию: false) определяет, подготовит ли драйвер запрос при первом или втором выполнении. Подготовка к первому выполнению немного повышает производительность, если приложение последовательно выполняет одну и ту же подготовленную инструкцию несколько раз. Подготовка к второму выполнению повышает производительность приложений, которые в основном выполняют подготовленные инструкции один раз. Эта стратегия удаляет необходимость отдельного вызова для неподготовки, если подготовленная инструкция выполняется только один раз.
  • prepareMethod — (по умолчанию: prepexec) Указывает поведение, используемое для подготовки (prepare или prepexec). Установка prepareMethod для prepare приводит к отдельному начальному обращению к базе данных для подготовки запроса без каких-либо начальных значений, которые база данных могла бы учитывать в плане выполнения. Установите prepexec для использования sp_prepexec в качестве метода подготовки. Этот метод объединяет действие подготовки с первым выполнением, уменьшая количество сетевых взаимодействий. Он также предоставляет базу данных с начальными значениями параметров, которые база данных может рассмотреть в плане выполнения. В зависимости от того, как оптимизированы индексы, один параметр может работать лучше, чем другой.
  • serverPreparedStatementDiscardThreshold — (по умолчанию: 10) управляет пакетной обработкой операций sp_unprepare . Этот параметр может повысить производительность путем пакетного объединения sp_unprepare вызовов. Более высокое значение оставляет подготовленные запросы на сервере дольше.

Дополнительные сведения см. в статье о настройке свойств подключения.

Сводка

Чтобы оптимизировать производительность подготовленного SQL-запроса с параметрами:

  1. Используйте явные методы задания, соответствующие типам столбцов базы данных.
  2. Поддерживайте согласованность метаданных параметров (тип, точность, масштаб, длина) во всех выполнениях.
  3. Не переключайтесь между различными методами задания для одного и того же параметра.
  4. Явно укажите типы SQL при использовании setObject или setNull.
  5. Повторно используйте подготовленные выражения вместо создания новых.
  6. Отслеживайте статистику кэша планов для выявления проблем с переподготовкой.
  7. Рассмотрим свойства подключения, которые влияют на производительность подготовленного запроса.

Следуя этим рекомендациям, вы сведете к минимуму пере-подготовку на стороне сервера и получите максимальные преимущества производительности от подготовленных инструкций.

См. также

Кэширование метаданных подготовленного запроса для драйвера JDBC
Повышение производительности и надежности с помощью JDBC Driver
Настройка свойств подключения

  • Last updated on