Frans,

We are about to buy SQL Server Licenses (per processor) for our application and having decided that 4x Standard Edition processor licenses are better than 1x Enterprise Ed processor license we have come up against a problem regarding our use of Indexed Views.

The SQL Server Standard Ed query optimiser doesn't consider Indexed Views by default (unlike the Enterprise Edition). This means that you have to manually specify a NOEXPAND hint for SELECT statements . See Using Indexed Views On Non Enterprise Editions of SQL Server for details...

SELECT Ark_Item.ItemID, ItemName 
FROM Ark_ReadableFolderItem 
INNER JOIN Ark_Item ON Ark_Item.ItemID = Ark_ReadableFolderItem.ItemID 
WHERE FolderID = 107 AND (GroupID = 74 OR GroupID = 78 )

SELECT Ark_Item.ItemID, ItemName 
FROM Ark_ReadableFolderItem WITH (NOEXPAND) 
INNER JOIN Ark_Item ON Ark_Item.ItemID = Ark_ReadableFolderItem.ItemID 
WHERE FolderID = 107 AND (GroupID = 74 OR GroupID = 78 )

The first query does not consider using the Indexed View "ReadableFolderItem" while the second one does... Obviously there is a BIG diference in the performance...

A quick search of the docs leads me to believe that the NOEXPAND hint is not currently supported.

I know there is a way to specify a NoLock hint using

SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;

and was wondering how big a deal would it be to add NOEXPAND to the DynamicQueryEngine?

[edit]

mmm Having looked at the code, the NoLock hint seems to be global which won't do... There is however a RdbmsHint enum which currently contains on 1 hint type... and is emitted around line 721 of the DynamicQueryEngine

                queryText.Append(base.Creator.CreateObjectName(fieldsPersistenceInfo[0]));
                if(selectList[0].ObjectAlias!=string.Empty)
                {
                    queryText.AppendFormat(" AS [{0}]", selectList[0].ObjectAlias);
                }
                queryText.AppendFormat(" {0}", ((IDbSpecificHintCreator)base.Creator).CreateHintStatement(RdbmsHint.TableInFromClauseHint, null));

Would it be as simple as expanding upon this?

Marcus

Problematic. The thing is (as I understand it) that the hint has to be given per view. So not per call, or globally, but per view. If a view isn't indexed you get an error when specifying the hint (as I understand it).

Which thus comes down to some sort of hint specification in the persistent info, and that room isn't there yet (as info is stored per field, not per target).

So, I dont see an 'easy' way to hack it in. Well... unless you alter the DQE of course, then it is possible. (together with the RelationCollection). A list of views which have to receive the hint has to be stored somewhere and at runtime whenever a target name is produced in a from clause (which is done at two places in selects: in the select dq routine if no relations are specified and in the RelationCollection if there are relations specified), and the name is on the list, the hint has to be applied. But it's dirty and complicated.

Otis wrote:

Problematic. The thing is (as I understand it) that the hint has to be given per view. So not per call, or globally, but per view. If a view isn't indexed you get an error when specifying the hint (as I understand it).

Which thus comes down to some sort of hint specification in the persistent info, and that room isn't there yet (as info is stored per field, not per target).

So, I dont see an 'easy' way to hack it in. Well... unless you alter the DQE of course, then it is possible. (together with the RelationCollection). A list of views which have to receive the hint has to be stored somewhere and at runtime whenever a target name is produced in a from clause (which is done at two places in selects: in the select dq routine if no relations are specified and in the RelationCollection if there are relations specified), and the name is on the list, the hint has to be applied. But it's dirty and complicated.

Okay, no problem... I think the best course of action for me then is to use a Stored Procedure for now.

Something that did occur to me however and this is something that might be a feature of your product... The Std Ed. requires you to change the code in order to use Indexed Views properly and this is something that the designer could determine for you and add the hint as required... This might be a cool feature for those wanting to remove this step from their development process... Obivously removing the index (in production) then breaks the code... but this would just need to made clear or maybe have the hint optional in the designer...