sql query optimization techniques

BackgroundThe system serializes the data object JSON into a database field.The report module needs to get the value of real-time data objects, and it is time-consuming and inefficient to make numerical judgments in SQL.AnalysisThe inefficiency of the current implementation is mainly the unreasonable design of the program structure.The SQL bottleneck is as follows, where wordstr is the statistic keyword, and 7 is the string length of wordstr.sum (len(@

, the engine cannot take advantage of the index, such as:SELECT * from T WHERE name like '%jhon% 'It is recommended to use full-text search.6. Force the query optimizer to use an index if necessaryUsing parameters in the WHERE clause can also cause a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot say that the Access plan's selection is deferred until run time, a

In the case of a large record, the not in query will certainly be slow and unacceptable. Like what:SELECT A.* fromWHERE A.user_id not in (SELECT B.user_id from Table_b)the best way to find it now is to check it in seconds. For example, query the DEPT_ID data with table A and b not available:SELECT A.*from tb_dept A, Tb_dept_lev BWHERE= b.dept_id (+) and is NULLThe (+) number can be understood as: + is a

], [extent1].[ Usertype] As [usertype], [extent1].[ TEL] as [tel], [extent1].[ CreateDate] As [createdate], [extent1].[ Loginnum] As [Loginnum], [extent1].[ Lastdate] As [lastdate], [extent1].[ Shopid] as [shopid] from [dbo].[ S_users] as [Extent1] WHERE N'liuyangh' = [Extent1]. [LoginName] So instead of simply lambda expressions or delegate types, we try to use an expression tree to pass the argument. Why is that? Because Dbcontext.set public static iquerya

Debugging the following statement will find that the Slect statement without the plus (NOLOCK) will wait for the lock to be queriedBEGIN TRAN//Add and Subtract Tablockx (table Lock) When selected, SQL Server will place an exclusive lock on the entire table until the command or transaction ends. This prevents other processes from reading or modifying the data in the table. SELECT * from Student holds shared locks, no waiting, sharedUpdate Student Set s

fact there is only one member table and one distributed partitioned view per server. The location of the data is transparent to the application. NBSP;11, rebuilds the index DBCC REINDEX, DBCC INDEXDEFRAG, shrinks data and logs DBCC SHRINKDB,DBCC shrinkfile. Set up automatic shrink logging. For large databases do not set up automatic database growth, it can degrade server performance. There is a great deal of attention in the writing of T-SQL, the fol

Tags: style using SP on C log r ad Size1. Split the multi-table connection into smaller parts in order to execute2. Replace the outer connection with an inner connection.3. Priority line which can greatly reduce the operation of the return row, and then the production of the temporary table and other tables to the left connection.4. Modify the like program to remove% because there is no index to use.5. Use stored procedures to encapsulate which complex SQL