Experience on designing database

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 database for many years, the author puts forward some design principles for our colleagues ' reference.
Careful use of cursors (Cursor)
Cursors provide a step-by-step scan of a particular set, typically using cursors to traverse data line by row, and to perform different operations depending on how the data is removed. And for multiple tables and large tables defined in the cursor (large data set) loop is very easy to make the program into a long wait or even panic, the author in a city "Housing Provident Fund Management System" in the daily End account rolling charge number interest treatment, Processing of a 100,000-account cursor causes the program to enter an indefinite wait (48 hours after calculation) (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
Operation 1
If @vcount-no=′′ Condition 2
Operation 2
...
Fetch mycursor into @vcount-no
End
...
To
Update COUNT set operation 1 for condition 1
Update COUNT set operation 2 for condition 2
...
In some cases where cursors must be used, the performance can be significantly improved by considering qualifying rows of data into a temporary table, and then by defining cursors for the temporary table. In the background program design of the "Telecommunication toll system" database in a certain city, the author makes a cursor operation on a table (30 multiple 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 temporary table * *
(Field 1
Field 2
... )
Insert into #tmp select * from total where condition
Declare mycursor Cursor FOR SELECT * FROM #tmp/* To define a cursor on a temporary table * *
...
Tips for using indexes (index)
Creating an index typically has two purposes: maintaining the uniqueness of indexed columns and providing fast access to the data in the table. A large database has two indexes, a clustered index and a nonclustered index, and a table without a clustered index stores the data by the heap structure. All of the data is added to the end of the table, and the table with the clustered index, whose data is physically stored in the order of the cluster key, a table that allows only one clustered index, and therefore, according to the B-tree structure, It is understandable that adding any index can improve the speed of querying by indexed columns, but at the same time reduces the performance of INSERT, update, and delete operations, especially if the fill factor (fill Factor) is larger. Therefore, when the index is frequently inserted, updated and deleted, the table and index should be set up with a small filling factor to leave more free space in the data pages, and reduce the page segmentation and the work of RE organization.