SQL where clause in large amount of data in the loop instead?-CodePudding

For example, actual situation more complex: I set up a web page, the user will enter thousands of data query database: select product_name, deliver the from the product where product_name in thousands of name (user input),

When I use the foreach loop instead of can not? That is, in the select external plus a loop, each query a product_name, recycling thousands of times, so efficiency will be higher than in point? Thank you ~

CodePudding user response:

The

refer to the original poster wyc9296 response:

, for example (actual situation more complex) : I set up a web page, the user will enter thousands of data query database: select product_name, deliver the from the product where product_name in thousands of name (user input),

When I use the foreach loop instead of can not? That is, in the select external plus a loop, each query a product_name, recycling thousands of times, so efficiency will be higher than in point? Thank you ~

 
- the first insert thousands of user input product_name into a temporary table, then use the join to connect, so quickly, than you use in circulation faster 
The select Amy polumbo roduct_name, a. d. eliver the from the product a 
INNER JOIN # TAB ON Amy polumbo roduct_name b=p. roduct_name 
- where product_name (in thousands of user input name)

CodePudding user response:

In this case, the front desk should be paged? So may be better, if paging is the name on the index can give you some help, of course may need in the index or the fields up and do some small hands and feet,

If the situation is to more simple point: it is only such a single table query, this form according to the total quantity is probably how many? After thousands of name in this, can from this list to get to many records? If less, and the name is a 1:1 relationship, for example, has thousands of, and according to the total amount may be hundreds of thousands of millions of even more, so the name on the index can solve the problem,

You say things are more complicated, of course, don't know to what extent are complex, so is not conclusive,

CodePudding user response:

reference 1/f, the rain stopped reply:

Quote: refer to the original poster wyc9296 response:

For example, actual situation more complex: I set up a web page, the user will enter thousands of data query database: select product_name, deliver the from the product where product_name in thousands of name (user input),

When I use the foreach loop instead of can not? That is, in the select external plus a loop, each query a product_name, recycling thousands of times, so efficiency will be higher than in point? Thank you ~

- the first insert thousands of user input product_name into a temporary table, then use the join to connect, so quickly, than you use in circulation faster
The select Amy polumbo roduct_name, a. d. eliver the from the product a
INNER JOIN # TAB ON Amy polumbo roduct_name b=p. roduct_name
- where product_name (in thousands of user input name)

Specific how to write SQL?

CodePudding user response:

reference wyc9296 reply: 3/f

Quote: refer to the 1/f, rain stopped reply:

Quote: refer to the original poster wyc9296 response:

For example, actual situation more complex: I set up a web page, the user will enter thousands of data query database: select product_name, deliver the from the product where product_name in thousands of name (user input),

When I use the foreach loop instead of can not? That is, in the select external plus a loop, each query a product_name, recycling thousands of times, so efficiency will be higher than in point? Thank you ~

- the first insert thousands of user input product_name into a temporary table, then use the join to connect, so quickly, than you use in circulation faster
The select Amy polumbo roduct_name, a. d. eliver the from the product a
INNER JOIN # TAB ON Amy polumbo roduct_name b=p. roduct_name
- where product_name (in thousands of user input name)

Specific how to write SQL?

Not to say clear? Put you in the content of the separate insert a table inside, and then use the above link way

CodePudding user response:

Use the where make queries only exists

CodePudding user response:

 
- 1. Create a temporary table, store thousands of user input name 
The create table TMP as thousands of the select the user to enter the name as product_name from dual; 

SQL realize 
 - 2.The select Amy polumbo roduct_name, a. d. eliver 
From the product a 
Where the exists (select null from TMP b where p. roduct_name=Amy polumbo roduct_name);

CodePudding user response:

refer to 6th floor qq646748739 response:

 
- 1. Create a temporary table, store thousands of user input name 
The create table TMP as thousands of the select the user to enter the name as product_name from dual; 

SQL realize 
 - 2.The select Amy polumbo roduct_name, a. d. eliver 
From the product a 
Where the exists (select null from TMP b where p. roduct_name=Amy polumbo roduct_name);

The create table TMP as thousands of the select the user to enter the name as product_name from dual; In this way, more than 4000 characters, complains, prompt long,
Suggest the building Lord, described the application scenario of more specific

CodePudding user response:

Like this:
The select product_name, deliver the from the product where product_name in (
The select name1 from dual union all
The select name2 from dual union all
The select name3 from dual union all
The select name4 from dual union all
The select name5 from dual union all
.
The select name1000 from dual
)
Name1 name2, name3... Name1000, it is the user input values, the content is by entering values in parentheses, spell out the SQL statement,

CodePudding user response:

refer to the eighth floor ZBDZJX response:

like this:
The select product_name, deliver the from the product where product_name in (
The select name1 from dual union all
The select name2 from dual union all
The select name3 from dual union all
The select name4 from dual union all
The select name5 from dual union all
.
The select name1000 from dual
)
Name1 name2, name3... Name1000, it is the user input values, the content is by entering values in parentheses, spell out the SQL statements,

This is to avoid in the list is too long?

CodePudding user response:

references 9 f minsic78 response:

Quote: refer to the eighth floor ZBDZJX response:

Like this:
The select product_name, deliver the from the product where product_name in (
The select name1 from dual union all
The select name2 from dual union all
nullnullnullnullnullnullnullnullnullnull