Cookies op Tweakers

Tweakers maakt gebruik van cookies, onder andere om de website te analyseren, het gebruiksgemak te vergroten en advertenties te tonen. Door gebruik te maken van deze website, of door op 'Ga verder' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt, bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

[jdbc] Extreme performance degradatie prepared statements

Pagina: 1
Acties:

  • flowerp
  • Registratie: september 2003
  • Laatst online: 16-09 13:18
Ik heb een aantal queries die vanaf een vrij grote tabel (paar miljoen rijen) een sum maken van een aantal kolommen. De querie gebruikt een aantal parameters waarmee sommige rijen wel en niet meegenomen worden in de sum.

Als ik deze query helemaal als een string opbouw en dan uitvoer via JDBC, doet ie er op een pentium 4 2.0Ghz ongeveer 800ms over. Doe ik echter de exact zelfde query via een JDBC prepared statement, dan gaat de executie tijd naar zo'n 20 seconden!

De reden dat ik het graag via een prepared statement wil is om te beschermen tegen SQL injectie.

De gebruikte DB is postgresql 8.1. Ik gebruik JDBC onder Tomcat 5.5.15 met gebruikmaking van de standaard connection pool DBCP. De JVM is Sun Java 5 update 6. Zowel Tomcat als Postgresql draaien op een debian linux machine met kernel 2.6.

Heeft iemand een idee wat deze extreme traagheid veroorzaakt wanneer ik de query execute via een prepared statement?

It's shocking to find how many people do not believe they can learn, and how many more believe learning to be difficult.


  • paulh
  • Registratie: juli 1999
  • Laatst online: 23-08 21:04
Roep je de methode con.prepareStatement meerdere keren aan?
Deze hoef je niet elke keer opnieuw aan te roepen als je alleen de variabelen verandert.
Dat kan een hoop tijd schelen.

[ZwareMetalen.com] - [Kom in aktie tegen de CO2 maffia]


  • flowerp
  • Registratie: september 2003
  • Laatst online: 16-09 13:18
paulh schreef op dinsdag 24 januari 2006 @ 21:08:
Roep je de methode con.prepareStatement meerdere keren aan?
Deze hoef je niet elke keer opnieuw aan te roepen als je alleen de variabelen verandert.
Dat kan een hoop tijd schelen.
Ik run de query maar eenmalig.

Ik vraag een connectie aan (van de pool)
Geef de query string door met de ?'s op de plaats van de parameters
Zet de parameters, execute de query, lees de resultset naar een eigen structuur uit en sluit de connection.

Ik gebruik het prepared statement dus niet voor het meermalig veranderen van parameters, maar uitsluitend voor de automatische escaping die het met zich meebrengt.

Dat het een hoop tijd kan schelen als je wel meerdere malen de query execute begrijp ik, maar hier gaat het om 1 run met een gewoon statement en 1 run met een prepared statement. Daar kan toch niet zo onwijs veel performance verschil in zitten?

It's shocking to find how many people do not believe they can learn, and how many more believe learning to be difficult.


  • misfire
  • Registratie: maart 2001
  • Laatst online: 07-09 15:51
Kun je een (versimpeld) voorbeeld laten zien van de prepared statement code? Kun je aan de Postgres kant de uitgevoerde SQL code tracen, zodat je kunt zien of hier verschillen in ontstaan? Prepared Statements kunnen afhankelijk van de JDBC driver en database behoorlijk duur zijn en daarom zijn ze lang niet altijd de beste keuze voor performance, maar je hebt hier meestal wel opties voor in de driver om dit te optimaliseren. 20 seconden is inderdaad extreem lang dus er is wellicht meer aan de hand.

  • flowerp
  • Registratie: september 2003
  • Laatst online: 16-09 13:18
misfire schreef op dinsdag 24 januari 2006 @ 23:15:
Kun je een (versimpeld) voorbeeld laten zien van de prepared statement code?
Ja hoor, die ziet er ongeveer zo uit:

Java:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 try {          
    connection = getConnection(); // connection from DBCP pool      
    PreparedStatement stmt = connection.prepareStatement(sqlTextPrep);
    int i = 1;          
    for ( Object value : params ) { // params is a list with the parameters
        stmt.setObject(i,value);
        i++;                
    }
                        
    ResultSet rs = stmt.executeQuery();
    .. // convert rs to own structure       
}       
finally {
    try {
        if ( connection != null ) {
            connection.close();
        }
    }
    catch (SQLException e) {
        e.printStackTrace();
    }               
}


