1 big query or 2 small queries?

which is faster and more efficient?

[carsten888]

What would be better:
1 query getting 15 columns of the whole table (al sorts of data types)

2 smaller queries:
- 3 columns of the whole table
- 15 columns of 1 row

number of rows could be anything but is most likely to be something between 30-150.

which is faster and more efficient?

[Demaestro]

Well the good thing about keeping them in one query is that you can loop the one record set instead of having to loop through 2. And if you want the data to line up you have to make sure they both return the same amount of rows and you have to do the job of matching rows from one record set to the other so you can do the job of displaying the rows corrected. Plus things can get silly if you put a order by on one but not the other.

1 query = 1 database connection.

2 queries = 2 database connections.

Even if you keep the connection open and close it once you still have to tax that connection with 2 queries instead of 1. Plus you have to create 2 variables in memory to hold both outputs instead of 1.

In my eyes 1 is better. If you only need 3 columns then write 1 query that returns 3 columns, and when you need 16 columns write 1 query that returns 16 columns.

[edited by: Demaestro at 2:38 pm (utc) on Mar. 24, 2008]

[carsten888]

"you can loop the one record set instead of having to loop through 2"
maybe I wasn't clear enough.
In the option for 2 queries one of them will return only 1 record (so no looping there) of 15 columns. the other query returns a whole set with 3 columns.

[jatar_k]

I think a more important question is how do you need it to scale?

if it is always going to return this small amount of data then the single query is much better

[carsten888]

like I said:
number of rows could be anything but is most likely to be something between 30-150.

[PHP_Chimp]

I would think that a single query should be faster. As opening and closing database connections take a fair amount of time in comparison to the actual query. You could of course use a persistent connection to get around this.
Although as it has been pointed out you do need to think about what will happen to the size of these queries over time. As your 30-150 rows is not bad, but what happens when it is 300-1500 or 3000-15000? As what works for a small result set may not work so well for a large result set.

However the only way to be certain would be to test on your system. As each set of servers will be slightly different.

[edited by: PHP_Chimp at 9:30 am (utc) on Mar. 26, 2008]

[carsten888]

"As opening and closing database connections take a fair amount of time in comparison to the actual query. You could of course use a persistent connection to get around this."
It is for a Joomla extension, so I use the Joomla-database object. I'm not sure how Joomla deals with opening and closing connections.