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 the following
Accounting principles for the reference of colleagues.

Careful use of cursors (Cursor)

Cursors provide a step-by-step scan of a particular set, typically using cursors to traverse the data line by row, depending on the condition of the data being fetched.
For And for multiple tables and large tables defined in the cursor (large data set) loop is easy to make the program into a long wait or even panic, the author in a city "live
Housing Provident Fund Management System "to carry out the day end account rolling charge number, a 100,000-account cursor processing causes the program to enter an indefinite wait
(It takes 48 hours to complete) (Hardware environment: alpha/4000 128MB RAM, SCO Unix, Sybase 11.0). Modify the program and use update instead
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 situations where cursors must be used, consider moving the qualifying data rows into a temporary table, and then defining the cursor for the temporary table to operate so that the
Performance has been significantly improved. The author is in a certain city "telecommunication toll system" Database backstage program design, to a table (30,000 rows of 30 rows of data that meet the conditions)
For cursor operations (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. There are two kinds of indexes in large database, that is, the cluster cable
Both the citation and the nonclustered index, a table without a clustered index stores the data by the heap structure, all the data is added to the end of the table, and the table with the clustered index, whose data
Are physically stored according to the order of the cluster keys, and a table allows only one clustered index, so according to the B-tree structure, it can be understood that adding any index can
Increases the speed of queries by index columns, but at the same time reduces the performance of INSERT, update, and delete operations, especially if the fill factor (fill Factor) is larger.
So when you do frequent inserts, updates, and deletions on more indexed tables, you should set a smaller fill factor when you build tables and indexes so that you can leave them in the data pages
More free space to reduce page segmentation and organizational work.

Consistency and integrity of data

To ensure consistency and completeness of the database, designers tend to design too many table associations (relation) to reduce data redundancy as much as possible. Table
An association is a mandatory measure that, when established, inserts, updates, and deletes to the parent table (parent table) and child tables (children table) to occupy the system
, and it is also best not to associate the child table with the Identify property field as the primary key. If data redundancy is low, the integrity of the data is easily guaranteed, but the increase
The operation of the join query between tables is added. In order to improve the response time of the system, reasonable data redundancy is also necessary. Use rules (rule) and Constraints (Check) to
Preventing system operators from mistakenly entering errors that cause data is another common tool for designers, but unnecessary rules and constraints can also occupy the system's
The necessary overhead, it should be noted that the constraint on the data validation is faster than the rule. All of these, designers should be in the design phase according to the system operation of the class
Type and frequency are considered in equilibrium.

The trap of the business

A transaction is a set of actions that is completed at one one-time. Although these operations are a single operation, SQL Server can ensure that the set of operations is either complete or
Do not do a bit. It is this characteristic of large database that makes the integrality of data be guaranteed greatly.

As we all know, SQL Server provides implicit transaction control for each individual SQL statement, allowing the data operations of each DML to be fully committed or returned
rolling, but SQL Server also provides explicit transaction control statements, such as:

Begin TRANSACTION Start a transaction

Commit TRANSACTION Commit a transaction

ROLLBACK TRANSACTION rollback a transaction

Transactions can be nested and can be retrieved through the global variable @ @trancount to a connected transaction nesting level. To be particularly careful, each display or implied
Things start with the variable plus 1, the commit of each transaction causes the variable to be reduced by 1, and the rollback of each transaction causes the variable to be 0, and only if the variable is 0 o'clock
A transaction commits (when the last commit statement) to write the physical data to disk.

Selection of data types

The reasonable choice of data type has a great influence on the performance and operation of the database, and there are many books on this aspect, the author mainly introduces
Some experience in Shaoxing:

1. The Identify field should not be associated with other tables as the primary key of the table, which will affect the data migration of the table.

2. The Text and image fields are pointer-type data and are used primarily for storing binary large objects (BLOBs). This type of data is manipulated in comparison to other data types
Slow, so avoid using.

3. The advantage of date-type fields is that there are numerous date function supports, so it is very simple to compare and subtract the date. However, in accordance with the date
The query operation as a condition also uses a function, which is much slower than other data types because the server cannot use a function as the condition of the query with the advanced
Performance policies to optimize queries and only table scans to traverse each row.