[Repost] <High Performance Database> Second Testation Database Programming

zhaozj2021-02-16  175

Second lecture database programming

Programming Guidelines: (Simply talking)

1, the data logic is placed on a 2, the business logic is placed on a 3, only interface 4, the smallest network transmission

----------------

1, the data logic is placed on one

For example, Table: History

Then, the stored procedure: SpWriteHistory indicates that the data logic is the same, and the SPQueryHistoryFromhistoryID represents the query according to HistoryID to the History record, SPQueryHistoryFromhistoryID; 2 (; is not ordinary semicolon, is a stored procedure group) indicates HistoryID pair History record another similar method

2, business logic

For example, also book, SPRETURNBOOK is purchased, then spbuybook

At this point, you may have to call a lot of tables, or many data logic:

Create Proc spbuybook (@ibookid int, - book id @ioperatorid int - operator ID) ----------------------------- ---------------- Name: SPBUYBOOK - FUNC: Buy a book - Var: See - ISE: SPWRiteOperateRecord, SpProc2, SpProc3 ....-- User: Chair3, Chair4 - Author: chair3 # sapphiRestudio (www.chair3.com) - Date: 2003-4-16 - Memo: Temporarily writing, give you a Sample ---------- -------------------------------- Asbegin Begin TRAN

- Data Logic: Update Book Table, Reduced Quantity Update Book Set BookNumber = BookNumber-1 Where Bookid = @ iBookId ...

- Business Logic: Writing Operation Record EXEC SPWRITEOPERATERECORD 'Someone sells a book, the ID of the book is xxx' ... commit tran End Don't try to make all things to do, you should consider the partial code points For smaller stored procedures to achieve code common and maintainability. Of course, it is necessary to have a high performance, it is best to be completed separately.

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

When you are in SEG, other colleagues are not allowed to read the table directly, they can only have permission to call Store Procedure. This is certain according to the development environment, of course, not absolute, try to do this.

In this way, the consequences of modifying all programs because modifying data tables is modified.

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 the direct operation of the client to the database table (with 3 is a bit) 4.2 Avoid cyclic writing on the client to the database Operation, transfer the loop to the server. 4.3 ... forgot ...

At 4 o'clock, many people's procedures are very slow, because there are too many networks.

转载请注明原文地址:https://www.9cbs.com/read-8864.html

New Post(0)