Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » NOEXPAND hints and bind-parameters(performance)
NOEXPAND hints and bind-parameters [message #780874] Thu, 19 January 2012 18:47
Rich MacDonald is currently offline Rich MacDonaldFriend
Messages: 18
Registered: December 2010
Junior Member
Fascinating problem I am running into with eclipselink (2.2.1) and SQLServer. I am dealing with a "bill-of-materials" (BOM) query problem, so I have written a database view to do that query, then mapped the view to a JPA class. Then I use a named JPA query to access the query.

I noticed performance was heading downhill, so I ran the SQLServer profiler and tuning advisor. Lo and behold, the analysis stated that the BOM query could not be optimized because it included the NOEXPAND hint. So every query was running a full scan of the view then performing the filter afterwards. Queries run with the sqlserver management studio that take 1 second are now taking 1 minute when run with eclipselink/jdbc.

The NOEXPAND hint is either present or missing (no way to turn it off) so I have to find who is adding it, then stop it.

I can find no evidence in the trace log that the NOEXPAND hint is present. So it could be some problem with the trace/analysis tool. Unlikely but possible.

I walked the eclipselink code in the debugger as far as I could and did not see anything setting a query hint.

I tried some other alternatives. I rewrote the named JPA query as a named native query. Same problem.

I tried the criteria api. Same problem.

I bypassed the named queries by generating the sql on the fly and creating a native query that way. This worked! SQLserver no longer "sees" a NOEXPAND hint and optimizes the query properly.

When I set eclipselink.jdbc.bind-parameters to false, it works! Bingo.

What gives?
Previous Topic:Error when trying to use PLSQLTable type as an IN parameter in a PLSQLStoredProcedure in eclipse lin
Next Topic:NOEXPAND hints and bind-parameters
Goto Forum:
  


Current Time: Thu Nov 27 21:18:36 GMT 2014

Powered by FUDForum. Page generated in 0.01535 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software