Prestaties van parameters van prepared statements voor het JDBC-stuurprogramma

JDBC-stuurprogramma downloaden

In dit artikel wordt uitgelegd hoe parameters voor voorbereide instructies van invloed zijn op de prestaties aan de serverzijde in het Microsoft JDBC-stuurprogramma voor SQL Server en richtlijnen biedt voor het optimaliseren van parametergebruik.

Vertrouwd raken met parameters voor voorbereide instructies

Voorbereide instructies bieden aanzienlijke prestatievoordelen doordat SQL Server een query eenmaal kan parseren, compileren en optimaliseren, en vervolgens het uitvoeringsplan meerdere keren opnieuw kan gebruiken. De manier waarop u parameters opgeeft, kan echter aanzienlijk van invloed zijn op dit prestatievoordeel.

Wanneer u een voorbereide instructie maakt, genereert SQL Server een uitvoeringsplan op basis van de metagegevens van de parameter, waaronder:

Gegevenstype
Precisie (voor numerieke typen)
Schaal (voor decimale typen)
Maximale lengte (voor tekenreeks- en binaire typen)

Deze metagegevens zijn van cruciaal belang omdat SQL Server deze gebruikt om het queryuitvoeringsplan te optimaliseren. Wijzigingen in een van deze parameterkenmerken kunnen ertoe dwingen dat SQL Server het bestaande plan negeert en een nieuw plan maakt, wat resulteert in een prestatiestraf.

De invloed van parameterwijzigingen op de prestaties

Wijzigingen in parametertype

Wanneer het parametertype van een voorbereide instructie wordt gewijzigd tussen uitvoeringen, moet SQL Server de instructie opnieuw voorbereiden. Deze reparatie omvat:

De SQL-instructie opnieuw parseren.
Een nieuw uitvoeringsplan samenstellen.
Het nieuwe plan opslaan in de cache (als caching is ingeschakeld).

Bekijk het volgende voorbeeld:

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 dit scenario verandert het overschakelen van setInt naar setString het parametertype van int naar varchar, waardoor SQL Server de instructie opnieuw moet voorbereiden.

Wijzigingen in precisie en schaal

Voor numerieke typen zoals decimal en numeric worden wijzigingen in precisie of schaal ook herpreparatie activeert.

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 maakt verschillende uitvoeringsplannen voor verschillende combinaties van precisie en schaal, omdat precisie en schaal van invloed zijn op de manier waarop de database-engine de query verwerkt.

Aanbevolen procedures voor parametergebruik

Om de prestaties van de geprepareerde instructie te maximaliseren, volgt u deze beste werkwijzen:

Parametertypen expliciet opgeven

Gebruik indien mogelijk de expliciete settermethoden die overeenkomen met uw databasekolomtypen:

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

Consistente parametermetagegevens gebruiken

Behoud consistente precisie en schaal voor numerieke parameters:

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

Inzicht in de afronding van data met behulp van numerieke typen

Het gebruik van onjuiste precisie en schaal voor numerieke parameters kan leiden tot onbedoelde afronding van gegevens. De precisie en schaal moeten geschikt zijn voor zowel de parameterwaarde als waar deze wordt gebruikt in de SQL-instructie.

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

Hoewel u de juiste precisie en schaal voor uw gegevens nodig hebt, moet u deze waarden niet wijzigen voor elke uitvoering van een voorbereide instructie. Elke wijziging in precisie of schaal zorgt ervoor dat de instructie opnieuw wordt voorbereid op de server, waardoor de prestatievoordelen van voorbereide instructies worden genegeerd.

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

Om juistheid en prestaties in balans te brengen:

Bepaal de juiste precisie en schaal voor uw bedrijfsvereisten.
Normaliseer alle parameterwaarden om consistente precisie en schaal te gebruiken.
Gebruik expliciete afrondingsmodi om te bepalen hoe waarden worden aangepast.
Controleer of uw genormaliseerde waarden overeenkomen met de doelkolomdefinities.

Opmerking

U kunt de calcBigDecimalPrecision verbindingsoptie gebruiken om parameterprecisie automatisch te optimaliseren. Wanneer deze functie is ingeschakeld, berekent het stuurprogramma de minimale precisie die nodig is voor elke BigDecimale waarde, waardoor onnodige afronding wordt voorkomen. Deze benadering kan echter leiden tot meer voorbereidende instructies wanneer de gegevens veranderen, omdat verschillende precisiewaarden een hernieuwde voorbereiding vereisen. Het handmatig definiëren van de optimale precisie en schaal in uw toepassingscode is de beste optie, omdat deze zowel gegevensnauwkeurigheid als consistent gebruik van instructies biedt.

Vermijd het mengen van methoden voor het instellen van parameters

Schakel niet tussen verschillende settermethoden voor dezelfde parameterpositie voor uitvoeringen:

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

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

SetNull() gebruiken met expliciete typen

Wanneer u null-waarden instelt, geeft u het SQL-type op om consistentie te behouden:

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

