Using 1 SQL to retrieve multiple records or multiple SQL to retrieve individual records

Question

I would like ask if there is any performance advantage of 1 over the other.

Here is an example:

// suppose I want to retrieve 10000 different records
select * 
from table_a 
where from in (1,2,3,4,5,6 .... 10000)

// alternatively
select * 
from table_a 
where from=1 or from=2 or from=3 ... from=10000

compared to

select * from table_a where from=1
select * from table_a where from=2
select * from table_a where from=3
.
.
select * from table_a where from=10000

What are the scenarios that one will outperform the other?

The WHERE clause is simplified here, it may have nested AND and OR clauses.

in the case as you showed, using single sql statement should have a better performance. you could also simplify the where clause as from <= 10000 (if possible). — Shiping, Jan 24 '17 at 03:49
@Shiping, yap my example is oversimplified, what about those with multiple and clauses? I am using contiguous number for simplicity. the actual sql have random numbers that are not contiguous. — Mox, Jan 24 '17 at 03:50
generally speaking, fewer IO accesses, better the performance. of course there're always exceptions. — Shiping, Jan 24 '17 at 03:55
@Shiping, in this case the statements are all executed in single DB access — Mox, Jan 24 '17 at 03:57
http://stackoverflow.com/questions/3308280/is-there-a-performance-difference-between-between-and-in-with-mysql-or-in-sql-in — sumit, Jan 24 '17 at 03:59
@sumit, thx for the link, unfortunately it is a separate issue. — Mox, Jan 24 '17 at 04:00
@Mox how can you execute multiple select statements in a single db access? — Shiping, Jan 24 '17 at 04:00
@shiping, yes you can, just needs to be comma separated statements. — Mox, Jan 24 '17 at 04:02
@ManishSingh , this is just an example, it may not be contiguous. and it can be string not just number — Mox, Jan 24 '17 at 04:25
Oh so sorry i seen your example and i think it is suitable for this — Manish Singh, Jan 24 '17 at 04:27

score 1 · Answer 1 · answered Jan 24 '17 at 04:33

1

There are many factors beyond your simple example involved.

For your exact example 1 query is better than 1000, because example is simple and against one field. Main factor is network I/O operations, physical and/or logic reads and such.

But if you have more WHERE conditions especially when there are joins, that can be questionable what is better. And it depends on actual DB tables, relationships, indexes design, types of joins, size of tables and (so and so)... As general direction in most cases 1 SQL is better, but other factors can be much more important than that.
All starts from very careful database design. Mistakes there (happen quite often), cost a lot later. Usually 1000 queries are better when database was designed badly.

answered Jan 24 '17 at 04:33

Vadim

4,027
2
10
26

the actual scenario consist of multiple joins across 4 to 5 tables and may contain 4 to 5 And clauses. – Mox Jan 24 '17 at 04:38
Then,it depends on nature of those joins, if it is normalized joins (one-to-many Foreign Key to Primary Key) it is good to have indexes on Foreign Key columns as well as indexes on fields involved in Where clause like your `from` column in example (BTW not a good name for column). But count of indexes for one table must be in balance. Too many of them may affect performance of CRUD operations. Physically each Index is another table. So... good luck! – Vadim Jan 24 '17 at 13:52

Using 1 SQL to retrieve multiple records or multiple SQL to retrieve individual records

1 Answers1