Recent Posts

Tuesday, 13 November 2018

About PreparedStatement

     In the case of normal Statement, whenever we are executing SQL Query, every time compilation and execution will be happened at database side.
Statement st = con.createStatement();
ResultSet rs = st.executeQuery ("select * from emp");
Total Time per Query = Request Time + Compile Time + Execution Time+Response Time
                                    = 2 ms + 1 ms + 1 ms + 3 ms = 7 ms
per 1000 Queries = 7 * 1000ms = 7000ms


     Sometimes in our application,we required to execute same query multiple times with same or different input values.
E.g 1
     In IRCTC application,it is common requirement to list out all possible trains between 2 places.
select * from trains where source='XXX' and destination='YYY';
     Query is same but source and destination places may be different. This query is required to execute lakhs of times per day.

E.g 2
     In BookMyShow application, it is very common requirement to display theatre names where a particular movie running/playing in a particular city.
select * from theatres where city='XXX' and movie='YYY';

     In this case this query is required to execute lakhs of times per day. May be with different movie names and different locations. For the above requirements if we use Statement object, then the query is required to compile and execute every time, which creates performance problems. To overcome this problem, we should go for PreparedStatement.
     The main advantage of PreparedStatement is the query will be compiled only once even though we are executing multiple times, so that overall performance of the application will be improved. We can create PreparedStatement by using prepareStatement() method of Connection interface.
Example
PreparedStatment pst = con.prepareStatement(sqlQuery);

     At this line,sqlQuery will send to the database. Database engine will compile that query and stores in the database. That pre compiled query will be returned to the java application in the form of PreparedStatement object.
     Hence PreparedStatement represents "pre compiled sql query". Whenever we call execute methods,database engine won't compile query once again and it will directly execute that query,so that overall performance will be improved.

Total Time per Query = Request Time + Response Time + Execution Time
                                = 2 ms + 1 ms + 3 ms = 6 ms
1000 Queries = 6 * 1000 = 6000 ms

Next Tutorial : Steps to develop JDBC Application by using PreparedStatement

Previous Tutorial : Working with MySQL Database  

No comments:

Post a Comment