|
|
|
Re: NOEXPAND hints and bind-parameters [message #781151 is a reply to message #781135] |
Fri, 20 January 2012 18:03 |
Rich MacDonald 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.
|
|
|
Re: NOEXPAND hints and bind-parameters [message #781183 is a reply to message #781135] |
Fri, 20 January 2012 18:03 |
Rich MacDonald 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.
|
|
|
Powered by
FUDForum. Page generated in 0.03596 seconds