Database optimization tips and database optimization tips

Database optimization tips and database optimization tips
Database TIPS ::
1. When creating a table for record or data analysis: Use Id as the primary key, 1, 2, 3... indicates the number of messages. The user account id is used as a foreign key. A user corresponds to a unique accountId.
One accountId can correspond to multiple data entries;

2. Create an index: for example, you can create an index based on the version number and game ID when querying other fields :: equivalent to creating an index based on the query conditions;


3. Database Query Optimization: (1) Use select distinct with caution. DISTINCT is used only when duplicate records are excluded from the record set;
(2) Select the table names with optimal efficiency. SQL server processes the table names in the FROM clause sequentially from the right to the left. If the FROM clause contains multiple tables, you must select the table with the least records as the base table, that is, put the table with less data at the end. (Note: if more than three tables are connected for query, You must select the cross table as the base table)

4. Use the table Alias (Alias): To connect multiple tables, use the Alias to prefix the Alias on each Column.


5. It is best to use SARG as the WHERE Search Condition: for example, Comparison Between Columns and constants. In the WHERE clause, you cannot search for SARG conditions, such as "is null", "<> ","! = ","!> ","! <"," NOT "," not exists "," not in "," not like "and" LIKE '% 100' ", usually (but NOT always) the query optimizer is blocked from using indexes for search. In addition, the use of expressions including functions on the column, expressions using the same column on both sides, or expressions comparing with a column (not a constant) cannot be SARG. (Note: If you do not know whether the specific WHERE query statement is SARG, you can check the query execution plan in the query analyzer)


6. Connection sequence in the WHERE clause: SQLSERVER uses the bottom-up sequence to parse the WHERE clause. Therefore, the connection between tables must be written before other WHERE conditions, conditions for filtering the maximum number of data records must be written at the end of the WHERE clause.


7. Avoid using difficult Regular Expressions ::


8. Avoid sequential access to row data of large data tables ::




9. correct use of UNION and union all: When UNION is used, it is equivalent to executing select distint on the result set. UNION will join two similar record tables, and then search for duplicate records and exclude them ;;; union all is used if duplicate records are found in the UNION record set.


10. Use of EXISTS and IN: IN basic table-based queries, IN order to satisfy one condition, it is often necessary to connect the other table. IN this case, using EXISTS (or not exists) usually improves the query efficiency.


11. Use stored procedures instead of Directly Writing query statements: reduces network traffic and response time, and improves application performance;

12. The stored procedure name should not start with sp _: This special prefix is reserved for system stored procedures. User-Defined stored operations will slightly reduce execution efficiency.


13. The owner of the ancient city should be the same: the owner of all objects called in the same stored procedure should be the same, and DBO is more suitable.

14. Keep the food as short as possible: Keep the TSQL transaction as short as possible, which will reduce the number of locks.


15. tinyint (0-255,-128-127 1 byte); smallint (0-65535,-32768-32767 2 byte)
Mediumint (0-16777215,-8388608-8388607 4 bytes); int (0-4294967295,-2147483648-2147483647 8 bytes)

How to optimize the database

Database Design must meet three major paradigms: The first paradigm:
1. Columns with similar content must be eliminated (the elimination method is to create another data table to store them and establish associations)
2. You must create a table for each group of related data.
3. Each data record must be marked with a primary key.

Second paradigm:
1. As long as the content in the data column is repeated, it means that the table should be split into multiple tables.
2. the table to be split must be associated with a foreign key.

Third paradigm:
1. Data columns that have no direct relationship with the primary key must be eliminated (the elimination method is to create another table to store them)
 
How to optimize Databases

Maintenance statement

Dbcc dbreindex rebuilding one or more indexes of a table in the specified database
Dbcc dbrepair remove corrupted Database
Dbcc indexdefrag sorts out clustered indexes and secondary index fragments of a specified table or view.
Dbcc shrinkdatabase: shrink the data file size in the specified database
Dbcc shrinkfile: compresses the specified data file or log file size of the relevant database
Dbcc updateusage reports and corrects incorrect content in the sysindexes table,
This content may cause the use of sp_spaceused
System stored procedures generate incorrect space usage reports

Status statement
Dbcc inputbuffer displays the last statement sent from the client to ms SQL Server
Dbcc opentran if the specified database contains the oldest active transactions and oldest distributed and non-distributed replication transactions,
The related information is displayed. Only when active transactions or databases contain replication Information,
To display the results. If no active transaction exists, an informative message is displayed.
Dbcc outputbuffer returns the current output buffer of the specified system process ID (SPID) in hexadecimal or ASCII format
Dbcc proccache displays process cache information in the form of reports
Dbcc showcontig displays the data and index fragmentation information of the specified table
DBCC SHOW_STATISTICS: displays the current distribution statistics of the specified target in the specified table
Dbcc sqlperf provides statistics on the usage of transaction log space in all databases
Dbcc tracestatus displays the status of the trace tag
Dbcc useroptions returns the SET Option of the current connection activity (setting ).

Verification statement
Dbcc checkalloc check the consistency of the disk space allocation structure of the specified database
Dbcc checkcatalog check the consistency between the system tables in the specified database and between the system tables
Dbcc checkconstraints checks the integrity of specified or all constraints on a specified table
Dbcc checkdb checks the allocation and structural integrity of all objects in the specified database
Dbcc checkfilegroup checks the allocation and structural integrity of all tables in the specified file group (in the current database)
Dbcc checkident ...... remaining full text>