Designing tables efficiently
Be careful using triggers.
when a trigger is fired (or “triggered”) by a given SQL statement, then all the work that trigger does becomes a part of the SQL statement. This extra work can slow down the SQL statement, and sometimes that can cause a big hit on performance if the trigger fires a lot.
Use the same data type for both primary and foreign key columns.
This is because table joins when the data types are different, then the DBMS will have to convert one of the data types to the same type as the other one.
When choosing a data type for a numeric column, you should always pick the smallest data type that can hold your data.
Use CHAR instead of VARCHAR when you know that the entries will have the same length.
VARCHAR columns actually have an extra 1 to 3 bytes per entry so that they can also hold the length of the actual data. In addition to that extra 1 to 3 bytes, whenever a VARCHAR column has some change in the column data, there is some more processing overhead because the length of the column data has to be calculated yet again.
Reducing disk writes and reads
Use more than just one physical disk drive and spread out the database files on the different disk drive, so we can have many I/O operations happening in parallel.
make sure that we allocate buffers which have the right size.
Creating indexes help greatly in tuning database performance
Read full article from How to Tune Database Performance
Be careful using triggers.
when a trigger is fired (or “triggered”) by a given SQL statement, then all the work that trigger does becomes a part of the SQL statement. This extra work can slow down the SQL statement, and sometimes that can cause a big hit on performance if the trigger fires a lot.
Use the same data type for both primary and foreign key columns.
This is because table joins when the data types are different, then the DBMS will have to convert one of the data types to the same type as the other one.
When choosing a data type for a numeric column, you should always pick the smallest data type that can hold your data.
Use CHAR instead of VARCHAR when you know that the entries will have the same length.
VARCHAR columns actually have an extra 1 to 3 bytes per entry so that they can also hold the length of the actual data. In addition to that extra 1 to 3 bytes, whenever a VARCHAR column has some change in the column data, there is some more processing overhead because the length of the column data has to be calculated yet again.
Reducing disk writes and reads
Use more than just one physical disk drive and spread out the database files on the different disk drive, so we can have many I/O operations happening in parallel.
make sure that we allocate buffers which have the right size.
Creating indexes help greatly in tuning database performance
Read full article from How to Tune Database Performance
No comments:
Post a Comment