Running a massive script without keeping the user waiting for it.
Discuss Running a massive script without keeping the user waiting for it. in the PHP Development forum on Dev Shed. Running a massive script without keeping the user waiting for it. PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
Posts: 1,676
Time spent in forums: 2 Weeks 5 Days 17 h 46 m
Reputation Power: 71
Running a massive script without keeping the user waiting for it.
Ok,
Lets say if user decides to update item A, over 2 million records linked to that item also need to get updated as well.
Now in this case, which one would you do:
1 - Somehow run the query in the background (run the update script with ajax, jquery,...) and show message on screen the records are updating now, allow a few minutes to see the changes.
2 - Run the script and keep user waiting
Thanks
__________________
Devshed people, please fix the spell check:
System is temporarily busy. Please try again in a few seconds.
"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin
"The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002
Posts: 1,898
Time spent in forums: 2 Weeks 4 Days 19 h 4 m 31 sec
Reputation Power: 1798
That doesn't sound like a great DB design. What data is shared in all of these tables that needs to be updated all of the time? I'll bet that it's something that could be either stored in one only table, or it can be moved somewhere else to make everything a lot easier.
With DB optimisation, the key is to find any data that's shared across the tables and extract that out to one central point instead of having it in 2,000,000 different points.
Posts: 78
Time spent in forums: 22 h 7 m 34 sec
Reputation Power: 0
There are 3000+ hotels
Each have rooms (up to 100)
Each room has packages (up to 20)
you want to reserve hotel rooms??
let's say you have x hotels with p rooms.
each room have package(please define)? possible, but "pkg" will tie up y number of allocation options. So your 2mil permutation is slightly off???
SWAG: you want to update allocation tbl in some way that affects the rooms and hotels? btw, each hotel only has their own rooms, so watch cartesian product on SQL queries...
maybe dan notso maniac
Quote:
Highly doubt its possible!
What is possible? DB update 1 million rows? That is no biggie...
What are trying to do? Other than being cryptic? If the user is kicking off this whole situation, what are they doing? and why are u allowing?
Posts: 1,676
Time spent in forums: 2 Weeks 5 Days 17 h 46 m
Reputation Power: 71
Quote:
Originally Posted by ManiacDan
but...why? This design doesn't require a booking to be updated when the room's details change. What actual updates are being performed?
This is a sample a supplier's control panel. The drop down menus show the list of this suppliers hotels, hotel rooms and packages related to each room.
The grid behind the drop down menu is an annual calendar.
It shows the number of allotments per room, per day of the year.
The supplier may decide to add x number of allotments for any day(s) of the year. So he simply fills the box(ex), (for day or days he wants to edit) with specific number of allotments and hits submit.
In here it might help if I give a little info on the database:
Hotels table is pretty simple, only column to note here is the hotel_id.
Hotelrooms table is simple too, it has room_id, hotel_id,..
Packages id is the same,
allotments table is the tricky one:
Code:
CREATE TABLE `allotments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test_comun_delete` int(11) NOT NULL,
`package_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`hotel_id` int(11) NOT NULL,
`date_added` date NOT NULL,
`day_date` date NOT NULL,
`number_of_rooms` int(11) NOT NULL,
`status` varchar(50) NOT NULL,
`supplier_id` int(11) NOT NULL,
`day_of_the_week` varchar(255) NOT NULL,
`day_rate` decimal(11,2) NOT NULL,
`source` text NOT NULL,
`minimum_number_of_nights` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `day_date_index` (`day_date`),
KEY `day_rate_index` (`day_rate`),
KEY `room_id_index` (`room_id`),
KEY `index_minimum_number_of_nights` (`minimum_number_of_nights`),
KEY `id_package_id` (`package_id`),
KEY `id_hotel` (`hotel_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8476032 DEFAULT CHARSET=latin1
What happens next the script goes through the allotments table and makes update or add based on the posted data. Please note here a lot of queries have to check the database before updating.
A few things:
* Changing allotments value per package effects all the values for all the other packages linked to that room in allotments table.
For example:
PHP Code:
////Check if other packages have allotments
$sql_other_packages = "SELECT id FROM packages WHERE room_id=:room_id AND id<>:id";
$args_other_packages = array('room_id'=>$this_room_id,'package_id'=>$this_package);
$other_packages = DB::Load()->Execute($sql_other_packages,$args_other_packages)->returnArray();
foreach($other_packages as $values=> $other_packages_row)
{
//Check allotments table for this package
$sql_other_packages_all = "SELECT id FROM allotments_new WHERE package_id=:package_id AND day_date=:day_date";
$args_other_packages_all = array('day_date'=>$row['DD'],'package_id'=>$other_packages_row['id']);
$other_packages_all = DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnNumAffectedRows();
//If exists
if($other_packages_all==1)
{
$this_other_package_id = DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnArray();
$sql_other_packages_all_update = "UPDATE allotments_new SET number_of_rooms=:number_of_rooms,
status=:status WHERE day_date=:day_date AND package_id=:package_id ";
if($the_month[$day_without_zero]>0)
{
$status='b';
}
else
{
$status = 'r';
}
* Each package has 365 rows in allotments table per year. Various reasons for that. For example, each day can have a specific rate, season or minimum number of nights.
I have done many applications previously but this one they specifically asked for 365 rows in allotments table per package.
If you require more information, please do not hesitate to ask.
Posts: 8,057
Time spent in forums: 2 Months 1 Day 6 h 47 sec
Reputation Power: 7104
As a general answer to the question, if an operation is going to take more than a few seconds you don't keep the user waiting without feedback, particularly in a web environment.
I didn't read through your design, but if a single operation requires updating a massive number of rows it usually, but not always, indicates a design issue with the database. Often one of two things:
1) You have a piece of data duplicated between rows in a table when it would be more appropriate to have the data in a different, but related, table.
2) You're storing calculated values (that can be derived from other data already in the database).
Posts: 129
Time spent in forums: 2 Days 23 h 1 m 20 sec
Reputation Power: 10
Quote:
* Each package has 365 rows in allotments table per year. Various reasons for that. For example, each day can have a specific rate, season or minimum number of nights.
This is the part that seems like it could be fixed. I am kind of confused on all of it because I haven't really tackled a project of this caliber but it seems as if this table would be full of repetitive data.
Would it not be easier to have the possible packages in one table with an id. and then in the allotments table you could have 365 rows representing days of the week and a column with the package id available for that day? That way when changing the package available for that day all you are doing is changing one integer and not 5-6 rows. And if you need to change it to a package that does not exist yet you just create it and add that id to the table?
I have a feeling that this seems to simple and I have mis understood your schema.
Posts: 1,676
Time spent in forums: 2 Weeks 5 Days 17 h 46 m
Reputation Power: 71
Quote:
Originally Posted by drumn4life0789
This is the part that seems like it could be fixed. I am kind of confused on all of it because I haven't really tackled a project of this caliber but it seems as if this table would be full of repetitive data.
Would it not be easier to have the possible packages in one table with an id. and then in the allotments table you could have 365 rows representing days of the week and a column with the package id available for that day? That way when changing the package available for that day all you are doing is changing one integer and not 5-6 rows. And if you need to change it to a package that does not exist yet you just create it and add that id to the table?
I have a feeling that this seems to simple and I have mis understood your schema.
Hey due, here I explain a bit more:
Each package could have a different value (rate, min_number_of_nights, status,...) for every single day of the year (for next 5 years).
Each package should have 365 rows per year in allotments table so the unique value (day_date, rate, package_id, status,..) can be placed there.
The other thing is that hotels ARE allowed to assign each day of the year as different season. FOr example 2012-01-01 can be high season, 2012-01-02 can be low season and so on and so far.
How do can I show different rates for every day of the year without specifying day_date and package_id?
Posts: 9,917
Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 25 m 59 sec
Reputation Power: 6113
Well the first way to make this operation a little quicker is to only perform operations where there is a change. I don't see where you process the POST data, but if you only change the database rows for which the post data actually changed, you should be a lot better off.
Now as for your humongous loop:
1) args_other_packages is wrong, it doesn't specify the same keys as $sql_other_packages requires. I don't know how this code is functioning as written.
2) You can combine sql_other_packages and sql_other_packages_all into a single query with a JOIN condition, removing thousands of queries from this page.
3) You execute sql_other_packages_all twice for no reason whatsoever. If you need the row count AND the data, return the data and check count($other_packages_all), don't run the query twice.
4) I bet you can combine all these update statements into a single query, but Rudy would be best able to handle that syntax.
5) Is the code you pasted inside yet another loop? I see $row with no reference to where it was created.
Posts: 1,676
Time spent in forums: 2 Weeks 5 Days 17 h 46 m
Reputation Power: 71
Quote:
Originally Posted by ManiacDan
Well the first way to make this operation a little quicker is to only perform operations where there is a change. I don't see where you process the POST data, but if you only change the database rows for which the post data actually changed, you should be a lot better off.
Now as for your humongous loop:
1) args_other_packages is wrong, it doesn't specify the same keys as $sql_other_packages requires. I don't know how this code is functioning as written.
2) You can combine sql_other_packages and sql_other_packages_all into a single query with a JOIN condition, removing thousands of queries from this page.
3) You execute sql_other_packages_all twice for no reason whatsoever. If you need the row count AND the data, return the data and check count($other_packages_all), don't run the query twice.
4) I bet you can combine all these update statements into a single query, but Rudy would be best able to handle that syntax.
5) Is the code you pasted inside yet another loop? I see $row with no reference to where it was created.
I agree a fair bit of this is caused by lack of query writing skills. I am currently working with Rudy's support.
Yes the code I pasted is inside another loop. I believe that part can also be fixed with the help of built-in mysql date functions. At the moment I have a calendar table which contains id, day_date from now till next 10 years. A lot of calculations use that table. I am sure it is something that I can use using mysql date function somehow.
What I crave when writing this is using "SELECT, UPDATE if NULL or ignore if not NULL" query. I see Rudy mentioning some common date functions in chapter 9 SimplySql I'm just about to read. I am pretty sure that can get rid of some of these loops.
Thank you
Edit: Incase you wondered, this is where it all happens:
PHP Code:
$sql_check = "SELECT
annual_calendar.day_date AS DD
, annual_calendar.day_of_the_week AS DOW
, Q1.RATE AS RATE
, Q1.number_of_rooms AS number_of_rooms
, Q1.package_id
FROM
annual_calendar
LEFT OUTER
JOIN
(SELECT allotments_new.day_rate AS RATE, allotments_new.number_of_rooms, day_date, package_id
FROM allotments_new
WHERE allotments_new.package_id =:package_id
GROUP BY day_date) AS Q1
ON Q1.day_date = annual_calendar.day_date
WHERE annual_calendar.day_date <=:next_year
AND
annual_calendar.day_date>=:today
GROUP BY annual_calendar.day_date
ORDER BY `annual_calendar`.`day_date` ASC";
$next_year = $this_year+1;
$args_check = array('next_year'=>$next_year."-01-01", 'today'=>$this_year."-01-01", 'package_id'=>$this_package);
$data = DB::Load()->Execute($sql_check,$args_check)->returnArray();
//$q = DB::Load()->formatSQL($sql_check,$args_check);
foreach($data as $i => $row)
{
$date_to_check = explode('-',$row['DD']);
$date_to_check[2]."-".$date_to_check[1]."-".$date_to_check[0];
if($date_to_check[2]<10)
{
$day_without_zero = substr($date_to_check[2], 1);
}
else
{
$day_without_zero = $date_to_check[2];
}
$day_without_zero = $day_without_zero-1;
////Check if other packages have allotments
$sql_other_packages = "SELECT id FROM packages WHERE room_id=:room_id AND id<>:id";
$args_other_packages = array('room_id'=>$this_room_id,'package_id'=>$this_package);
$other_packages = DB::Load()->Execute($sql_other_packages,$args_other_packages)->returnArray();
foreach($other_packages as $values=> $other_packages_row)
{
//Check allotments table for this package
$sql_other_packages_all = "SELECT id FROM allotments_new WHERE package_id=:package_id AND day_date=:day_date";
$args_other_packages_all = array('day_date'=>$row['DD'],'package_id'=>$other_packages_row['id']);
$other_packages_all = DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnNumAffectedRows();
//If exists
if($other_packages_all==1)
{
$this_other_package_id = DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnArray();
$sql_other_packages_all_update = "UPDATE allotments_new SET number_of_rooms=:number_of_rooms,
status=:status WHERE day_date=:day_date AND package_id=:package_id ";
if($the_month[$day_without_zero]>0)
{
$status='b';
}
else
{
$status = 'r';
}
Posts: 1,676
Time spent in forums: 2 Weeks 5 Days 17 h 46 m
Reputation Power: 71
The issue here is:
By looking at this you can see that there are approximately 365 posted values by the user and each of these values can be different from each other, so update on every row is compulsory and it is not an option.
I thought I could use something like this query, avoid using 'annual_calendar table' and somehow do a select update, but I can't as the post values vary for each day!
PHP Code:
$upodate = "SELECT day_date
, number_of_rooms
, day_date
, day_of_the_week
FROM
allotments_new
WHERE day_date
BETWEEN
(
SELECT CONCAT( YEAR( CURDATE( ) ) , '-01-01' )
)
AND
(
CONCAT( YEAR( CURDATE( ) ) +1, '-01-01' )
)
AND package_id=$_POST['package_id']
ORDER BY `allotments_new`.`day_date` ASC";
So I wonder if loop in inevitable in this case! I am going to post a similar thing in mysql forum maybe I hear from Rudy on this.
These are the posted values after submitting the form:
Posts: 9,917
Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 25 m 59 sec
Reputation Power: 6113
But are they different from what you already had? that was my point. If the user opens this page and changes one day, you do this loop and all its queries 365 times.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
