Code Project

Link Unit

Wednesday, September 03, 2014

Performance Improvement

 
Update Statistics: 

Let's look at following query.

SELECT *
FROM customer
WHERE city = 'Pune'
 AND phone = '2020666666' -- dummy Number 
 
Above query contain two fields in the "WHERE" clause and suppose there are two indexes defined, each containing one field. One very important notion to remember is that the optimizer can only use ONE index per table.

Therefore, it has to make a decision as to which index to use. Ideally, optimizer should pick index created on phone but in absence of updated statistics it can pick cityIndex.

This decision can be informed one if statistics are up to date.


Avoid foreign key constraints

Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.

No comments: