Is subquery of SQL a weakness?

Msdn defines subqueries as follows:

You can use the result of one query as the input of another query. The subquery results can be used as statements using the in () function, exists operator, or from clause.

A good and commendable rule is to try to replace all subqueries with connections. The optimizer can sometimes "flat" subqueries automatically and replace them with regular or external connections. But that is not always valid. Explicit connections provide more options for selecting the sequence of the table and finding the most likely plan. When you optimize a special query, It is very different to know whether to remove the self-query. This passage comes fromBytes.

Currently, the main pressure on websites comes from databases. Frequent database accesses often cause server crashes. My principle is to minimize the number of database connections and never connect to the database once, but sometimes it is not exactly the case. Depressed, no solution.
 

Some of my friends cannot understand the SQL statements I wrote. It may be because some of the fields I wrote have little to do with this case. Now I want to replace them.

My project has two tables:

1: Movie table, which contains detailed information about movies.

Field Description: resource_volumecount
It refers to the total number of sets of a movie. This field is of the int type.

Other fields that have little to do with this case are omitted.

2: Movie File Table:Tb_resource_primarvideo

It refers to the number of actually existing sets of specific movies in the movie table. For example, kung fu has 1 set, which is based on the movie Id (Resource_id) Associated with the movie table. The movie table has a one-to-many relationship with it.The rest will be omitted if it has little to do with this case.

Requirements:

Take the movie information record set and calculate the missing set of each movie. For example, a complete kung fu movie should haveTwo SetsBut there is only one set in the movie file table.I.

The following SQL statement contains a subquery,Info. resource_volumecountIt refers to the total number of sets of a movie. This field is of the int type. The subquery aims to calculate the total number of sets of a movie in the resource table (Actually exists) To calculate the movie.Total number of missing sets (lastcount)We know that when this statement is executed, the outer record queries the table again when calculating the lastcount field:Movie tableOnce. If there are 10 records on the outermost layer, the query will be scanned in total.Movie table11 times, once connected to the database.

String Sqlcode =   @" Select distinct info. resource_officialname,
Info. resource_id,
Field 1,
(
Info. resource_volumecount -
(Select count (vid3.resource _ id) from movie table info3
Inner join tb_resource_primarvideo vid3 on
Info3.resource _ id = vid3.resource _ id and
Info. resource_id = vid3.resource _ id

)
) As lastcount,
Field 2
From movie table info
Where 1 = 1 " ;

Now we can do it in another way:

There is only one difference between the following statement and the preceding statement: No QueryLastcountHowever, the info. resource_volumecount field (the number of sets for the complete movie) is queried, that is, the subquery is removed. When binding data, the resource_id (movie Id) recorded each time is used)Re-import the databaseSubquery before,

The specific implementation is as follows:
During the loop of the outer record, resource_id is used to obtain the actual number of sets of movies.ProgramTo implement info. resource_volumecount and "Number of actually existing movies" in subtraction. This is also a query table:Movie tableA total of 11 times, 11 times connected to the database.

Private   String Getcount ( String Resource_id)
{
// Returned results
String S = "" ;
String SQL = @" Select count (vid3.resource _ id) from movie table info3
Inner join tb_resource_primarvideo vid3 on
Info3.resource _ id = vid3.resource _ id and
Info3.resource _ id = " + Resource_id;
// Execution Code omitted
Return S;


}

The SQL query statements for subqueries are removed. It is the outermost loop, and each record is obtainedMovie IDThen, call the above method to calculate the actual number of sets of the movie. In this way, the function is the same as that of the first method (including subqueries. However, there is a big gap in efficiency.

String Sqlcode =   @" Select distinct info. resource_officialname,
Info. resource_id,
Field 1,
Info. resource_volumecount , -- How many sets should a complete movie have
Field 2
From movie table info
Where 1 = 1 " ;

Theoretically, it should beQuery the table 11 timesOpenDatabase Connection onceThe performance should be much better, but the opposite is true.Queries a table 11 times and opens the Database 11 times.There will be much less execution time in the latter method. I don't know how much practical use this subquery will have in actual database operations. Is it really helpful? In my experiment, there is little difference when there is very little data. If there are more than 1000 records, there will be a huge performance gap, and sometimes there will be a crash. I am using the Firebird Database Under InterBase. The specific execution time cannot be displayed here. Sorry. Although it is small, it can also reflect the problem. I hope the experts can help me analyze the cause. Can we still use this subquery when querying data?

MyArticleAfter the release, many of my friends gave their own ideas, many of which are good. The main saying is to use join to replace subqueries. However, if there are too many fields to be retrieved, when grouping, multiple fields will appear in group by, which may not be good in performance. and this is just a solution. Can you refer toPerformance problemsWhat are the specific causes? Why is the second solution more efficient?