I'm devoping an app that accesses an embedded database (Apache Derby right now). It is single user and desktop.
A certain functionality of the application requirmes to read a _lot_ of data from a certain table (think millions).
So, for that particular part, we decided to get a bit dirtier, and go into SQL.
The table in question just has three columns: a FK that also acts as "column", another integer that acts as the "row" (and when mapped to Hibernate also acts as the list-index) and the actual column that contains the data (type float).
The problem is, I'm getting a HUGE performance loss when I try to do things proper and use named parameters.
Code:
Long id = 1;
int orderLow = 0;
int orderHigh = 5000; //These three columns usually are passed to the function as parameters
SQLQuery query = mySession.createSQLQuery("SELECT cdata from data where columnnumber = :cn AND rownumber >= :ol AND rownumber < :oh ORDER BY rownumber");
query.setParameter("cn", id, StandardBasicTypes.LONG);
query.setParameter("oh", orderHigh, StandardBasicTypes.INTEGER);
query.setParameter("ol", orderLow, StandardBasicTypes.INTEGER);
query.addScalar("cdata", StandardBasicTypes.FLOAT);
query.list();
this takes around 3 seconds to execute... which obviously isn't right. On the other hand...
Code:
StringBuilder sb = new StringBuilder("SELECT cdata from data where columnnumber = ");
sb.append(id);
sb.append(" AND rownumber >=");
sb.append(orderLow);
sb.append(" AND rownumber <");
sb.append(orderHigh);
sb.append(" ORDER BY rownumber);
SQlQuery query= mySession.createSQLQuery(sb.toString());
query.addScalar("cdata", StandardBasicTypes.FLOAT);
query.list();
(I know this is a bad practice. Just using it for comparision porposes).
This takes between 0.02 and 0.04 seconds to execute. Which is quite closer to the actual performance I'm getting with other clients connecting to the same database.
So... any ideas? I'm seriously confused as to what this is happening.
Login
FAQ
Search