Comparing Statements, PreparedStatements and JSTL on a MySQL DB
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:
try {
PreparedStatement ps = conn.prepareStatement(sqlStatement);
setParameters(ps, parameters);
ResultSet rs = ps.executeQuery();
result = new ResultImpl(rs, startRow, maxRows);
ps.close();
}
catch (Throwable e) {
throw new JspException(sqlStatement + ": " + e.getMessage(), e);
}
Wow, I thought. That's horrible! JSTL is creating a prepared statement for every query 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("nn Statement:" + (System.currentTimeMillis() - time) + "nn");
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("nn PreparedStatement (in):" + (System.currentTimeMillis() - time) + "nn");
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("nn PreparedStatement (out):" + (System.currentTimeMillis() - time) + "nn");
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("nn JSTL:" + (System.currentTimeMillis() - time) + "nn");
%>
</p>
</body>
</html>
The results are astounding:
Statement: 1,792ms
PreparedStatement (in): 1,969ms
PreparedStatement (out): 1,626ms
JSTL: 28,596ms
Yes, using JSTL is around 10 times slower than a PreparedStatement [LATER: see below, actually], 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.
-Russ
Update: It dawned on me looking at the test code again that I was doing the conn = ds.getConnection() outside the loop, whereas JSTL was doing that inside the loop. Since this was a test that to me was simulating page hits, this is an important metric. In fact, I should probably put everything that happens, from the JNDI lookup for the shared DataSource to the getConnection() in the loop to most accurately replicate the pages - or better yet, separate these calls all out into their own page and use a product like JMeter to measure actual performance, if that's what I want to do.
Anyways, the first thing I learned is still valid: Statements and PreparedStatements are the same when using MySQL. The second thing isn't, it seems, as when I put the connection call in the first two loops, the times for the tests shoot up to be *slower* than the JSTL call. So in fact, JSTL is doing some magic to make it more efficient. Behold:
Statement: 36,984ms
PreparedStatement (in): 41,871ms
PreparedStatement (out): 1,598ms
JSTL: 30,743ms
Now isn't *that* confusing. Maybe JSTL doesn't suck after all, and in fact is doing the right thing. God knows what this all means.
-R