[Contrib: Evaluation System] AARON: Using SQL in eval DAO

[Jim Eng]

Reduces caching of objects.  And eliminates dirty checking.  So it will do bulk updates faster with less memory. If I understand correctly.

So the code would get objects within a StatelessSession and then update them and resave.  It seems more roundabout, but it seems like it might work better for MySQL.  Hard to tell until we can try it.  

Of course, if we moved from hibernate 3.2 to hibernate 3.5 or 3.6, the HQL query might actually work just fine for MySQL.  Who knows?

Jim


On Aug 11, 2011, at 11:36 AM, Aaron Zeckoski wrote:

> How does StatelessSession handle this?
> -AZ
> 
> 
> On Thu, Aug 11, 2011 at 11:24 AM, Jim Eng <jimeng at umich.edu> wrote:
>> It looks like StatelessSession might handle this:
>> http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/batch.html#batch-statelesssession
>> http://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/StatelessSession.html
>> Unfortunately, that doesn't seem to exist in the version of hibernate used
>> in Sakai trunk.
>> Jim
>> 
>> 
>> On Aug 11, 2011, at 10:15 AM, Jim Eng wrote:
>> 
>> Hi Aaron,
>> I am working on consolidated notifications, in which a student gets one
>> announcement or reminder a day instead of one message for each eval they can
>> take. I have written HQL in the DAO impl to update records to indicate that
>> an announcement has been sent.  The query works fine in Oracle but it is
>> deadly slow in MySQL.  I have tried revising the HQL to generate a better
>> MySQL query, but it seems that quirks in hibernate and MySQL make this
>> unlikely.  I have written SQL that performs very well in MySQL (but only
>> MySQL).
>> It's easy elsewhere in sakai to write custom SQL for specific vendors.
>>  Hibernate usually takes care of that, but it fails in this case.  If we had
>> a way in the DAO of identifying the vendor, I could do this:
>> if("mysql".equalsIgnoreCase(dialect)) {
>> StringBuilder sqlBuffer = new StringBuilder();
>> sqlBuffer.append("update EVAL_ASSIGN_USER eau, EVAL_EMAIL_PROCESSING_QUEUE
>> epq ");
>> if(sendingAvailableEmails) {
>> sqlBuffer.append("set eau.AVAILABLE_EMAIL_SENT=:dateSent ");
>> } else {
>> sqlBuffer.append("set eau.REMINDER_EMAIL_SENT=:dateSent ");
>> }
>> sqlBuffer.append("where eau.id = epq.EAU_ID and epq.USER_ID=:userId and
>> epq.EMAIL_TEMPLATE_ID=:emailTemplateId");
>> updateQuery = session.createSQLQuery(sqlBuffer.toString());
>> } else {
>> StringBuilder hqlBuffer = new StringBuilder();
>> hqlBuffer.append("update EvalAssignUser ");
>> if(sendingAvailableEmails) {
>> hqlBuffer.append("set availableEmailSent = :dateSent ");
>> } else {
>> hqlBuffer.append("set reminderEmailSent = :dateSent ");
>> }
>> hqlBuffer.append("where id in (select eauId from EvalEmailProcessingData
>> where emailTemplateId = :emailTemplateId and userId = :userId)");
>> updateQuery = session.createQuery(hqlBuffer.toString());
>> }
>> 
>> Unfortunately, hibernate does not surface that information that I can see..
>>  I have tried accessing the "hibernate.dialect" property in various ways,
>> and I've looked for anything in the hibernate API's that might identify the
>> dialect.  Haven't found anything.
>> So I'd like to ask this.  The database vendor is readily accessible from
>> sakai's SqlService.  Suppose we added this method to EvalExternalLogic:
>> public String getDatabaseVendor();
>> What would you think about calling an external logic method in the DAO impl?
>>  Can you see another way to do this, other than just hanging MySQL users out
>> to dry?
>> Thanks.
>> Jim
>> 
>> 
>> 
>> 
> 
> 
> 
> -- 
> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
> 
>