Hi Ralf, Yes creating test is an option to measure (to get an idea) the improvement because I also did some similar when I came to know about the performance improvement using PrepareStatment and its Batch feature. It was a Live project and I used simple Statements with long transactions everywhere and I was bit busy with other things that time (or you can say lazy :p ), so I wanted to make sure if the improvement is enough rewarding to spend that much time. It was like a miracle for me to see the performance improvement in the transactions (49 x 900 x 6 quires at a time) that time and more when I found about Batch. So I decided to investigate more about PrepareStatement and these things I found.
1. 1000 queries, 1(first) registered(validated, optimized)and remaining 999 will follow the first one (no validation, no optimization). Now, this validation(syntax) and query optimization(internal engine process) varies from engine to engine. In whole this is more of an mathematical (probably Relational Calculus and Query Optimization) analysis to give the answer how much improvement we will get because people have variables like var1 - Query (Sub variables - Complexity, Where, SubQuery, Parameters etc) var2 - No. of queries per transactions var3 - others So the cost of var1 will be involved for every query while using Statement, but var1 will cost only once if we use PrepareStatement. So here what give us the improvement is var1 mostly and improvement depends on the cost of the var1. 2. excuteBatch(), It's a network latency issue (depends on the DB and Application server network) . In my case it was huge as the Application server and DB server was at different physical location. 3. It is recommended by OWASP (owasp.org) that PreparedStatment is secure from SQL injections because the query registers first with the DB and the later parameter get into it by using bind, so one can't extend it to modify the query. eg: query = "SELECT * FROM <MYTABLE> WHERE id = "+p; Now the Parameter p is coming from the request.getParameter which if not check someone could a vulnerability. if I send p = 123; DROP <MYTABLE> But this PreparedStatement this doesn't occur even if you be careless with parameters(but careful with query). I might be repeating myself but I think that test is a good idea, and I would like to finally add that you can see as much as improvement as you want by increasing the cost of var1 and var2 in Point 1 and for excuteBatch() by Network Latency. Cheers, Udai Gupta On 5/16/09, Ralf Joachim <ralf.joac...@syscon.eu> wrote: > Thanks for sharing your opinion Udai. > > I guess it will be best to create a small test to get an impression on > how big the improvement would be. Will create a task for Ahmad to test this. > > Regards > Ralf > > Udai Gupta schrieb: > > > Hi, > > > > In my personal experience with JDBC I know that for reusing Prepared > > statements really gives a huge performance improvement as the JDBC. > > > > Suppose we have "INSERT INTO <TABLE> VALUES (PARAMETER1,PARAMETER2)" > > and we want to execute it for like thousand times in a transaction. > > > > Now with simple Statement every time the Query will be > > validated(optimized internally) (1000 times) by the SQL compiler of DB > > and then data will be inserted to table. > > > > On the other hand with PreparedStatement the Query will the > > validated(optimized) only for the first time and for remaining 999 > > only parameters will be passed to Query(no validation and optimization > > overhead for other 999) > > > > Rest is just math. > > > > There is one more thing with PreparedStatement, excute() and excuteBatch(). > > excute() send parameters per query over the network (JDBC to DB server) > > while excuteBatch() sends the Batch(1000 times at ones) of parameters > > over networks. > > So using Batch gives high performance improvement if the network > > latency is costly(which mostly is the case as webserver and database > > server are separated these days). > > > > I have personally ran the tests to check these things. > > > > Regards, > > Udai Gupta > > > > > > On 5/15/09, Ralf Joachim <ralf.joac...@syscon.eu> wrote: > > > >> Hi all, > >> > >> as you may already know we have a GSoC student working at refactoring > >> SQL engine of Castor. While planing next improvement steps I had also > >> been thinking about possibilities to improve performance of execution of > >> SQL statements. I got one idea on how to improve performance but this > >> would require quite some refactoring work. As I do not know how big the > >> improvement could be, I like to hear your opinoins if it is worth to > >> spend time in this refactoring. > >> > >> > >> Current codebase of Castor works as follows: > >> > >> When a JDO configuration is loaded or used for the first time it creates > >> SQL strings with parameters for every entity of the configiguration and > >> every standard database operation executed for this entities. Standard > >> operations in this context are those that get executed by a call to > >> create(), load(), update() or remove() methods on a Database instance. > >> > >> When one of the operations from above get called Castor every time > >> prepares a java.sql.PreparedStatement, binds the required parameters and > >> executes the prepared statement. > >> > >> > >> Looking at best practis for prepared statements it is said that a > >> application should prepare a statement only once per connection as you > >> could reuse the prepared statement to bind different parameters and > >> execute it multiple times. As I expect that some database drivers and > >> about all database servers do such optimisations themself, my question > >> is, how big would be the performance improvement we could expect when > >> reusing prepared statements. > >> > >> As we need to prepare statements for every connection in any case, the > >> same prepared statement can only be reused within a single transaction. > >> According to this an application that uses an own transaction would not > >> gain any performance improvement. In the worst case we could get a very > >> small performance penulty by the code needed to handle creation and > >> closing of prepared statements in this case. > >> > >> On the other hand are there quite some applications that execute > >> hundreds or thoutheds of operations within one transaction. If all those > >> operations lead to the execution of the same SQL statement with > >> different parameters we should get a performance improvement according > >> to the best practis mentioned above. > >> > >> > >> Do anyone have experience with this? Any opinions? > >> > >> Regards > >> Ralf > >> > >> -- > >> > >> Syscon Ingenieurbüro für Meß- und Datentechnik GmbH > >> Ralf Joachim > >> Raiffeisenstraße 11 > >> 72127 Kusterdingen > >> Germany > >> > >> Tel. +49 7071 3690 52 > >> Mobil: +49 173 9630135 > >> Fax +49 7071 3690 98 > >> > >> Internet: www.syscon.eu > >> E-Mail: ralf.joac...@syscon.eu > >> > >> Sitz der Gesellschaft: D-72127 Kusterdingen > >> Registereintrag: Amtsgericht Stuttgart, HRB 382295 > >> Geschäftsleitung: Jens Joachim, Ralf Joachim > >> > >> > >> > >> > >> --------------------------------------------------------------------- > >> To unsubscribe from this list, please visit: > >> > >> http://xircles.codehaus.org/manage_email > >> > >> > >> > >> > > > > --------------------------------------------------------------------- > > To unsubscribe from this list, please visit: > > > > http://xircles.codehaus.org/manage_email > > > > -- > > > Syscon Ingenieurbüro für Meß- und Datentechnik GmbH > Ralf Joachim > Raiffeisenstraße 11 > 72127 Kusterdingen > Germany > > Tel. +49 7071 3690 52 > Mobil: +49 173 9630135 > Fax +49 7071 3690 98 > > Internet: www.syscon.eu > E-Mail: ralf.joac...@syscon.eu > > Sitz der Gesellschaft: D-72127 Kusterdingen > Registereintrag: Amtsgericht Stuttgart, HRB 382295 > Geschäftsleitung: Jens Joachim, Ralf Joachim > > > > > --------------------------------------------------------------------- > To unsubscribe from this list, please visit: > > http://xircles.codehaus.org/manage_email > > > --------------------------------------------------------------------- To unsubscribe from this list, please visit: http://xircles.codehaus.org/manage_email
