Index Hints and SQL Optimizer

Hi Guys, Greetings! Recently, I have been troubleshooting some performance problem on AR module-SalesTable form. The developers our here have customized a lot and i have seen in many places where they use select statements, they have explicitly put index hints… We are using Axapta 3.0 with SQL Server 2000 as backend and I believe SQL 2000 Query optimizer is smart enough to determine when and where to use index hints by itself and from front end rarely it should be used…How far I’m correct?? Any suggestios welcome… Thanks and Regards, dilip

It should be in most cases. I think delopers guide also sais this:


If data ordering is important, use ORDER BY in your SELECT statements. If you can predict that the database will not be capable of deciding which index it should choose, use the INDEX keyword to give the database a hint of which index to choose. Combine this with an ORDER BY expression to get MorphX to select records in a specific order.


How to apply an index You do not have to worry about how and when to use an index. If you have made an index for a table and it can be used in a given context, then MorphX automatically uses it when the table is accessed.


Forcing a specific index in a SELECT statement Indexes are used automatically when you access the data in your tables from X++ code. The application programmer can use the INDEX keyword to give MorphX a hint of which index to apply when records are selected.

This works fine, but every option has it benefits. Having explicitly selected index saves time used for query optimization. But index may not be the best option in all situations, so sometimes query optimizer chooses better execution path. I hope Axapta will use stored procedures for MS SQL Server in next release (compared to cursor operations which are currently used), because execution plans are saved, so this eliminates the need for Query Optimizer to evaluate path every time stored procedure is called.

Hi Bojan, Thanks for your inputs. Actually, I think in some cases I will have to do a test by specifying with and without index hints in the select and then see the execution how many ms it will get the results? As per the cursor goes, i agree with you axapta uses server-side cursors and this bogs down the performance considerably when say e.g. 100+users are concurrently accessing. I found this when i run SQL profiler and can see the trace and the number of times database is hit. i think in future releases, microsoft should come out with the stored procedure approach which will give a boost to performance. Thanks for your time. Regards, Dilip