Supplement to SQL query optimization-(in)

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 only add a series of values after the in statement, separated by commas. If the SELECT statement is a simple query, there is no problem (simple means that multi-table join queries are not needed). However, if multi-table join queries occur, the efficiency becomes very poor. (Especially when thousands of values are added after in)

 

In this case, we need some tips to improve the query performance. The trick is to use a filtered temporary table to replace the conditional query table. For example, I want to query a phone table and associate it with other tables, such as a transportation table or a ticket table. However, the query condition contains thousands of IDs in the phone table, which is generally where phone. ID in. At this point, we can do this in two steps: first use select (the field you want) into # temptable from phone where ID in (..........); next, we wanted to associate the phone table for joint query. Now we have joined # temptable. Delete the phone table Association and do not forget to drop table # temptable.

 

Using temporary tables for filtering in this separate step is much more efficient than directly associating queries. If you do not believe it, try it on your own.