cf.Objective() Daryl Banttari: Database Performance Tuning

In many CF apps databases are choke points.

Without indexes, SQL Server will do a full table scan (finishing even after it finds your record).

Clustered Index actually becomes the table. So, on one-to-many tables, you would create a Clustered Index on the foreign key in the "many" table.

Summary Of A Query Round-Trip: SQL goes from CF to JDBC over the network to the Query Optimizer and then to the Execution Engine. Finally, the Execution Engine sends the result back to the driver which hands it to CF.

If indexes are not very selective they will actually be skipped. When he says "very selective", he means like eliminating 95% of the other records.

If you run a SQL statement in Query Analyzer, you can check the execution plan. If you see Parallelism, this is bad. Look at the "Estimated subtree cost". It should be .01 to .1.

At this point, you can add indexes based on what path you usually take to drill into that table. This part obviously takes some thought. If the results of a query are most frequently ordered by a second column, consider adding it to the index.

Note: SQL server will generally only use one index per lookup.

Now on to the report type queries. You can use "covering indexes" to cover special cases. Daryl's recommendation is to name the indexes for what they are covering (ie. IX_CoverMonthlyExpenseReport).

In Query Analyzer all the icons show you what types of lookups the database will make. I'll try to find a page to link to with what they mean...

Tips:

• Tune first.
• But after that there are ways to tell if you need to actually physically tune your server. Then, you can do things like splitting the table data, log data, and indexes all off to separate physical storage.
• Use Union if you have global "or"s on different columns.

This post feels a little rough to me. But, I want to get them out ASAP. I reserve the right to come back and spiff it.