Problemen met repreparatie detecteren

Ga als volgt te werk om te bepalen of parameterwijzigingen prestatieproblemen veroorzaken:

Gebruik SQL Server Profiler of Uitgebreide gebeurtenissen om SP:CacheMiss en SP:Recompile gebeurtenissen te bewaken.
Controleer de sys.dm_exec_cached_plans DMV om het hergebruik van het plan te controleren.
Analyseer metrische gegevens over queryprestaties om instructies met frequente repreparaties te identificeren.

Voorbeeldquery om het opnieuw gebruiken van plannen te controleren:

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;

Prestatiestatistieken

Volg deze SQL Server-prestatiecounters:

SQL-statistieken: SQL-compilaties per seconde : hier ziet u hoe vaak instructies opnieuw worden gecompileerd.
SQL-statistieken: SQL-compilaties per seconde : hier ziet u hoe vaak nieuwe plannen worden gemaakt.
PlanCache: Cachetrefferverhouding - Geeft aan hoe effectief plannen opnieuw worden gebruikt.

Zie SQL Server, Plan Cache-object voor meer informatie over de tellers en hoe u deze interpreteert.

Geavanceerde overwegingen

Geparameteriseerde query's en plan cachevervuiling

Plancachevervuiling treedt op wanneer verschillende decimale of numerieke precisie ertoe leidt dat SQL Server meerdere uitvoeringsplannen voor dezelfde query maakt. Dit probleem verspilt geheugen en vermindert de efficiëntie van het hergebruik van plannen:

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

Houd consistente precisie en schaal voor numerieke parameters om vervuiling van de cache te voorkomen:

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

Variatie in tekenreekslengten en integerwaarden veroorzaakt geen vervuiling in de plancache. Alleen precisie- en schaalwijzigingen voor numerieke typen veroorzaken dit probleem.

Verbindingsreeks eigenschappen

Het JDBC-stuurprogramma biedt verbindingseigenschappen die van invloed zijn op het gedrag en de prestaties van de prepared statement:

enablePrepareOnFirstPreparedStatementCall - (standaard: false) Bepaalt of het stuurprogramma sp_prepexec aanroept bij de eerste of tweede uitvoering. Het voorbereiden van de eerste uitvoering verbetert de prestaties enigszins als een toepassing dezelfde voorbereide instructie meerdere keren uitvoert. Het voorbereiden van de tweede uitvoering verbetert de prestaties van toepassingen die meestal eenmaal voorbereide instructies uitvoeren. Deze strategie verwijdert de noodzaak van een afzonderlijke aanroep om onvoorbereid te zijn als de voorbereide instructie slechts eenmaal wordt uitgevoerd.
prepareMethod - (standaard: prepexec) Hiermee geeft u het gedrag op dat moet worden gebruikt voor voorbereiding (prepare of prepexec). Instelling prepareMethod voor prepare leidt tot een afzonderlijk, eerste verzoek naar de database om de statement voor te bereiden zonder initiële waarden om op te nemen in het uitvoeringsplan. Stel in op prepexec om sp_prepexec als voorbereidingsmethode te gebruiken. Deze methode combineert de voorbereidingsactie met de eerste uitvoering, waardoor retouren in het netwerk worden verminderd. Het biedt ook de database met initiële parameterwaarden die de database in het uitvoeringsplan kan overwegen. Afhankelijk van hoe uw indexen zijn geoptimaliseerd, kan de ene instelling beter presteren dan de andere.
serverPreparedStatementDiscardThreshold - (standaard: 10) Beheert batchverwerking van sp_unprepare bewerkingen. Met deze optie kunt u de prestatieniveaus verbeteren door sp_unprepare oproepen te bundelen. Bij een hogere waarde blijven prepared statements langer op de server actief.

Zie De verbindingseigenschappen instellenvoor meer informatie.

Samenvatting

Optimaliseer de prestaties van voorbereide verklaringen voor parameters:

Gebruik expliciete settermethoden die overeenkomen met uw databasekolomtypen.
Behoud parametermetagegevens (type, precisie, schaal, lengte) consistent voor uitvoeringen.
Schakel niet tussen verschillende settermethoden voor dezelfde parameter.
Geef EXPLICIET SQL-typen op wanneer u deze gebruikt setObject of setNull.
Voorbereide instructies opnieuw gebruiken in plaats van nieuwe instructies te maken.
Monitor plancachestatistieken om problemen met repreparatie te identificeren.
Overweeg verbindingseigenschappen die invloed hebben op de prestaties van de prepared statement.

Door deze procedures te volgen, minimaliseert u repreparatie aan de serverzijde en profiteert u van de meeste prestatievoordelen van voorbereide instructies.

Zie ook

In cache opslaan van voorbereide statementmetagegevens voor de JDBC-driver
Prestaties en betrouwbaarheid verbeteren met het JDBC-stuurprogramma
De verbindingseigenschappen instellen

Feedback

Is deze pagina nuttig?

Last updated on 2025-12-22