Hi People!

Just wondered if anyone had worked out how to use NO EXPAND hint for indexed views in SQL 2005 standard issue, and if anyone has come up with any sneaky work arounds to fix LLBLGEN not supporting it? One idea is to use a stored proceedure, but in my case thats a no go.

Thanks! Alex

Hints are a bit of a struggle to implement.

It's not easy because specifying which tables to get which hints is probably a per-query task, and you;re not always in control when which query is executed. So what's left is either that you override in a subclass of DataAccessAdapter the routine CreateSelectDQ, and modify the SQL, or alter the DQE sourcecode: the routine SqlServerSpecificCreator.CreateHintStatement produces the hints, though the design hasn't been done in such a way that support for flexible hint production is possible (no table name is passed in for example, it's a one shot fits all approach).

Thanks for that, I think overriding the CreateSelectDQ would be the way to go. Perhaps having a property like: bool DataAccessAdapter.AddIndexedHint when the Adapter generated the SELECT DQ is checks what it set to and appends the hint accordingly, currently im overriding the update method anyway so that might be an ideal solution. Will give it a try and report back how it goes.

Thanks! A

I think that's too global. WIth hints, it's likely that you want exactly THAT table / view in THAT query called from THAT routine to give THAT hint. That actually asks for a set of table-hint combinations passed to the adapter up front.

Hi Otis,

Yes your correct it is too global, Unfortunately the Select DQ only really gives me access to the Query parameters and to the generated query, so i would have to recreate most of the query engine code to get it to work, I could just copy that out of the provided source code, but does create a maintence over head. Also another catch is with the NO expand is it needs to be added after a specific view name.

Such as

SELECT * FROM TblA, TblB with (NO EXPAND) WHERE TblA.FieldA = TblB.FieldB.

Meaning I would also need to pass in the view name (s) as well then have it emit the option for that field when it generates the SQL. So your correct simply overriding the Select DQ and added the hint at the end isn't going to work.

Ideally what I would want is to be able to pass a list of views to the Engine and and tell it those views are indexed and that you would like it to emit the expand hint when ever their used, it would be great to be able to add that option in the designer and emit it during code generation.

Yes, it's not that easy... Take into account that there might be an alias and replacing parts of the SQL is undoable.

The SQL DQE has little maintenance overhead though, hardly a bug is found in there, so it can be an option for you (to run with a custom DQE).

Another element of focus is RelationCollection.ToQueryText. As I said earlier, the design for the hint support that IS there (NOLOCK) isn't really great , as it doesn't pass the target to the hint provider. So IF that would be done, the hint provider routine can be much smarter and in THERE you then can specify the hint to emit, based on the table name passed in. (Check RelationCollection.cs, line 356 for example, which calls into SqlServerSpecificCreator.CreateHintStatement, line 520)

I'll add a request for a code change for v2.6, so we at least update the design in that area so customization on a detailed level doesn't have to take a lot of effort in a lot of areas.