Condividi tramite

Prestazioni dei parametri delle istruzioni preparate per il driver JDBC

Scaricare il driver JDBC

Questo articolo illustra in che modo i parametri delle istruzioni preparate influiscono sulle prestazioni lato server in Microsoft JDBC Driver per SQL Server e forniscono indicazioni sull'ottimizzazione dell'utilizzo dei parametri.

Comprensione dei parametri delle istruzioni preparate

Le istruzioni preparate offrono vantaggi significativi in termini di prestazioni consentendo a SQL Server di analizzare, compilare e ottimizzare una query una sola volta, quindi riutilizzare più volte il piano di esecuzione. Tuttavia, il modo in cui si specificano parametri può influire significativamente su questo vantaggio sulle prestazioni.

Quando si crea un'istruzione preparata, SQL Server genera un piano di esecuzione basato sui metadati dei parametri, tra cui:

  • Tipo di dati
  • Precisione (per i tipi numerici)
  • Scala (per i tipi decimali)
  • Lunghezza massima (per i tipi stringa e binari)

Questi metadati sono fondamentali perché SQL Server lo usa per ottimizzare il piano di esecuzione delle query. Le modifiche apportate a una di queste caratteristiche di parametro possono forzare SQL Server a rimuovere il piano esistente e crearne uno nuovo, con una riduzione delle prestazioni.

Impatto delle modifiche ai parametri sulle prestazioni

Modifiche al tipo di parametro

Quando il tipo di parametro di un'istruzione preparata cambia tra le esecuzioni, SQL Server deve replicare l'istruzione. Questa ripreparazione include:

  1. Analizzare di nuovo l'istruzione SQL.
  2. Compilazione di un nuovo piano di esecuzione.
  3. Memorizzazione nella cache del nuovo piano (se la memorizzazione nella cache è abilitata).

Si consideri l'esempio seguente:

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();

In questo scenario, il passaggio da setInt a setString modifica il tipo di parametro da int a varchar, che forza SQL Server a replicare l'istruzione.

Modifiche di precisione e scalabilità

Per i tipi numerici come decimal e numeric, le modifiche apportate alla precisione o alla scalabilità attivano anche la replica:

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 crea piani di esecuzione diversi per combinazioni di precisione e scala diverse perché la precisione e la scalabilità influiscono sul modo in cui il motore di database elabora la query.

Procedure consigliate per l'utilizzo dei parametri

Per ottimizzare le prestazioni delle istruzioni preparate, seguire queste procedure consigliate:

Specificare i tipi di parametro in modo esplicito

Quando possibile, usare i metodi setter espliciti che corrispondono ai tipi di colonna del database:

// 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

Usare metadati dei parametri coerenti

Mantenere la precisione e la scalabilità coerenti per i parametri numerici:

// 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

Informazioni sull'arrotondamento dei dati con tipi numerici

L'uso di precisione e scalabilità non corrette per i parametri numerici può comportare l'arrotondamento imprevisto dei dati. La precisione e la scala devono essere appropriate sia per il valore del parametro che per la posizione in cui viene usata nell'istruzione 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

Sebbene siano necessarie precisione e scala appropriate per i dati, evitare di modificare questi valori ad ogni esecuzione di un'istruzione preparata. Ogni modifica della precisione o della scala determina che l'istruzione venga ripreparata sul server, annullando i vantaggi prestazionali delle istruzioni preparate.

// 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();
}

Per bilanciare la correttezza e le prestazioni:

  1. Determinare la precisione e la scalabilità appropriate per i requisiti aziendali.
  2. Normalizzare tutti i valori dei parametri per usare precisione e scala coerenti.
  3. Usare le modalità di arrotondamento esplicite per controllare la modalità di regolazione dei valori.
  4. Verificare che i valori normalizzati corrispondano alle definizioni di colonna di destinazione.

Annotazioni

È possibile usare l'opzione calcBigDecimalPrecision di connessione per ottimizzare automaticamente le precisioni dei parametri. Se abilitato, il driver calcola la precisione minima necessaria per ogni valore BigDecimal, che consente di evitare l'arrotondamento non necessario. Tuttavia, questo approccio potrebbe comportare più istruzioni preparate man mano che i dati cambiano perché valori di precisione diversi causano la ripreparazione. La definizione manuale della precisione e della scalabilità ottimali nel codice dell'applicazione è l'opzione migliore quando possibile, poiché offre sia l'accuratezza dei dati che il riutilizzo coerente delle istruzioni.

Evitare di combinare i metodi di impostazione dei parametri

Non passare da metodi setter diversi per la stessa posizione del parametro tra le esecuzioni:

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

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

Usare setNull() con tipi espliciti

Quando si impostano valori Null, specificare il tipo SQL per mantenere la coerenza:

// 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

Rilevamento dei problemi di ripreparazione