Opzich redelijk standaard lijkt me, hoewel het natuurlijk wel opvalt dat ik alles als object zet. Dat komt omdat ik in de omgeving waar de code draait de types niet heb. Wel merk ik dat de types wel gechecked worden. Zet ik "abs" als parameter terwijl die placeholder in de query alleen een integer kan zijn, dan komt er zoals verwacht een exception.

Waar ik nog een klein beetje aan zit te denken is aan de bekende bug in postgres 7.4. Als je daar een biginteger type had dan moest je die in de query tussen quotjes zetten (als string dus), want anders werd de index niet gebruikt.
Kun je aan de Postgres kant de uitgevoerde SQL code tracen, zodat je kunt zien of hier verschillen in ontstaan?
Dat ga ik zeker proberen. Desnoods doe ik een lokale installatie van PG 8.1.
Prepared Statements kunnen afhankelijk van de JDBC driver en database behoorlijk duur zijn en daarom zijn ze lang niet altijd de beste keuze voor performance
Ik snap dat het voor 1 enkele query execute mogelijk langzamer is omdat er zowieso twee maal gecommuniceerd wordt met de DB. Ik verwachtte eigenlijk meer dat de tijd zou toenemen met een paar ms, niet meer dan een factor 20.

It's shocking to find how many people do not believe they can learn, and how many more believe learning to be difficult.


  • mark platvoet
  • Registratie: februari 2002
  • Laatst online: 19-08-2010

mark platvoet

Moutarde apres le diner

beetje offtopic, maar dit is een typisch voorbeeld waar je de enhanced for-loop niet gebruikt. :)

Religion has no place in public schools the way facts have no place in organized religion


  • Varienaja
  • Registratie: februari 2001
  • Laatst online: 19:50

Varienaja

Wie dit leest is gek.

Het enige dat ik kan bedenken is dat de DB queries met vraagtekentjes slecht kan analyseren, terwijl het stukken beter gaat als er parameters ingevuld zijn.

Voorbeeld (de tabel bevat een index op 'veld', en de waarden daarin liggen tussen 0 en 1000):
code:
1
2
3
select * from tabel where veld>999

select * from tabel where veld>?

De DB kan in het eerste geval 99,9% van de database negeren, terwijl de optimizer daar in het onderste geval geen zicht op heeft. (Of toch in elk geval pas als de parameters worden ingevuld).

Is het misschien mogelijk om toch een vaste clausule in je where op te nemen die de selectie fors beperkt?

Gras groeit niet door eraan te trekken.


  • Lethalis
  • Registratie: april 2002
  • Niet online
Wat voor datatypes gebruik je voor de parameters?

Onnodige conversies kunnen dodelijk zijn bij queries op grote tabellen :) Je krijgt dan namelijk veel meer I/O.

Even a broken clock is right twice a day.


  • Cuball
  • Registratie: mei 2002
  • Laatst online: 01-10 10:28
Lethalis schreef op woensdag 25 januari 2006 @ 12:29:
Wat voor datatypes gebruik je voor de parameters?

Onnodige conversies kunnen dodelijk zijn bij queries op grote tabellen :) Je krijgt dan namelijk veel meer I/O.
Ik denk ook dat hier enorm veel conversies gebeuren bij de query op de databank (doordat je niet het juiste type meegeeft aan je preparedstatement).

Je kan misschien voordat je je setObject uitvoert eerst achterhalen welke type het is en dan de juiste setter hiervoor gebruiken...

"Live as if you were to die tomorrow. Learn as if you were to live forever"


  • jochemd
  • Registratie: november 2000
  • Laatst online: 08-09 14:30
Ik zou het zoeken in de richting die Varienaja aangeeft. Laat je SQL statement eens zien met een EXPLAIN ANALYZE er bij.

  • henk_DE_man
  • Registratie: december 2001
  • Laatst online: 23-12-2006
