solutions that use or affect performance in SQL

Recently done a stored procedure, the execution found very slow, unexpectedly need 6, 7 seconds!

After the investigation, the discovery time is mainly consumed in one of the query statements. This statement is used to isolate records with the same data for any one of the two fields in the two tables with the same structure.

For example, the structure of table A is as follows:

 --  member table   table   Member (
 memberid int, -- member ID      membername  varchar  (--   member name  memberphone varchar  (50 ) --  member phone  )  go  

The structure of table B is exactly the same as table A, assuming the table name is MEMBER_TMEP.

Now the Member table has 7,000 non-repeating data, the MEMBER_TMEP table has 2000 data, you need to find out the two tables, the member name or member phone is the same, but the membership ID is not the same record.

According to the usual logic, I wrote this at the beginning:

Select A.memberid,a.membername,a.memberphone      from Member A,MEMBER_TMEP b      where = or =  and <> B.memberid  

This statement seems logical and concise, but why is it so time-consuming to execute?

Although I do not know where this statement is wrong, but also think of trying to implement this query in another way, so I changed the query to the following:

--find records with the same member name but different IDSelectA.memberid,a.membername,a.memberphone fromMember aInner JoinMEMBER_TMEP b onA.membername=B.membername andA.memberid<>B.memberidUnion--And then find the same member phone with the same ID record, to mergeSelectA.memberid,a.membername,a.memberphone fromMember aInner JoinMEMBER_TMEP b onA.memberphone=B.memberphone andA.memberid<>B.memberid

This executes again and executes in seconds.

In fact, I have written a lot of SQL statements like the first one, it has not been the problem, because the amount of data is not so large.

You should try to avoid using or in the WHERE clause to join the condition, or it will cause the engine to abandon using the index for a full table scan. When you switch to union, the performance is greatly improved.

using Union All has a higher performance than union. becauseSQL  union Two query result collection Span style= "Font-family:times New Roman;" >union-all union all union,

In the example above, "union" is used instead of "union ALL" because "records with the same member name but different IDs " and "records with the same member phone but different IDs " may be duplicated, using "union" Duplicate records can be removed .

In fact, this truth has been seen before, but in the writing of the sentence often habitual use of a concise or statement, slowly forget the matter ...

In addition to the above, there is a common scenario where an or statement is used, which is to query a record that the value of a field equals a certain number of values.

For example, you need to check out a record whose member name is "Zhang San", "John Doe". We may write this:

Select *  from where = ' Zhang San ' or = ' John Doe '

Typically, this is not a problem to look at, but in a very large amount of data, the same can affect execution speed.

Another way to do this is to use the in statement , such as the following:

Select *  from where inch (' Zhang San ',' John Doe ')

But some say that the in statement will cause a full table scan . In and not should be avoided as much as possible.

if a specific value that requires a query is a contiguous range of values , such as 90--100, You can use the Bwteen...and statement instead . For example:

Select *  from where between  -  and  -

If you cannot use Bwteen...and, you still need to use the Union method, such as:

Select *  from where = ' Zhang San ' Union All Select *  from where = ' John Doe '

This is because the member name "Zhang San" and "John Doe" cannot have duplicate records, so you can use the higher performance union all instead of the union.

solutions that use or affect performance in SQL