vBcron batching?

[Dean C]

Well i'm just looking into it. I need to run about 10k queries every night around midnight. The queries are ran in threes. Two selects and one update.

Obviously it would not be sensible to run that many queries in one go. So would it be possible to do it in batches using vBcron?

[Andreas]

Jeez! Are you sure the there isn't smth. wrong with your database design?

[Dean C]

Hehe probably ;) Basically i'm upgrading my battle system to work vB3. I have my table structure like so:


CREATE TABLE `battle_records` (
`battleid` smallint(5) unsigned NOT NULL auto_increment,
`userid1` smallint(5) unsigned NOT NULL default '0',
`userid2` smallint(5) unsigned NOT NULL default '0',
`votesforuserid1` smallint(5) unsigned NOT NULL default '0',
`votesforuserid2` smallint(5) unsigned NOT NULL default '0',
`threadid` text NOT NULL,
`battletype` varchar(50) NOT NULL default '',
`timeadded` int(14) default NULL,
`winner` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`battleid`)
) TYPE=MyISAM AUTO_INCREMENT=1888 ;


Now I want to keep a log of all my users wins/losses. I'd rather do this every night and restrict it to one table than having to manage several tables when I go to edit/delete a battle. So the situation i'm faced with is updating every user on my boards win/loss tally in the user table. That way I can display the information in the postbit/profile without any extra queries :)

[Modin]

if you have shell access I'd recommend doing something this big via unix cron.

if you don't have shell access then batching is possible but would be tough. You'd have to write somewhere (either in the db or a file) saying how far a previous batch got so that you're not redoing queries. This opens up race conditions where you'd have to obtain a semaphore before starting... (A semaphore is a lock, if one process already has the lock then the next one who wants the lock will wait until the one holding the lock is finished.)

Something similar to this at the top of the batch file


<?php
$mutex = sem_get(12345);

//grab the lock and only do actions if lock was successful
if(sem_acquire($mutex))
{
$file = fopen("mark_file.txt", "w+");
//read and write some marking data to a file
fclose($file);

//release the lock
sem_release($mutex);

//start queries based on acquired mark
}
?>

[Dean C]

Interesting, I've never worked with a real-cron system on UNIX so I'll talk to my server admin when he gets back off holiday and see what we can do :)

[Andreas]

Hmm, I am not sure about your table structure and what you actuaklly want to do, but assuming that winner is the userid which won the battle and that your user table has fiels battles, wins and losses for the # of battles, wins and losses of each user, the following queries might work:


CREATE TEMPORARY TABLE battle_results_temp (userid INT( 10 ) NOT NULL , battles INT( 10 ) NOT NULL , wins INT( 10 ) NOT NULL);
CREATE TEMPORARY TABLE battle_results_temp2 (userid INT( 10 ) NOT NULL , battles INT( 10 ) NOT NULL , wins INT( 10 ) NOT NULL, losses INT (10) NOT NULL);
INSERT INTO battle_results_temp SELECT userid1 AS userid, COUNT(userid1) AS battles, SUM(IF(userid1=winner, 1, 0)) AS wins FROM battle_records GROUP BY userid1;
INSERT INTO battle_results_temp SELECT userid2 AS userid, COUNT(userid2) AS battles, SUM(IF(userid2=winner, 1, 0)) AS wins FROM battle_records GROUP BY userid2;
INSERT INTO battle_results_temp2 SELECT userid, SUM(battles) AS battles, SUM(wins) AS wins, SUM(battles) - SUM(wins) AS losses FROM battle_results_temp GROUP BY userid;
UPDATE battle_results_temp2, user SET user.battles=battle_results_temp2.battles, user.wins=battle_results_temp2.wins, user.losses=battle_results_temp2.losses WHERE battle_results_temp2.userid=user.userid;

[Xenon]

another way would be to use the CASE function to assign different values to different users, you can save a lot of queries that way :)

[Dean C]

My table is exactly as it is above. Right now I have this query which will give me the wins/losses for a user (thanks to a guy at sitepoint):


select sum(
case when $id = userid1
and votesforuserid1 > votesforuserid2
or $id = userid2
and votesforuserid1 < votesforuserid2
then 1 else 0 end
) as wins
, sum(
case when $id = userid1
and votesforuserid1 > votesforuserid2
or $id = userid2
and votesforuserid1 < votesforuserid2
then 0 else 1 end
) as losses
from battle_records
where $id in ( userid1 , userid2 )


Where $id is the userid in question.

The thing that i'm stuck with now is the easiest way to run that for all my users every night. Obviously running that 10,000 times is not very efficient.