mark platvoet schreef op woensdag 25 januari 2006 @ 08:46:
beetje offtopic, maar dit is een typisch voorbeeld waar je de enhanced for-loop niet gebruikt. :)
Hoezo niet? Heeft niks met de enhanced for-loop zelf te maken. Het had net zo goed een iterator kunnen zijn over een list. Stukje code kan net zo goed onderdeel zijn van een query management systeem. Dat hebben wij ook. Alle queries staan als losse .sql bestanden in een aparte directory, en via een manager worden deze ingelezen en van parameters voorzien. Dan heb je je rijtje parameters dus altijd in een list zitten en kun je niet aparte setters aanroepen.

Wat wij echter doen is het type meegeven. setObject heeft namelijk een overload, waarbij de 3de parameter een javax.sql type is. Het hangt sterk van de query af, maar bij sommige vervelende distributies van waarden kan prepared inderdaad langzamer zijn.

Ik snap zelf ook niet waarom het -zoveel- langzamer is als je de types niet meegeeft. Je zou toch zeggen dat postgres 1 maal de conversie doet en niet telkens voor elke rij weer? Als dat laatste gebeurd kan ik me goed indenken dat de boel enorm vertraagd. Ik zou het weer eens moeten opzoeken, maar volgens hadden wij ook een grote factor vertraging zonder type erbij. (wat varienaja zegt speelde ook wel iets mee, maar veel minder heftig)

[Voor 3% gewijzigd door henk_DE_man op 25-01-2006 20:21]


  • momania
  • Registratie: mei 2000
  • Nu online

momania

iPhone 30! Bam!

henk_DE_man schreef op woensdag 25 januari 2006 @ 20:18:
[...]


Hoezo niet? Heeft niks met de enhanced for-loop zelf te maken. Het had net zo goed een iterator kunnen zijn over een list. Stukje code kan net zo goed onderdeel zijn van een query management systeem. Dat hebben wij ook.
Hij bedoelt dit denk ik omdat er ook een int i gebruikt wordt.
Die staat nu buiten de scope van de for loop terwijl deze alleen maar nodig is binnen de for loop.

In dit geval hoor je dus gewoon de volgende loop te gebruiken om de scop van je variabelen zo klein mogenlijk te maken:
Java:
1
2
3
for (int i=; i<params.size(); i++) {
    stmt.setObject(i,params.get(i));
}

Neem je whisky mee, is het te weinig... *zucht*


  • henk_DE_man
  • Registratie: december 2001
  • Laatst online: 23-12-2006
momania schreef op woensdag 25 januari 2006 @ 20:23:
[...]

Hij bedoelt dit denk ik omdat er ook een int i gebruikt wordt.
Die staat nu buiten de scope van de for loop terwijl deze alleen maar nodig is binnen de for loop.
Aha, op die fiets. Ik keek daar even overheen, maar ben het er wel helemaal mee eens. In java is dit scope effect nog erger dan in bv C++ omdat je niet mag overriden. Bv

code:
1
2
int i=0;
{ int i =1; }


Dit mag niet in Java. (let op, for, while, if, etc vallen hier ook onder)
In dit geval hoor je dus gewoon de volgende loop te gebruiken om de scop van je variabelen zo klein mogenlijk te maken:
Java:
1
2
3
for (int i=; i<params.size(); i++) {
    stmt.setObject(i,params.get(i));
}
Ik weet niet zeker of ik het hier mee eens ben. get(i) op een collection in mogelijk ineffecient en probeer ik altijd zo veel mogelijk te vermijden. Het is een micro dingetje voor kleine aantallen, maar kan erg gaan meespelen bij grote aantallen als de de list implementatie bv een linked list is. Nu verwacht ik niet dat een query miljoenen parameters mee krijgt :+ maar toch...

Liever zou ik dus een iterator gebruiken, maar dan heb je nog steeds geen i. In java mag een for loop helaas alleen variabelen van hetzelfde type declareren in het initializer stuk.

bv

Java:
1
2
3
for ( int i=0, Iterator iter = params.iterator; iter.hasNext(); i++ ) {
   stmt.setObject(i, iter.next() );
}


Bovenstaande stukje zal dan ook niet compilen.

De next() werk ook lelijk als je een object terug krijgt waarvan je de onderdelen apart moet zetten in een functie. Dan moet je weer een temp object gaan aanmaken:

Java:
1
2
3
4
for ( int i=0, Iterator iter = params.iterator; iter.hasNext(); i++ ) {
   SomeObj myObj = iter.next();
   stmt.setObject(i, myObj.getValue(), myObj.getType() );
}


