Would you please give me some notes for DB design?
System Structure:
This requirement is configured almost same as others. You need to consider:
(1) Using 3-tier or Server/Client to reduce the loading of Database Server,
(2) Using Stored Procedure to reduce the unnecessary round-trips.
DB Server Configuration:
(1) Try to allocate 1/2 of physical memory size to DCCA for better performance.
(2) Appropriately splitting up the tablespace files to different Disks.
(3) Leave the independent table with big size into a sole tablespace. Prevent the small tables intermingling with the big table.
Schema Design:
(1) Set up the Lock Mode to Row or Page? By default, we will use Page Lock,but in some OLTP circumstance, Row Lock might be a better choice. But it will depend the table schema, (its index, and size and SQL commands).
(2) Use transaction by any chances. DBMaker uses auto-commit as its default. Users should know that it will have worse performance under this configuration.
(3) Make a 70% or 80% fillfactor in table and index. Or row-migration or index split occurrence will degrade your performance.
(4) Create an appropriate index as needed. By study, 40% or above indexes will never be used in DBMS. Make sure that you choose the right index to use in your system.
AP Design:
(1) Using Hint to choose the best execution plan if necessary.
(2) Use DB_DTCLT and DB_ITIMO to kill the possible dead connection. (By Default, this feature is turn on.)
It's not easy to use a simple word to express the good design in DBMaker. All of above are all big topics. User could refer to our manual with respect to the Performance Tuning and likewise.