sql query optimization techniques

In general, Oracle databases operate more efficiently than SQL Server databases in large data environments. From the query optimization alone, the two are quite different. Below, the author of the database query optimization of the difference to do a description, if there is

2005 provides the INCLUDE parameter in the Create Index statement, add other fields of the data table unrelated to the key-value column to the leaf layer of the non-clustered index to expand the non-clustered index function. However, these fields are not sorted and other additional maintenance actions are not performed. When querying, you can obtain all relevant data only by reading the index structure, without accessing the table or clustered index data, thus reducing disk I/O operations, redu

The optimization scheme of SQL Server Multi-table query is the main content of this article, we give the optimization scheme and specific optimization examples, then let us take a look at this part of the content. 1. Execution path This feature of Oracle greatly improves t

In our usual SQL query, in fact, we have a lot to pay attention to the principle, since the implementation of SQL query optimization , this article will introduce 30 query optimization

. Please submit the query again, and do not specify any prompts in the query, or use set forceplan.Check the following documents, that is, this statement does not support option (merge join );Switch to not exists and try again to see if the effect will be better.Select * From tablea where not exists (select 1 from tableb where tablea. ID = tableb. ID)After passing the test, it is found that the speed is imp

I have had 10 SQL optimization experiences in the past, but I still feel that it is not specific enough. Now I want to add some specific problems I encountered during my work. Today, let's talk about the situation of select... where... in. Sometimes there are many discrete values in our query conditions, such as the id value of a table. In this case, we can on

tried to do this using the following SQL (for the sake of comparison, the columns of both tables are output): SELECT stu.name, stu.class, s.name, s.score FROM student AS stu LEFT JOIN score AS s ON stu.name = s.name AND stu.class = ‘A‘ At first glance, there seems to be no problem, but also left the connection and on the condition of the increase in class restrictions, in fact, the results appear in Class B classma

First, we will compare two paging SQL statements. For example, the news table has 0.15 million records, NewsTypeId10 has 90 thousand records, and NewsTypeID10 is currently queried. Which SQL statement will be considered as highly efficient? First, compare the two paging SQL statements. Assume that the news table has 0.15 million records, NewsTypeId = 10 has 90 th

In the actual development often encounter the same keyword need to multi-field fuzzy query, such as a user table in the Input keyword query may be to the user name, user name, contact phone fields such as fuzzy queryIf written as: where UserName like '% ' keyword% ' or fullName as '% ' keyword% ' or mobile kind '% keyword% ' This way will result in very low performance, hundreds of thousands of data may be

1. UseProgramTo minimize the number of accesses to the database. By searching for parameters, You can minimize the number of rows accessed to the table and the result set, thus reducing the network burden; separate operations should be processed separately to improve the response speed each time. When using SQL in the data window, try to place the indexes in the first column selected;AlgorithmThe structure should be as simple as possible; During the

"> 2. Merge into statement: For the top statement, you can use the merge into statement --Merge into implementation method merge into EMP using (SELECT * from emp_new1 ) E on (e.empno = emp.empno) when matched then update set emp.empdesc = E.empdesc;The execution results are the same as the results in 1, assuming inference. It is recommended to use the merge into method. Because merge into only visits once emp_new1. Iii. Deleting delete op

difference between Xxx_bin and XXX is case sensitive. Here we will a.s all add binary qualification, first to lowercase, that is, to convert the temporary result set to Utf8_bin, then use B. The index can be used directly when the s is matched. In fact, the essence of the two rewriting methods is the same, the difference is that 1 is implicit conversion. In theory, 2 is faster. Summary The fields that do join are designed to be exactly the same as the type. » Reprint Please indicate so

In a data query, starting with 2008, SQL Server provides a new data type, HierarchyID, specifically for manipulating hierarchical data structures.The HierarchyID type logically encodes information about a single node in the hierarchy tree by encoding the path from the root of the tree to the node.This path is logically represented as a sequence of node labels for all children that are accessed after the roo

In the previous article "Supplement to SQL query optimization -- (in)", we mainly discussed the query statements such as select... from... where... in. Next, let's discuss Select... from... where... exists. The query speed of exists is much higher than that of in, which is w

return any result sets, but consumes system resources and should be changed to this: CREATE TABLE #t (...) 13, a lot of times with exists instead of in is a good choice: Select num from a where num in (select num from B) Replace with the following statement: Select num from a where exists (select 1 from b where num=a.num) 14, not all indexes are valid for the query, SQL is based on the data in the table to

, shrink data and log 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 lot of emphasis on T-SQL, and here's a list of common points: first, the process that the DBMS processes the query plan is this:The lexical and grammatical checks of the query