Sommige systemen bieden een for each loop aan icm een counter variable. Dat is bijvoorbeeld zo in de JSTL loops.

Wat overigens wel kan in Java, maar wat kwa style mischien bedenkelijk is:

Java:
1
2
3
{ int i = 1; for ( Object value : params ) 
    stmt.setObject(i++,value);                
}

[Voor 6% gewijzigd door henk_DE_man op 25-01-2006 22:30]


  • momania
  • Registratie: mei 2000
  • Nu online

momania

iPhone 30! Bam!

Je kan altijd nog itereren met de ListIterator ;)

Java:
1
2
3
4
ListIterator iterator = params.listIterator();
while(iterator.hasNext()) {
    stmt.setObject(iterator.nextIndex(), iterator.next());
}

Zo heb je helemaal geen aparte int nodig als teller :Y)

Neem je whisky mee, is het te weinig... *zucht*


  • mark platvoet
  • Registratie: februari 2002
  • Laatst online: 19-08-2010

mark platvoet

Moutarde apres le diner

Daar doelde ik inderdaad op, ik had alleen zelf even over het hoofd gezien dat het een List van parameters betrof. Dus dan is hoe dan ook de oplossing niet ideaal zoals henk_de_man schetst. Toch zou ik wel voor een traditionele for loop gaan omdat ik vind dat in de body van een loop 'enkel' logica moet staan die betrekking heeft op het huidige element. Het incrementeren van een counter hoort daar dan dus niet bij.

Ik zou overigens bij prepared statements de voorkeur geven aan arrays. Het aantal argumenten staat immers (doorgaans) vast.Maar goed ik neem aan dat in dit specifieke geval een List een logischere keuze is.

Ja en wat betreft het probleem, dat zou ik echt niet weten. Ik zou ook, zoals reeds gesuggereerd, kijken wat de statements worden en waar dan het probleem ligt.
momania schreef op woensdag 25 januari 2006 @ 22:34:
Java:
1
2
3
4
ListIterator iterator = params.listIterator();
while(iterator.hasNext()) {
    stmt.setObject(iterator.nextIndex(), iterator.next());
}
ik zou dit altijd nog in een for-loop schrijven tenzij de iterator daadwerkelijk belang heeft buiten de loop.
Java:
1
2
3
for(ListIterator iterator = params.listIterator();iterator.hasNext();) {
    stmt.setObject(iterator.nextIndex(), iterator.next());
}

[Voor 28% gewijzigd door mark platvoet op 25-01-2006 22:43. Reden: finally goed...]

Religion has no place in public schools the way facts have no place in organized religion


  • flowerp
  • Registratie: september 2003
  • Laatst online: 16-09 13:18
Ik ben de afgelopen tijd wat aan het testen geweest en de extreme traagheid bleek inderdaad voornamelijk te komen door het niet meegeven van de types. Als ik bij setObject als 3de parameter het type meegeef, dan wordt de query 'bijna' net zo snel als de non-prepared versie.

Blijkbaar doet postgresql dus een conversie per vergelijking ipv 1 keer voor het executeren van de query. Dit is eigenlijk wel vreemd, want het lijkt zo'n voor de hand liggende operatie voor een DBMS om te doen.

De extreme traagheid is er dus uit, maar helaas zet er nog wel een verschil in van zo'n 20 ~ 25% tussen prepared en non-prepared. Bijvoorbeeld 1 query doet er non-prepared 10 seconden over en prepared 13 seconden. Als ik dezelfde query minder parameters mee geef, dan gaat ie opzich een stuk sneller en merk je het verschil tussen prepared en non prepared ook minder.

1 van de parameters is bijvoorbeeld een status. Als ik daar maar 1'tje van meegeef als parameter (where status in (?) ), dan duurt de query prepared 1416ms en non-prepared 1257ms. Helaas kan ik dit verschil niet wegkrijgen. Ik heb geprobeerd om de prepared statement cache van DBCP aan te zetten, maar dit heeft 0.0 effect.

Wat wel hielp, maar een beetje raar is, is dat bij gebruik van de 7.4 JDBC driver (voor de 8.1 DB dus), alles -wel- even snel is. Maar dit is een 'oplossing' die ik liever niet gebruik.

It's shocking to find how many people do not believe they can learn, and how many more believe learning to be difficult.


  • jochemd
  • Registratie: november 2000
  • Laatst online: 08-09 14:30
