Experience on database design
Whether the design of database model is reasonable can greatly affect the performance of the system. Based on the experience of designing and using the database for many years, the author puts forward some design principles for the reference of colleagues.
Caution with cursors (cursor)
Cursors provide a means of progressive scanning in a particular set, typically using cursors to iterate through the data on a row-by-line basis, with different operations depending on the criteria for extracting the data. And for the multi-table and large table defined in the cursor (large data collection) loop is easy to make the program into a long wait or even panic, the author in a city "Housing Provident Fund Management System" for the day end account rolling plot no interest processing, Processing a 100,000-account cursor causes the program to enter an indefinite wait (48 hours after measurement) (Hardware environment: alpha/4000 128MB RAM, SCO Unix, Sybase 11.0). After modifying the program and using the UPDATE statement, the process can be completed within 20 minutes. Examples are as follows:
Declare mycursor Cursor FOR select Count-no from Count
Open MyCursor
Fetch mycursor into @vcount-no
while (@ @sqlstatus =0)
Begin
If @vcount-no=′′ Condition 1
Action 1
If @vcount-no=′′ Condition 2
Action 2
...
Fetch mycursor into @vcount-no
End
...
Switch
Update COUNT set operation 1 for condition 1
Update COUNT set operation 2 for condition 2
...
In some cases where cursors must be used, consider moving the qualifying rows of data into a temporary table and then manipulating the temporary table definition cursors, which can significantly improve performance. In the background programming of "Telecom toll system" in a certain city, the author makes a cursor operation on a table (30 rows of data in 30,000 rows) (Hardware environment: PC server, pⅱ266 64MB RAM, Windows NT4.0 MS SQL Server 6.5).
Examples are as follows:
Create #tmp/* Define TEMP Table */
(Field 1
Field 2
... )
Insert into #tmp select * from total where condition
Declare mycursor Cursor FOR SELECT * FROM #tmp/* define cursor on temporal table */
...
How to use Indexing (index)
Creating an index typically has two purposes: maintaining the uniqueness of the indexed columns and providing a strategy for quickly accessing the data in the table. Large databases have two indexes, cluster index and non-clustered index, a table without a clustered index is stored by the heap structure, all the data is added to the end of the table, and the cluster index table, whose data are physically stored in the order of the cluster index key, a table only allows one cluster index, so according to the B-tree structure, It can be understood that adding any index can increase the speed of query-by-index columns, but at the same time reduces the performance of INSERT, update, and delete operations, especially when the fill factor (Factor) is large. So when you make frequent insertions, updates, and deletions to tables with more indexes, you should set up a smaller fill factor to create tables and indexes so that you can leave more free space in each data page, reducing page splits and re-organization.
http://www.bkjia.com/PHPjc/631139.html www.bkjia.com true http://www.bkjia.com/PHPjc/631139.html techarticle Database design experience on whether the design of database model is reasonable will greatly affect the performance of the system. Based on the experience of designing and using the database for many years, the author puts forward some design original ...