Per identificare se le modifiche ai parametri causano problemi di prestazioni:

  1. Usare SQL Server Profiler o Eventi Estesi per monitorare gli eventi SP:CacheMiss e SP:Recompile.
  2. Esaminare il sys.dm_exec_cached_plans DMV per controllare il riutilizzo del piano.
  3. Analizzare le metriche delle prestazioni delle query per identificare le istruzioni con ricompilazioni frequenti.

Query di esempio per verificare il riutilizzo del piano:

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;

Contatori delle prestazioni

Monitorare questi contatori delle prestazioni di SQL Server:

  • Statistiche SQL: ricompilazione SQL/sec - Mostra la frequenza con cui le istruzioni vengono ricompilate.
  • Statistiche SQL: compilazioni SQL/sec - Mostra la frequenza con cui vengono creati nuovi piani.
  • Cache dei piani: rapporto di successo della cache - Indica quanto efficacemente i piani vengano riutilizzati.

Per altre informazioni sui contatori e su come interpretarli, vedere SQL Server, Plan Cache object (Oggetto Cache dei piani).

Considerazioni avanzate

Query con parametri e pianificare l'inquinamento della cache

L'inquinamento della cache dei piani si verifica quando la precisione decimale o numerica variabile fa sì che SQL Server crei più piani di esecuzione per la stessa query. Questo problema spreca la memoria e riduce l'efficienza del riutilizzo del piano di esecuzione.

// 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();

Per evitare l'inquinamento della cache dei piani, mantenere la precisione e la scalabilità coerenti per i parametri numerici:

// 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();

Le variazioni di lunghezza di stringa e valore intero non causano l'inquinamento della cache dei piani. Soltanto le modifiche alla precisione e alla scala per i tipi numerici creano questo problema.

Proprietà della stringa di connessione

Il driver JDBC fornisce proprietà di connessione che influiscono sul comportamento e sulle prestazioni delle istruzioni preparate:

  • enablePrepareOnFirstPreparedStatementCall - (impostazione predefinita: false) Controlla se il driver chiama sp_prepexec alla prima o alla seconda esecuzione preparata. La preparazione alla prima esecuzione migliora leggermente le prestazioni se un'applicazione esegue in modo coerente la stessa istruzione preparata più volte. La preparazione alla seconda esecuzione migliora le prestazioni delle applicazioni che eseguono principalmente istruzioni preparate una sola volta. Questa strategia elimina la necessità di una chiamata separata a unprepare se l'istruzione preparata viene eseguita una sola volta.
  • prepareMethod - (impostazione predefinita: prepexec) Specifica il comportamento da usare per la preparazione (prepare o prepexec). Impostando prepareMethod su prepare comporta un accesso iniziale separato al database per preparare l'istruzione senza valori iniziali da prendere in considerazione nel piano di esecuzione. Impostare su prepexec per usare sp_prepexec come metodo di preparazione. Questo metodo combina l'azione di preparazione con la prima esecuzione, riducendo i trasferimenti di rete. Fornisce inoltre al database i valori dei parametri iniziali che il database può prendere in considerazione nel piano di esecuzione. A seconda della modalità di ottimizzazione degli indici, un'impostazione può offrire prestazioni migliori rispetto all'altra.
  • serverPreparedStatementDiscardThreshold - (impostazione predefinita: 10) Controlla l'invio in batch delle sp_unprepare operazioni. Questa opzione può migliorare le prestazioni raggruppando sp_unprepare le chiamate. Un valore più elevato lascia le istruzioni preparate rimanere più a lungo sul server.

Per altre informazioni, vedere Impostazione delle proprietà di connessione.

Riassunto

Per ottimizzare le prestazioni delle istruzioni SQL preparate in relazione ai parametri:

  1. Usare metodi di setter espliciti che corrispondono ai tipi di colonna del database.
  2. Mantenere i metadati dei parametri (tipo, precisione, scala, lunghezza) coerenti tra le esecuzioni.
  3. Non passare da metodi setter diversi per lo stesso parametro.
  4. Specificare i tipi SQL in modo esplicito quando si usa setObject o setNull.
  5. Riutilizzare le istruzioni preparate invece di crearne di nuove.
  6. Monitorare le statistiche della cache dei piani per identificare i problemi di ricoparazione.
  7. Prendere in considerazione le proprietà di connessione che influiscono sulle prestazioni delle istruzioni preparate.

Seguendo queste pratiche, è possibile ridurre al minimo la riepreparazione lato server e ottenere i migliori vantaggi in termini di prestazioni dalle istruzioni preparate.

Vedere anche

Memorizzazione nella cache dei metadati delle istruzioni preparate per il driver JDBC
Uso del driver JDBC per il miglioramento di prestazioni e affidabilità
Impostazione delle proprietà delle connessioni

  • Last updated on