Database TIPS::
1, used for recording or data analysis of the table when created:: Use the ID as the primary key, ... represents the number of message bars, user account ID is used for foreign keys, a user corresponding to the unique AccountId
A accountid can correspond to more than one data;
2. Create an index: For example, if you need to query other fields based on the registered version number and the registration game ID, you can create an index based on the version number and game ID: the equivalent is to build the index according to the query criteria;
3, database query optimization:: (1), cautious use of SELECT DISTINCT, from the record set to exclude duplicate records, the use of DISTINCT;
(2), select the optimal efficiency table name order SQL Server processes the table names from the FROM clause in the right-to-left order, and when the FROM clause contains more than one table, you must select the table with the fewest records as the underlying table, that is, the table with the less dataIn the end; (Note: There are more than three tables connected to the query, you need to select the crosstab as the base table)
4. Alias using the table:: Connect multiple tables, use aliases to prefix aliases on each column
5. It is best to use Sarg as a where search condition:: For example, a comparison between columns constants. Non-SARG search conditions in the WHERE clause such as "is NULL", "<>", "! =", "!>", "!<", "not", "not EXISTS", "Not in", "not" and "like '%500 ' , usually (but not always) prevents the query optimizer from using the index to perform a search. An expression that includes a function on a column, an expression that uses the same column on either side, or a column (not a constant) is not sarg. (Note: If you do not know if a particular where query is Sarg, you can check the query execution plan in the Query Analyzer)
6. The connection order in the where sentence: SQL Server parses the WHERE clause in the bottom-up order, so that the connection between the tables must be written before the other where conditions, the criteria that can filter the most data records must be written at the end of the WHERE clauses.
7, to avoid the use of difficult formal expression::
8. Avoid sequential access to large data table rows:
9. Correct use of Union and UNION ALL:: When using union, the equivalent of executing select distint,union on the result set will federate two similar record tables and then search for duplicate records and exclude;;; if there are duplicate records in the searched federated record set, The union all is used.
10, EXISTS and in use:: In the query based on the underlying table, in order to satisfy one condition, often need to connect to another table, in this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query.
11, the use of stored procedures instead of direct write query statements:: Reduce network traffic and response time, improve application performance;
12, stored procedure name do not start with sp_:: This special prefix is reserved for the system stored procedures, user-defined storage runs, will slightly reduce some of the execution efficiency.
13. The owner of the old City must be the same: the owner of all objects called in the same stored procedure should be the same, and the dbo is more appropriate.
14. Keep the food as short as possible: Keep the TSQL transaction as short as possible, which reduces the number of locks.
15, tinyint (0-255,-128-127 1 bytes); smallint (0-65535,-32768-32767 2 bytes)
Mediumint (0-16777215,-8388608-8388607 4 bytes); Int (0-4294967295,-2147483648-2147483647 8 bytes)
Optimization tips for databases
