Hi Rusty,
Indexes are generally fine to add. Don't add clustered or unique indexes as they may interfere with SL. Standard tables usually have a clustered index already. Standard SL indexes that are used by the kernel and screens usually end in a number. Custom indexes that you add can be named whatever you like.
Indexed (materialized) views are not supported in SL. If you add one to a table it will cause a fatal exception in SL when the table is updated. Also, anything that modifies the structure of standard SL tables (number of fields, field types/lengths, computed columns, etc.) is disallowed. The table buffers compiled into the application must match the schema exactly.
Below are some non SL-specific suggestions applicable to SQL Server indexing/performance from my experience. These might help you or benefit others:
1. Try to get the columns that you are filtering on in the predicate (WHERE clause) into your index, and also add columns from the SELECT list as 'included' columns if the list isn't too long. This can allow the database to satisfy the query directly from the index itself.
2. Avoid doing computations on or using user-defined functions on columns in your predicate, as that can prevent the database from using indexes and you can end up with a table scan instead.
3. Avoid cursors/loops, especially in reports. There very few cases where cursors are appropriate at a reporting level. Usually cursors on a report will destroy performance.
4. Avoid temp tables - these will hit the tempdb and cause a lot of disk I/O.
5. Make sure that you have a maintenance plan to rebuild the indexes periodically so that they don't get too fragmented.
6. Avoid nesting too many sql objects in your query, such as views. In my experience it's best not to have more than 2 levels deep from you query to the underlying table.
7. Try to narrow your report selection criteria as far as possible with parameters, for example, using PerPost on transaction tables at a minimum.
Hope this helps.
Paul Phillips
Principal Development Consultant
www.Abalu.net
Share
Report
55
All responses (
Answers (
