CODEWITHSUNDEEP
javasqlperformanceprepared-statement
Russell
Russell

Reputation: 41

What is wrong with my SQL PreparedStatement?

I'm reading a lot of data from three tables in an Access database ~2 million rows, maybe 100MB of text. I was using the following SELECT statement:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + databaseLocation + databaseName +";selectMethod=cursor; READONLY=true; TYPE=FASTLOAD";

con = DriverManager.getConnection(url);

String SQL = "SELECT ADDRESS_MODEL.ADDR_LINE_1, ADDRESS_MODEL.ADDR_LINE_2, LOCALITIES.NAME, ADDRESS_MODEL.SECONDARY_LOCALITY, ADDRESS_MODEL.POST_TOWN, ADDRESS_MODEL.COUNTY, BUILDINGS.ED_ID FROM (ADDRESS_MODEL LEFT JOIN BUILDINGS ON ADDRESS_MODEL.BUILDING_ID = BUILDINGS.BUILDING_ID) LEFT JOIN LOCALITIES ON BUILDINGS.LOCALITY_ID = LOCALITIES.LOCALITY_ID WHERE BUILDINGS.COUNTY_ID = " + county_ID;

Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery(SQL);

This was taking a while (approx 2 mins) so I looked for ways to make it faster. I read about prepared statements and decided to try making one. Following various tutorials I put this together:

String SQL = "SELECT ADDRESS_MODEL.ADDR_LINE_1, ADDRESS_MODEL.ADDR_LINE_2, LOCALITIES.NAME, ADDRESS_MODEL.SECONDARY_LOCALITY, ADDRESS_MODEL.POST_TOWN, ADDRESS_MODEL.COUNTY, BUILDINGS.ED_ID FROM (ADDRESS_MODEL LEFT JOIN BUILDINGS ON ADDRESS_MODEL.BUILDING_ID = BUILDINGS.BUILDING_ID) LEFT JOIN LOCALITIES ON BUILDINGS.LOCALITY_ID = LOCALITIES.LOCALITY_ID WHERE BUILDINGS.COUNTY_ID = ?";

PreparedStatement prest = con.prepareStatement(SQL);
prest.setString(1, county_ID);

ResultSet result = prest.executeQuery();

It still works, but now it takes over 15 mins. I've had a good Google but I can't seem to spot what I'm doing wrong. Are prepared statements just not appropriate for my particular problem?

Upvotes: 0

Views: 1380

Answers (1)

royalsampler
royalsampler

Reputation: 1195

The main performance gain from using PreparedStatement is given when you want to execute the same query multiple times. The database constructs the query when the statement is prepared and then re-uses this every time the statement is executed to avoid overhead. If you're only executing the query once then it's likely you won't see any performance improvement from using PreparedStatement.

The main argument for using PreparedStatement is to protect against SQL injection

Upvotes: 1

Related Questions