How many queries are "Too Much"?

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Scam Warning
Post Reply
Omarvelous
Registered User
Posts: 232
Joined: Mon Jun 25, 2007 2:24 am

How many queries are "Too Much"?

Post by Omarvelous »

Hey...

My frum is still under dev, but I installed a users on forum MOD, which displays the number of users in a particular forum.... and my queries went from 13 to 68... Mainly due to the query running on each forum/subforum...

Now I know usually a high query count is frowned upon... but is this a "acceptable" amount? And would it matter so much even if those 55 of those queries are executed on one table, SESSIONS_TABLE? I'd think although that it is a lot, due to the simplicity of the query, it shouldn't be too much load on the server with 1000s of users...

And would that have an effect on the cache's ability?
User avatar
Kellanved
Former Team Member
Posts: 2635
Joined: Wed Jan 26, 2005 2:48 pm
Location: Meta-level

Re: How many queries are "Too Much"?

Post by Kellanved »

There is no easy answer. it depends on the queries; one poorly written query can be too much. It also depends on your server setup.
However, without knowing the MOD, 68 sounds very excessive.
Nocando is in Idontwanna county. No support via PM
Omarvelous
Registered User
Posts: 232
Joined: Mon Jun 25, 2007 2:24 am

Re: How many queries are "Too Much"?

Post by Omarvelous »

Kellanved wrote:There is no easy answer. it depends on the queries; one poorly written query can be too much. It also depends on your server setup.
However, without knowing the MOD, 68 sounds very excessive.
I might have to agree... I think it could be written more efficiently.... It's only 68 in MY case because it was 13 pre-mod, then a query for every forum/subforum.

Im thinking 1 select all could have been done, dump them into an array, and then grab what you need per forum....

Going to look in ways to optimize on it.

Thanks.
Omarvelous
Registered User
Posts: 232
Joined: Mon Jun 25, 2007 2:24 am

Re: How many queries are "Too Much"?

Post by Omarvelous »

This is the offending query

Code: Select all

			//-- [+] MOD: total user in forum ---------------------------------------
			//-- add

			$reading_sql ='';
			if (!empty($subforum_id))
			{
				// Do not change this (it is defined as _f_={forum_id}x within session.php)
				$reading_sql = " AND s.session_page LIKE '%\_f\_={$subforum_id}x%'";

				// Specify escape character for MSSQL
				if ($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc')
				{
					$reading_sql .= " ESCAPE '\\' ";
				}
			}

			if ($db->sql_layer === 'sqlite')
			{
				$sql = 'SELECT COUNT(session_ip) as num_user
						FROM (
						SELECT DISTINCT s.session_ip
						FROM ' . SESSIONS_TABLE . ' s
						WHERE s.session_time >= ' . (time() - ($config['load_online_time'] * 60)) .
						$reading_sql .
				')';
			}
			else
			{
			$sql = 'SELECT COUNT(DISTINCT s.session_ip) as num_user
					FROM ' . SESSIONS_TABLE . ' s
					WHERE s.session_time >= ' . (time() - ($config['load_online_time'] * 60)) .
					$reading_sql;
			}
			$result = $db->sql_query($sql);
			$subforum_total_users = (int) $db->sql_fetchfield('num_user');
As you can see it is looking for the s.session_page LIKE '%_F_={$subforum_id}x%, there fore it is ran for each subforum_id...

I'm thinking, i could remove that portion of the SQL, and dump not only the subforum_id, but also the user count for all subforums_id one time... and then fetch them based on the ID when needed... so it'll only add 1 Query, a SELECT INTO...
User avatar
Myspoonistoobig
Registered User
Posts: 30
Joined: Fri Mar 25, 2005 8:05 am

Re: How many queries are "Too Much"?

Post by Myspoonistoobig »

Omarvelous wrote:Im thinking 1 select all could have been done, dump them into an array, and then grab what you need per forum....
Jesus, no, don't do that. That's an excellent example of a single query that itself is worse than many queries

Always try to use the SQL query to pare down the data to as small as what you want, rather than sending large datasets back to your script
starware
Registered User
Posts: 309
Joined: Thu May 18, 2006 2:41 am

Re: How many queries are "Too Much"?

Post by starware »

I think it mostly depends on the server you are using. :)
Pardon my bad english, I'm american. :P
Post Reply

Return to “phpBB Discussion”