Background script (crontab) causing heavy load

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

This could be what you're after, and if it's not, it should give you an idea of how to get what you want...

Code: Select all

SELECT object.id AS object, 
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
That gives me (using your values from earlier)

Code: Select all

object 	num_choice 	total_rating 	final_score
1       	4           	10             	14
Yaniv
Forum Newbie
Posts: 13
Joined: Tue Jan 09, 2007 6:28 am

Post by Yaniv »

Grim... wrote:So it gets one 'point' for each user that chose it, plus whatever ratings it has?
It gets raised a point if the user rated +1 and loses a point if the user rated -1.
The result must be for each object per each choice.


About stored procedures, I can't use that because I must leave the project DB independent.
Last edited by Yaniv on Wed Jan 10, 2007 9:42 am, edited 1 time in total.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

So how about the SQL above (which I suspect you missed because of the new page)?
Yaniv
Forum Newbie
Posts: 13
Joined: Tue Jan 09, 2007 6:28 am

Post by Yaniv »

Grim... wrote:This could be what you're after, and if it's not, it should give you an idea of how to get what you want...

Code: Select all

SELECT object.id AS object, 
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
That gives me (using your values from earlier)

Code: Select all

object 	num_choice 	total_rating 	final_score
1       	4           	10             	14
That won't work because it groups them by object.id .
I need each object to list all choices and each choice to have a different result for each object.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

I can't see how choice links to object - there seems to be no common id's between them.
kingconnections
Forum Contributor
Posts: 137
Joined: Thu Jul 14, 2005 4:28 pm

Post by kingconnections »

I would like to ask a question real quick.

On this section of code (below), are you opening database connections and retrieving info while in those loops?

Code: Select all

objects = (Retrieve list of Objects from DB) 
foreach(objects) 
{ 
    users = (retrieve list of users who are linked to this object) 
    foreach(users) 
   { 
      profile = (retrieve list of user choices for this user) 
      (count each choice to a large list of choices statistics; each choice +1 if a user chose it) 
   } 
} 

(Send the choices statistics back to the database)

The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.

so for example

Code: Select all

// just examples
while ($row = mssql_fetch_assoc($result)){
	$in_website[]=strtoupper($row['server']);
}

while ($row = mssql_fetch_assoc($result)){
	$in_EPO[]=strtoupper($row['NodeName']);
	$server =strtoupper($row['NodeName']);
	$Epo_Data["$server"]=$row['LastUpdate'];
}


then ouput 

foreach ($server)

{
echo  $Epo_Data["$server"]
}
This is a quick rough example. Hope I explained it well, probably not. But it did cut my code down from minutes to almost instantly load time.
Yaniv
Forum Newbie
Posts: 13
Joined: Tue Jan 09, 2007 6:28 am

Post by Yaniv »

Grim... wrote:I can't see how choice links to object - there seems to be no common id's between them.
They are linked indirectly by object_user and user_choice
Yaniv
Forum Newbie
Posts: 13
Joined: Tue Jan 09, 2007 6:28 am

Post by Yaniv »

kingconnections wrote:I would like to ask a question real quick.

On this section of code (below), are you opening database connections and retrieving info while in those loops?

Code: Select all

objects = (Retrieve list of Objects from DB) 
foreach(objects) 
{ 
    users = (retrieve list of users who are linked to this object) 
    foreach(users) 
   { 
      profile = (retrieve list of user choices for this user) 
      (count each choice to a large list of choices statistics; each choice +1 if a user chose it) 
   } 
} 

(Send the choices statistics back to the database)

The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.

so for example

Code: Select all

// just examples
while ($row = mssql_fetch_assoc($result)){
	$in_website[]=strtoupper($row['server']);
}

while ($row = mssql_fetch_assoc($result)){
	$in_EPO[]=strtoupper($row['NodeName']);
	$server =strtoupper($row['NodeName']);
	$Epo_Data["$server"]=$row['LastUpdate'];
}


then ouput 

foreach ($server)

{
echo  $Epo_Data["$server"]
}
This is a quick rough example. Hope I explained it well, probably not. But it did cut my code down from minutes to almost instantly load time.

Yeah I do database access inside those loops. Perhaps that is the problem.
kingconnections
Forum Contributor
Posts: 137
Joined: Thu Jul 14, 2005 4:28 pm

Post by kingconnections »

Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....
Yaniv
Forum Newbie
Posts: 13
Joined: Tue Jan 09, 2007 6:28 am

Post by Yaniv »

kingconnections wrote:Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....
I will have to give this thing a shot. Thanks for the direction
Yaniv
Forum Newbie
Posts: 13
Joined: Tue Jan 09, 2007 6:28 am

Post by Yaniv »

It turned out rather simple to do this for a single object.

Code: Select all

SELECT *, SUM(object_user.rating)
FROM user_choice, object_user
WHERE user_choice.user_id=object_user.user_id
AND object_user.object_id=1
GROUP BY user_choice.choice_id
ORDER BY user_choice.choice_id
Yet I want to find a way to do the same thing for all object together to save database access time. In this method I still need to call this query for each object I have on the system.
Post Reply