"High Performance Database" II

Programming guidelines: (Simply speaking)

The data is logically placed in a piece

Business logic on a piece of

Interface as a interface

Minimum network transport

－－－－－－－－－－－－

1, the data logically put in a piece

For example, table: History

So, stored procedure: spwritehistory represents data logic: it is the write operation to history

Similarly, a storage Spqueryhistoryfromhistoryid represents a query based on Historyid for history records

Again, spqueryhistoryfromhistoryid;2 (; not a normal semicolon, a stored procedure group) represents another similar method of Historyid for history record queries

2. Business logic

For example, return the book, then Spreturnbook

Purchase, then Spbuybook

You may want to invoke many tables, or many data logic at this point:

Such as

Create Proc spBuyBook(
@iBookID int, --书ID
@iOperatorID int --操作员ID
)
-------------------------------------------
--Name: spBuyBook
--Func: 购买一本书
--Var: 见上
--Use: spWriteOperateRecord,spProc2,spProc3....
--User: Chair3,Chair4
--Author: Chair3 # SapphireStudio (www.chair3.com)
--Date : 2003-4-16
--Memo : 临时写写的，给大家作个Sample
-------------------------------------------
AS
Begin
Begin Tran

--数据逻辑：更新Book表，减少数量
Update Book
Set BookNumber=BookNumber-1
Where BookID=@iBookID
...

--业务逻辑：写入操作记录
Exec spWriteOperateRecord '某某人出售了一本书，书的ID为XXX'
...
Commit Tran
End

Instead of trying to do everything with a stored procedure, consider dividing some of the code into smaller stored procedures to achieve code utility and maintainability.

Of course, the requirement for a high performance part is best done alone.

3, the interface of the program do not directly call the data table.

When I'm in seg, I don't allow other colleagues to read the table directly, they can only have permission to invoke the Store Procedure.

This depends on the development environment, of course not absolutely, as far as possible.

In this way, you can reduce the consequences of modifying the data table to all programs.

4. Minimum network transmission

4.1 Reduce the use of select * .... Statement, select the data you only need, select Fd1,fd2 from TB1

4.2 Reduce client direct manipulation of database tables (somewhat the same as 3)

4.2 To avoid the database in the client loop write operation, the loop to the server.

4.3. ... Forgot... by experience.

4 points are most important, many people's program speed is very slow, because of the network transmission too much.

The final summary: Good programmers are more focused on the overall design, maintainability, scalability design.