Select docid, sum (A. Score + B. Score) as total itemset_one a left join itemset_two B on A. docid = B. Do

Currently, the last SQL statement of the exdocprocessing () function is slow to execute. It is a left join statement.

Select docid, sum (A. Score + B. Score) as total itemset_one a left join itemset_two B on A. docid = B. docid is too slow

 

See the followingArticle

1. Http://www.itpub.net/viewthread.php? Tid = 1008087

Why is left join faster than inner join sometimes?
For example, two tables are joined with the same column ID. Assume that the IDs of both tables are unique.

-- Use Inner join <--------- (VVI)
Select T1. *, T2 .*
From T1
Inner join T2 on t2.id = t2.id

-- Use left join <------------ (vvj)
Select T1. *, T2 .*
From T1
Left join T2 on t2.id = t2.id
Where t2.id is not null
Why is left join much faster than inner join?
---------------------------

A

The Post says it is becauseWhere t2.id is not nullIt works..To try this, you must clarify two problems.

(1) The two SQL statements (VVI) and (vvj) are indeed equivalent.

(2) Explicit useWhere t2.id is not nullThe premise is that t2.id must have been declaredNot null

B

TryHttp://www.dbforums.com/showthread.php? T = 1634590Mentioned

  Code   


    code highlighting produced by actipro codehighlighter (freeware) 
 http://www.CodeHighlighter.com/
 -->  If   you use salary   +   coalesce (bonus,   0  ) instead,   If   bonus   is   missing, then coalesce (bonus,   0  ) yields   0  , so salary   +   0  

 

C

Http://it.inhe.net/richbbs/2006/346/3466758.htm

Use left join to minimize the size of the Left table
That isThe partition should be selected first for the left table as far as possible...
Generally
When connecting a table, as long as it is a large table and a small table, or two small tables, the efficiency is very high, but when both sides are large tables, the efficiency costs are relatively large.
MSSQL is originally a relational database, and connection is the theoretical basis of the relational database. Of course, the efficiency will not be too bad. I often have hundreds of thousands of records connected.
However, the efficiency may be dozens of times different from that of other products ..
To sum up, we should try to select the tables before join.

D

AvoidLeft join and null

of course, you may need to execute left join and use null values. However, they do not apply to all situations. Changing the construction method of SQL queries may result in shortening a report that takes a few minutes to only a few seconds. Sometimes, you must adjust the data format in the query to adapt to the display method required by the Program . Although the table data type reduces resource usage, many areas can be optimized in the query. A valuable common function of SQL is left join. It can be used to retrieve all rows in the first table, all matched rows in the second table, and all rows in the second table that do not match the first table. For example, if you want to return each customer and their orders, use left join to display customers with and without orders.

this tool may be used excessively. left join consumes a lot of resources because it contains data that matches null (nonexistent) data. In some cases, this is inevitable, but the cost may be very high. left join consumes more resources than inner join , therefore, if you can re-compile the query so that the query does not use any left join, you will get a considerable return.

(1) a technique used to accelerate the query speed of left join involves creating a table data type, insert all rows in the first table (table on the left side of left join), and then update the table data type using the values in the second table. This technology is a two-step process, but it can save a lot of time compared with the standard left join. A good rule is to try different technologies and record the time required for each technology until you get the best query for the execution performance of your application.

when testing the query speed, it is necessary to run the query multiple times and then take an average value. Because of the query (or stored process) it may be stored in the process cache in SQL server memory , therefore, the first attempt takes a little longer, and all subsequent attempts take less time. In addition, you may be running other queries for the same table when running your query. When other queries lock and unlock these tables, your queries may have to wait in queue. For example, if someone is updating the data in this table when you perform a query, your query may take longer to execute during update submission.

(2)Avoid usingThe simplest way to reduce the speed of left join is to design databases around them as much as possible. For example, assume that a product may have a category or no category. If the products table stores the category ID instead of the category of a specific product, you can store the null value in the field. Then you must execute left join to obtain all products and their categories. You can create a category with a value of "no category" to specify that the foreign key relationship does not allow null values. By performing the preceding operations, you can use inner join to retrieve all products and their categories. Although this seems to be a work und with excess data, it may be a very valuable technology because it can eliminate left join statements that consume more resources in SQL batch processing statements. Using this concept in databases can save a lot of processing time. Remember, even a few seconds is important for your users, because when many users are accessing the same online database application, the seconds actually mean a lot.