brian yoder created OPENJPA-2549:
------------------------------------
Summary: Slow query with full index scan when using OpenJPA query
with string parameter
Key: OPENJPA-2549
URL: https://issues.apache.org/jira/browse/OPENJPA-2549
Project: OpenJPA
Issue Type: Bug
Reporter: brian yoder
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
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
