Can I add 'with (NoExpand)' after the select and before the where?

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?

Marc

If you mean prop:sql then the answer is yes. you can use any correct syntax in sql

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?

Marc Brierley
Trimble Forestry
+64212855568

Not sure. BUT you can create a View (on sql), then create Clarion table that uses that view and use it in your browse…

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.

Marc Brierley
Trimble Forestry
+64212855568

Not sure I ever understand what you need. Optimize your view then.

I need to add something on the end of the select statement that the browse creates.

If the no expand can go at the end of the statement I think you can use SQL() in the filter to do that.
I use that to add things like ‘a not in ( select…’

Thanks Sean. The ‘with (NoExpand)’ needs to go before the where statement unfortunately so before the filter is applied.

Marc, does your view on the Clarion side have more than one table in it? You can probably hack prop:JoinExpression on the last table to do what you want.

Not familiar (yet) with NoExpand, but can you either change the SQL view or create a new SQL view with NoExpand? On the Clarion side, it then simply becomes another table.

I’m Making a few assumptions here

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 :slight_smile:

Hi Rick
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.

Is there more than one table in your Clarion View in your browse procedure? Can you post it here?
If there is only one table, is there another table you can add that doesn’t impact the results/rows?

My idea is to do this:

MyView View(MySQLView)
  JOIN(TableTwoKey,MySQLView:LinkingColumn)
  END
  END

Then after your view is opened.

MyView{Prop:JoinExpression,1} = 'My:FieldTable1 = MyFieldTable2 ' & 'SQL(WITH(NOEXPAND))'

Might take some tweaking to get it to work.

Marc,

have you tried MyFile{Prop:Hint} = ‘with (NoExpand)’?

Mike, that’s a good idea. I’d be very curious to know if that works.

Would that need to go anywhere in particular?

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.

2 Likes

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.

I have often selected this solution rather than struggling to get Clarion to generate more nuanced SQL
I believe it has proven to be much more flexible too when things change.