Database TIPS: 1. When creating a table for record or data analysis: Id is used as the primary key, 3... indicates the number of messages. The user account id is used as a foreign key. One user corresponds to a unique accountId and one accountId can correspond to multiple data entries. 2. Create an index :: for example, you need to query other
Database TIPS: 1. When creating a table for record or data analysis: Id is used as the primary key, 3... indicates the number of messages. The user account id is used as a foreign key. One user corresponds to a unique accountId and one accountId can correspond to multiple data entries. 2. Create an index :: for example, you need to query other
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)
