Can I add ‘with (NoExpand)’ after the select and before the where?
e.g. "select * from ThisView with (NoExpand) where … ".
My DBA has an idea to use an indexed view but it only makes a
difference if 'with (NoiExpand) is included.
Any thoughts from the more experienced programmers out there?
The problem is that it’s a browse so the select is created by the application. I use the Prop:sqlfilter for a where statement but I don’t know about adding onto the select statement. Where and how would I put the extra?
Already there. The dictionary table is looking at a view. A DBA I work with has created the view and I need to add ’ with (NoExpand)’ to the query if I can. I captured the query in SQL Profiler and mucked around with in SSMS and if I can make it work I’ll get a huge boost in speed.
This is likely a materialized view on the SQL backend. Adding NoExpand hint tells the SQL Engine to not “look” at the tables that make up the View when creating it’s execution plan. To instead use the view definition to create the plan. This can create a performance boost (or just the opposite) depending on the tables used and the data distribution in those tables. For example, if the underlying tables have calculated columns that use scalar functions to calculate the value, then any query that hits that table and views using that table will go single threaded. This is true even if the query/view doesn’t use the calculated columns. Using NoExpand in this case on a materialized view will stop SQL from looking at the table definitions when creating its plan. That allows the statement to go parallel and use multiple cores.
Just one example
Thanks for your response.
That is a similar description to the one that my DBA gave me.
So the question is: How can I add ‘with (NoExpand)’ to the query.
It’s a browse box so it creates the select statement for me and I add the filter which it uses as the where clause.
Doesn’t look like it really matters. You can put in the dictionary. There is a Hint field in the Drivers string builder dialog. It looks like it will include it with every command. Hopefully, that works out OK.
Make the hint just NOEXPAND. The driver does the WITH( ) part for you.
Thanks for the suggestion Rick. Unfortunately it doesn’t seem to make a difference. We’ve created a work-around however by wrapping the view in another view which contains the with NoExpand. This gets us a significant speed increase so we’ll go with that.