[SQL Server] SQL server problems

Do not use cursor

I wonder if you know that every execution of fetch is equal to execution of a SELECT command? This means that if your light is marked with 10000 records, it will execute 10000 select!

I used a T-SQL to override a cursor-based stored procedure, the table only had 100,000 records, the original stored procedure took 40 minutes to complete, the new stored procedure only takes 10 seconds. Here, I think you can see what an incompetent programmer is doing !!!

We can write a small program to obtain and process data and update the database, which is sometimes more effective. Remember: T-SQL is powerless for loops.

 Do not useSelect
*

This is not easy to do. I know it too well, because I often do it myself. However, specifying the columns you need in the SELECT statement brings the following benefits:

1. Reduce memory consumption and network bandwidth

2. You can get a safer design

3. Give the query optimizer the opportunity to read all required columns from the index

Use transactions

Use transactions, especially when the query is time consuming. If a problem occurs in the system, this will save your life. Generally, experienced programmers have some experience-unexpected situations that may cause the storage process to crash.

Careful deadlock

Access your table in a certain order. If you lock table A and table B first, lock them in this order in all stored procedures. If you first lock table B in a stored procedure and then lock Table A, this may lead to a deadlock. If the lock sequence is not designed in detail in advance, the deadlock is not easy to detect.

Use a database with a large amount of data during program Encoding

Testing databases used by programmers in development generally have a small amount of data, but often end users have a large amount of data. Our usual practice is wrong. The reason is very simple: the hard disk is not very expensive now, but why should we wait until the performance problem is irrecoverable?

Do not useInsertImport large amounts of data

Do not do this unless it is necessary. With UTs or BCP, you can enjoy both flexibility and speed.

Pay attention to timeout issues

When querying a database, the default value of the database is generally small, for example, 15 seconds or 30 seconds. Some queries run longer than this, especially when the data volume of the database keeps increasing.

Do not insert records in the detail table.Select
Max (ID)

This is a common error. When two users insert data at the same time, this will cause an error. You can use scope_identity, ident_current, and @ identity. If possible, do not use @ identity, because when a trigger exists, it may cause some problems (see the discussion here ).

Avoid setting columnsNullable

If possible, you should avoid setting the column as nullable. The system will allocate an additional byte to each row of the nullable column, resulting in more system overhead during query. In addition, setting columns as nullable makes encoding complex, because each access to these columns must be checked first.

I am not saying that nulls is the root cause of troubles, although some people think so. I think if you allow "NULL data" in your business rules, setting the column as nullable sometimes plays a very good role.

Try not to useTextData Type

Do not use text unless you use text to process a large amount of data. Because it is not easy to query and slow, it will waste a lot of space if it is not used well. Generally, varchar can better process your data.

Try not to use temporary tables

Try not to use temporary tables unless you have. Generally, subqueries can replace temporary tables. Using a temporary table will bring about system overhead. If you program using COM +, it will bring you a lot of trouble, because COM + uses the database connection pool, temporary tables exist from beginning to end. SQL
Server provides some alternative solutions, such as the table data type.

Learn to analyze and query

SQL
Server Query analyzer is a good partner. You can use it to learn how queries and indexes affect performance.

Integrity of reference

Define the primary key, Uniqueness constraint, and foreign key, which can save a lot of time.