[OPENJPA-2549] Slow query with full index scan when using OpenJPA query with string parameter

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: brian yoder
• Created: 01/Dec/14 21:46
• Updated: 17/Apr/15 06:29

Description

I am not sure if this is an OpenJPA issue or not but here goes:

This happens with MS SQL JDBC driver type 4

– This takes 7 seconds or more - full index scan
Query q = em.createNativeQuery("select top 100 email_msg_id from im_email_msg where email_id like ? and firm_id = ?");

– This takes 10 milliseconds - index seek
c.prepareCall("select top 100 email_msg_id from im_email_msg where email_id like ? and firm_id = ?");

email ID parameter is a string, firm ID is integer. I have a work arround on this issue by setting the “sendStringParametersAsUnicode=false” JDBC parameter, but I think the issue may actually be OpenJPA.

The reason I think this is an OpenJPA issue is that when I do a native call using the JDBC Connection API it works fine. As soon as OpenJPA comes into the picture it seems to pass ntext() conversion down to the driver, which is causing full index scan, since MS SQL Server is actually converting all values to UNICODE. The data in my column is text, not ntext, therefore no ntext should be used. But for some reason I think OpenJPA is calling the incorrect setString function, or somehow using unicode as the string paramer.

This link may be helpful:
http://stackoverflow.com/questions/8948872/jpa-hibernate-native-query-for-prepared-statement-slow