Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » NOEXPAND hints and bind-parameters
NOEXPAND hints and bind-parameters [message #781041] Thu, 19 January 2012 18:47 Go to next message
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?

Report message to a moderator

Re: NOEXPAND hints and bind-parameters [message #781131 is a reply to message #781041] Fri, 20 January 2012 16:32 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1300
Registered: July 2009
Senior Member
I am not sure I have ever seen EclipseLink automatically add a hint other than when it is using rownum pagination on the Oracle platform. You can verify that EclipseLink isn't adding it to the SQL it issues by turning logging on using <property name="eclipselink.logging.level" value="FINEST"/>. If you are issuing native queries (named or otherwise) EclipseLink will not modify the sql in anyway, so I don't see how the NOEXPAND hint gets added in one case but not another - you may need to check the driver and database settings. This is more likely since EclipseLink cannot tell that you are using a view vs a database table, and so wouldn't know to use the hint, but the database might.

Best Regards,
Chris

Report message to a moderator

Re: NOEXPAND hints and bind-parameters [message #781135 is a reply to message #781041] Fri, 20 January 2012 16:44 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1300
Registered: July 2009
Senior Member
Using google I http://dba.stackexchange.com/questions/3044/noexpand-hint-for-non-enterpise-edition-and-performance
" In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used." It could be you are using SQL Server Enterprise which decided to use an indexed view for some of your queries, making it appear to have the NOEXPAND hint so that it can use the indexed view.

Best of luck,
Chris

Report message to a moderator

Re: NOEXPAND hints and bind-parameters [message #781151 is a reply to message #781135] Fri, 20 January 2012 18:03 Go to previous message
Rich MacDonald is currently offline Rich MacDonaldFriend
Messages: 18
Registered: December 2010
Junior Member
Chris, great suggestion. I had just seen that article myself and was arriving at the same idea at the same time Smile I'd satisfied myself that eclipselink could not be adding the hint. There was a outside chance that the jdbc driver was, but that would be too weird. So I finally removed all my indexed views in sqlserver, and yes, the NOEXPAND hint went away.

Annoying that that sqlserver profiler and database tuner were reporting NOEXPAND in the queries. Dumb actually, because that should be "downstream" of where they are analyzing.

Oh well, at least I have made lateral progress. I used indexed views because I have expensive queries using Rank() and Max(), which are two procedures that sqlserver does not support inside indexed views. So I had created an indexed view for the "raw data" (the "inner portion" of the query), with appropriate indexes, then was using a normal view based on those indexes for the final/outer filtering. Unfortunately, with that configuration, the NOEXPAND causes braindead "full table scan" behavior.

Back to the drawing board ... but at least with a better idea. Thread closed and thanks for your help.

Report message to a moderator

Re: NOEXPAND hints and bind-parameters [message #781183 is a reply to message #781135] Fri, 20 January 2012 18:03 Go to previous message
Rich MacDonald is currently offline Rich MacDonaldFriend
Messages: 18
Registered: December 2010
Junior Member
Chris, great suggestion. I had just seen that article myself and was arriving at the same idea at the same time :) I'd satisfied myself that eclipselink could not be adding the hint. There was a outside chance that the jdbc driver was, but that would be too weird. So I finally removed all my indexed views in sqlserver, and yes, the NOEXPAND hint went away.

Annoying that that sqlserver profiler and database tuner were reporting NOEXPAND in the queries. Dumb actually, because that should be "downstream" of where they are analyzing.

Oh well, at least I have made lateral progress. I used indexed views because I have expensive queries using Rank() and Max(), which are two procedures that sqlserver does not support inside indexed views. So I had created an indexed view for the "raw data" (the "inner portion" of the query), with appropriate indexes, then was using a normal view based on those indexes for the final/outer filtering. Unfortunately, with that configuration, the NOEXPAND causes braindead "full table scan" behavior.

Back to the drawing board ... but at least with a better idea. Thread closed and thanks for your help.

Report message to a moderator

Previous Topic:NOEXPAND hints and bind-parameters
Next Topic:Error generating static Data Objects from XSD
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 22 19:06:02 GMT 2018

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

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

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly