Over the last three years I've analysed the performance of at least a dozen completely different solutions. However the performance bottlenecks I find mostly tend to follow similar themes. Having analysed the types of issues that these bottlenecks are I believe I have distilled out three rules, that if you follow them religiously, you are well on your way to having a solution that performs as well as possible in the environment that it is running. So without further ado here they are with a couple of examples of common issues that match each rule:
Thou shalt never do anything more than once. Examples Cache everything you are likely to use more than once. It is a cardinal sin to retrieve the same data from a SQL Server or a Web Service call twice. Nothing is free – Never assume that anything you do doesn’t cost any time or resources. A classic example of this is accessing registry keys - especially tracing flags! When you're getting hardcore about performance tuning even memory allocation falls into this rule. Thou shalt not do anything you don’t need to. Examples Don’t try and reuse an existing stored procedure which returns tons of data when you only want one field that it returns – create a new stored procedure which just gets the data you need - you're unnecessarily burning CPU cycles and disk access on the SQL server and hitting network latency sucking the redundant data back to the calling tier. Use stored procedures. Dynamic SQL generally causes SQL Server to repeatedly do a lot of unnecessary work with compilation and Execution plan generation. Thou shalt get everything in one go. Examples Round trips, especially over a network or from a disk are expensive in latency. Don’t use lots of fine grained web service calls when one chunky call can be used instead. Aim for one Web page = at most one web service call. Don’t use several stored procedures to get a set of data. Create a new one which does everything and returns the complete result set. Aim for one Web Page\Web Service call = at most one SQL Server call.
Examples
Cache everything you are likely to use more than once. It is a cardinal sin to retrieve the same data from a SQL Server or a Web Service call twice.
Nothing is free – Never assume that anything you do doesn’t cost any time or resources. A classic example of this is accessing registry keys - especially tracing flags! When you're getting hardcore about performance tuning even memory allocation falls into this rule.
Don’t try and reuse an existing stored procedure which returns tons of data when you only want one field that it returns – create a new stored procedure which just gets the data you need - you're unnecessarily burning CPU cycles and disk access on the SQL server and hitting network latency sucking the redundant data back to the calling tier.
Use stored procedures. Dynamic SQL generally causes SQL Server to repeatedly do a lot of unnecessary work with compilation and Execution plan generation.
Round trips, especially over a network or from a disk are expensive in latency. Don’t use lots of fine grained web service calls when one chunky call can be used instead. Aim for one Web page = at most one web service call.
Don’t use several stored procedures to get a set of data. Create a new one which does everything and returns the complete result set. Aim for one Web Page\Web Service call = at most one SQL Server call.