Database performance relies on several layers ranging from operation system, hardware, network, devices, database server, database design, to client side SQL query design in an application. The bottleneck could lay on any layer. However , once the system environments have been established and a database has been properly designed, the performance of the database mainly depends on properly created indexes and SQL query design. Accordiing to Sybase , 80% of Sybase database performance problems can be solved by properly created index and carefully design of SQL queries.

In the process of performance analysis , keep two things in mind.

Firstly, there are limits to the maximum performance . i.e. for a given query or sql statement, it has a minimum response time or a maximum throughput. Therefore, your goal is to tune your query or sql statement to achieve or close to the minimum response time or the maximum throughput.

Secondly, when using an index to speed up queries; remember , index speed up those data retrieving queries but it will slowdown those data updating statements, and it uses more database space. Therefore, for a dynamic database, users have to trade off the use of index based on the overall performance of a database.

With respect to client side performance tuning I generally follow these steps which can be summarized as the following;

Drop index before loading data. Consider dropping the indexes on a table before loading a large batch of data. This makes the insert statement run faster. Once the inserts are completed, you can recreate the index again.

If you are inserting thousands or rows in an online system , use a temporary table to load data. Ensure that this temporary table does not have any index. Since moving data from one table to another is much faster than loading from an external source, you can now drop indexes on your primary table, move data from temporary to final table, and finally recreate the indexes.

Select limited data. The less data retrieved, the faster the query will run. Rather than filtering on the client , push as much filtering as possible on the server-end. This will result in less data being sent on the wire and you will see results much faster.