Improve JDBCTextFeatureWriter to use Prepared Statements

Andrea Aime added a comment - 23/Jul/04 3:19 AM For the record, Postgis is using prepared statements for writes when using the wkb4j libraries. As for a possible speed increase, the following applies: * postgres driver does not support prepared statements, it just fakes them, so there is no speed increase in this case (I needed to use them in order to support wkb4j); * a database that really supports them will optimize the query just once, and then only execute it at each call. Since usually one more round trip to the database is made, there is a speed increase only if you make enough inserts/updates/deletes, usually 20 to 50 depending on the database and the nature of the query. To really get a sizeable speed increase we should use batch of statements, that really cut the round trips to the database (using addbatch/executebatch). This way the communication with the database occurs only on executebatch. I propose the following: * you port the prepared statement code from postgres to the other databases; * I will follow, back from the holidays, and try to add the batch of statements support with some classes of my own that make statement batching as transparent as possible. What do you think?

Chris Holmes added a comment - 23/Jul/04 9:44 AM That's too bad postgres doesn't actually support it. Where is the code for the prepared statements in postgis? I couldn't find it (though I wasn't quite sure where to look). I'll check it out. This actually isn't an incredibly high priority for me, I just was changing oracle to use the JDBCTextReader, since it actually worked, unlike the JDBCFeatureWriter. Jody wanted me to put a note that it could get faster with prepared statements. Someday I'll work on speed increases for writing, but it may be a while. I think things are good enough now - I'll ask for your help when I start to think about speed - and we can try out the batch queries.

Chris Holmes added a comment - 04/Oct/04 4:00 PM This isn't getting fixed soon...

Jody Garnett added a comment - 28/Jul/05 8:34 PM This is not a blocker.

Jody Garnett added a comment - 27/Mar/06 7:08 AM Confirmed that the approach works in Oracle, will revisit and see if the approach can be generalized.

Gabriel Roldan added a comment - 11/Aug/08 4:52 PM PostGISDataStore is already using prepared statements for inserts and updates. The unresolved problem is still dealing with potentially malicious literals in filters. This is because a normal read only query may contain a filter literal with malicious code, I still need to get a good test case for it, but to completely solve the problem the approach would be to turn the Filter2SQL visitor into one that produces a statement usable in a JDBC prepared statement and provides the list of value objects to fill in the prepared statement value holders. like in {{SELECT a,b,c from FT where b like ? AND c = ?}} instead of {{SELECT a,b,c from FT where b like 'fake'; drop table FT;...}

Gabriel Roldan added a comment - 28/Jul/09 12:11 PM closing as won't fix as the jdbc-ng datastores can already use prepared statements for good