This post originated from an RSS feed registered with Java Buzz
by Russell Beattie.
Original Post: Comparing Statements, PreparedStatements and JSTL on a MySQL DB
Feed Title: Russell Beattie Notebook
Feed URL: http://www.russellbeattie.com/notebook/rss.jsp?q=java,code,mobile
Feed Description: My online notebook with thoughts, comments, links and more.
So I was using some JSTL SQL queries to produce XML pages. Doing it in JSTL is quick an easy and I figured wouldn't be that much slower than doing it in a servlet or JSP with scriptlet. Wow, was I wrong.
First, though, let me say that I was thinking about it in the bathroom (where I do most of my thinking) and I thought "I bet you deep inside of JSTL, they're only using Statements, not PreparedStatements" - so I went diving into the chaos that is the JSTL source code and sure enough, I found this deep in the heart of the QueryTagSupport.java class:
Wow, I thought. That's horrible! JSTL is creating a prepared statement for every even if there are no parameters being sent. Oof. That must be a major hit, I thought, as I've seen articles that stated that PreparedStatements can be 65 times as slow as Statements.
I happened to be online with Erik at the time, so I told him what I found and he told me that it was up to the driver, actually. Then he went routing around in the MySQL JDBC driver and sure enough, there's no fundamental difference between the two methods when it talks to the server. To prove it, I decided to write a quick test. First, I just did a Statement verses a PreparedStatement, looking up one record (it's the lookup that counts, since the Results will be the same) and returning it 10,000 times. The results amazed me: Statements came back in around 1.7 seconds, and Prepared statements around 1.9. No big difference at all - even when I moved the prepareStatement() outside the loop. I increased the number of queries and there still was no major difference.
Then after chatting with Erik for another bit, we decided to test the JSTL code as well to see if it had any performance hits. Wow again. The first query of 100,000 hits took so long I thought I wacked my server, so I modified the code to not print out the results, backed the loops down to 10,000 and ran the page again.
Here's the test code:
<%@ page import="java.sql.*, javax.naming.*, javax.sql.*, java.io.*, java.util.*"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<html>
<body>
<sql:setDataSource dataSource="jdbc/TestDS"/>
<p>
<%
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:/comp/env");
DataSource ds = (DataSource)envCtx.lookup("jdbc/TestDS");
Connection conn = ds.getConnection();
long time = System.currentTimeMillis();
for(int i = 0; i < 10000; i++ )
{
Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = s.executeQuery("select * from test where id=100803");
while(rs.next()){
//out.write(rs.getString("id"));
}
rs.close();
s.close();
}
out.write("\n\n Statement:" + (System.currentTimeMillis() - time) + "\n\n");
time = System.currentTimeMillis();
for(int i = 0; i < 10000; i++ )
{
PreparedStatement ps = conn.prepareStatement("select * from test where id = ?");
ps.setInt(1, 100803);
ResultSet rs = ps.executeQuery();
while(rs.next()){
//out.write(rs.getString("id"));
}
rs.close();
ps.close();
}
out.write("\n\n PreparedStatement (in):" + (System.currentTimeMillis() - time) + "\n\n");
time = System.currentTimeMillis();
PreparedStatement ps = conn.prepareStatement("select * from test where id = ?");
for(int i = 0; i < 10000; i++ )
{
ps.setInt(1, 100803);
ResultSet rs = ps.executeQuery();
while(rs.next()){
//out.write(rs.getString("id"));
}
rs.close();
}
ps.close();
out.write("\n\n PreparedStatement (out):" + (System.currentTimeMillis() - time) + "\n\n");
time = System.currentTimeMillis();
for(int i = 0; i < 10000; i++ )
{
%><sql:query var="rs">select * from test where id=100803</sql:query><c:forEach var="row" items="${rs.rows}"></c:forEach><% }
out.write("\n\n JSTL:" + (System.currentTimeMillis() - time) + "\n\n");
%>
</p>
</body>
</html>
Yes, using JSTL is around 10 times slower than a PreparedStatement, and it scales for shit. Holy Crap! What the hell is that code doing? Well, first, it's throwing the entire results into a HashMap, that's definitely not particularly efficient, but then after that I'm not sure. What I do know is that a regular scriptlet .jsp page that produces a .java file of around 60 lines of code, produces a .java file that's 500(!!) lines of code if I duplicate the functionality using JSTL.
That's all serious food for thought. I really like JSTL because it's quick to develop in, but not if there's this much of a performance hit. I need to test the XML stuff as well, since I'm using that in lots of different places. I just can't believe how innefficient that SQL stuff is! I'm definitely re-writing all the quick-and-dirty XML generating code I had done using the JSTL SQL tags though. No question.
Previous Topic
