Database Tuning
While working with the database we should keep in mind
few facts and concepts to make database operations fast.
Following list may be helpful to increase database
performance and reduce the server overhead
• Use 3BNF database design.
• Avoid number-to-character conversions because numbers
and characters compare differently and lead to performance downgrade.
• Don’t use * in your SELECT queries because it would
load the system unnecessarily. Select only necessary fields.
• Create your indexes carefully on all the tables where
you have frequent search operations
• Avoid index on the tables where you have less number of
search operations and more number of insert and update operations.
• Avoid a full-table scan by creating an index on columns
that are used as conditions in the WHERE clause of an SQL statement.
• Be very careful of equality operators with real numbers
and date/time values.
• Use pattern matching carefully. LIKE INDI% is a valid
WHERE condition, reducing the returned set to only those records with data
starting with the string COL. However, INDI%A does not further reduce the
returned results set since %A cannot be effectively evaluated.
• Fine-tune your SQL queries examining the structure of
the queries, the SQL syntax, to discover whether you have planned your tables
to support fast data operation and written the query in an optimal manner,
allowing your DATABASE to operate the data efficiently.
• Use stored procedures in SQL rather than queries.
• Avoid using the logical operator OR in a query if
possible.
• You can enhance bulk data loads by dropping indexes
• When performing batch transactions, perform COMMIT at
after a fair number of records creation instead of creating them after every
record creation.
• Plan to defragment the database on a regular basis,
even if doing so means developing a weekly routine.
Built-In Tuning Tools
• Explain plan − tool classifies the access path that
will be taken when the SQL statement is executed.
• tkprof − measures the performance by time elapsed
during each phase of SQL statement processing.