De 7.4 JDBC driver gebruikt versie 2 van het wire protocol. De 8.1 JDBC driver gebruikt versie 3. Je kan de 8.1 driver met het toevoegen van een parameter ook dwingen om een fallback te doen naar het versie 2 protocol. Google is your friend. Risico als je dat doet is overigens wel dat je voor de meeste queries sneller bent, maar voor enkele corner cases veel langzamer. YMMV.

  • flowerp
  • Registratie: september 2003
  • Laatst online: 16-09 13:18
jochemd schreef op zondag 29 januari 2006 @ 13:41:
De 7.4 JDBC driver gebruikt versie 2 van het wire protocol. De 8.1 JDBC driver gebruikt versie 3. Je kan de 8.1 driver met het toevoegen van een parameter ook dwingen om een fallback te doen naar het versie 2 protocol. Google is your friend. Risico als je dat doet is overigens wel dat je voor de meeste queries sneller bent, maar voor enkele corner cases veel langzamer. YMMV.
Hmmm... het wire protocol? Zal ik wel even naar googlen dan. Toevallig komen wel alle PG JDBC drivers in de verschillende JDBC smaken uit en die hebben toevallig dezelfde nummers. JDBC 1, 2 en 3. Zowel de 7.4 driver die ik gebruikte als de 8.1 zijn JDBC3. Wire protocol is toch niet hetzelfde als deze driver varianten die je kunt downloaden vanaf de offciele site?

Weet je mischien ook waarom wire 2 dan zoveel sneller is als wire 3? Of anders gezegd, waarom in wire 3 er dan verschil zit tussen not-prepared, prepared zonder types en prepared met types? Bij de 8.1 driver (en ook de 8.0) geven deze alle 3 verschillende uitkomsten, terwijl ze bij de 7.4 gemiddeld bijna tot op de ms gelijk zijn.

It's shocking to find how many people do not believe they can learn, and how many more believe learning to be difficult.


  • jochemd
  • Registratie: november 2000
  • Laatst online: 08-09 14:30
flowerp schreef op zondag 29 januari 2006 @ 14:54:
[...]


Hmmm... het wire protocol?
Het protocol dat tussen client en server wordt gesproken. Omdat client en server meestal met een wire verbonden zijn wordt aan dat protocol ook wel gerefereerd als het wire-protocol. In de documentatie heet het Frontend/Backend Protocol
Zal ik wel even naar googlen dan. Toevallig komen wel alle PG JDBC drivers in de verschillende JDBC smaken uit en die hebben toevallig dezelfde nummers. JDBC 1, 2 en 3. Zowel de 7.4 driver die ik gebruikte als de 8.1 zijn JDBC3. Wire protocol is toch niet hetzelfde als deze driver varianten die je kunt downloaden vanaf de offciele site?
Nee, dat nummer slaat op de versie van de JDBC API die door de driver wordt geimplementeerd.
Weet je mischien ook waarom wire 2 dan zoveel sneller is als wire 3? Of anders gezegd, waarom in wire 3 er dan verschil zit tussen not-prepared, prepared zonder types en prepared met types? Bij de 8.1 driver (en ook de 8.0) geven deze alle 3 verschillende uitkomsten, terwijl ze bij de 7.4 gemiddeld bijna tot op de ms gelijk zijn.
Omdat pas in versie 3 prepared statements in het protocol ondersteund worden. Daarvoor werden prepared statements door de driver geimplementeerd als gewone queries. Kennelijk is in jouw geval een prepared statement langzamer dan een gewone query. Dat heeft ongetwijkfeld te maken met het feit dat de optimizer een prepared statement moet optimalizeren op het moment van compileren, terwijl een gewone query op het moment van uitvoeren geoptimaliseerd kan worden en dan heeft de optimizer meer informatie (de waardes van de parameters) en kan hij zijn werk dus beter doen.

Als je zoals al eerder gezegd de EXPLAIN ANALYZE output van je queries gaat bekijken zie je vanzelf wat er aan de hand is.
Pagina: 1


Apple iPhone 12 Microsoft Xbox Series X LG CX Google Pixel 5 CES 2020 Samsung Galaxy S20 4G Sony PlayStation 5 Nintendo Switch Lite

'14 '15 '16 '17 2018

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2020 Hosting door True