SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-20-2008, 12:46 PM
| ||||
| ||||
 SQL from Java - PREPARE (with parameter markers) vs. Dynamic Maybe this has been asked and answered - but I have been looking through old archives for quite some time.... Typical Java app running from Websphere connected to DB2 V8.2FP15 on Linux using JDBC Type 4 drivers. Programmers use mostly PREPARE/EXECUTE logic for security and 'other' reasons - but some SQL executes MUCH slower than same SQL using plain old SQL with values then the SQL with variable markers. Worst case 20 seconds vs. 1 second. Best case 4 seconds vs. .2 seconds. I've read about REOPT(ALLWAYS), etc. but can't figure out HOW this could be implemented in Java or at the database for SQL being submitted by these Java apps. Any help would be appreciated.      |
|
03-20-2008, 12:46 PM
| ||||
| ||||
| Re: SQL from Java - PREPARE (with parameter markers) vs. Dynamic m wrote: > Maybe this has been asked and answered - but I have been looking through > old archives for quite some time.... > > Typical Java app running from Websphere connected to DB2 V8.2FP15 on > Linux using JDBC Type 4 drivers. Programmers use mostly PREPARE/EXECUTE > logic for security and 'other' reasons - but some SQL executes MUCH > slower than same SQL using plain old SQL with values then the SQL with > variable markers. Worst case 20 seconds vs. 1 second. Best case 4 > seconds vs. .2 seconds. I've read about REOPT(ALLWAYS), etc. but can't > figure out HOW this could be implemented in Java or at the database for > SQL being submitted by these Java apps. Any help would be appreciated. Main goal is to let programmers continue to use PREPARE/EXECUTE - but somehow implement REOPT(ONCE) or REOPT(ALLWAYS) to get the SQL optimizer to pick an access plan based on actual predicate values. I know REOPT is a BIND option - so again - how can this be implemented on dynamic SQL packages? |
Linear Mode
