Hi,
My client has a product database od around 20000 items. And it is
updated every few days when he gets a catalog from the supplier.
It's supposed to work like this: if there already is a product in the
database with that ID (primary key), just UPDATE the price, and if
there is no product with that ID it should do an INSERT for that item.
Now I've tried it in several ways and I can't seem to get it to execute
in under 20 seconds (max execution time is 30s, so just to be sure).
I've tried doing an update for every item (UPDATE product SET
price=$price where ID=$id) and if I get a mysql_affected_ rows()==0 then
I do an insert. This takes too long for 20000 items.
I've also tried collecting all IDs in one array and then doing an
in_array() check for every ID value, and if it exists in the array I do
an update in MySQL, and if not I do an insert. This also takes too long
(although less than the first method).
Of course I can have the script redirect to itself after 20 seconds and
continue from where it left off, but this seems kind of lame.
Since the client wants to enter extra data for products and not lose it
when he does a price update, I can't just replace everything on every
update, just the price.
Changing the hosting is out of the question (he paid 2 years in advance
and has like 100000 emails set up in the control panel).
I'm sorry if this does not belong in comp.lang.php, sorry to take your
time.
TIA
6  2236  a-**@consultant.c om wrote: My client has a product database od around 20000 items. And it is
updated every few days when he gets a catalog from the supplier.
It's supposed to work like this: if there already is a product in the
database with that ID (primary key), just UPDATE the price, and if
there is no product with that ID it should do an INSERT for that item.
Now I've tried it in several ways and I can't seem to get it to
execute in under 20 seconds (max execution time is 30s, so just to be
sure).
I've tried doing an update for every item (UPDATE product SET
price=$price where ID=$id) and if I get a mysql_affected_ rows()==0
then I do an insert. This takes too long for 20000 items.
I've also tried collecting all IDs in one array and then doing an
in_array() check for every ID value, and if it exists in the array I
do an update in MySQL, and if not I do an insert. This also takes too
long (although less than the first method).
Of course I can have the script redirect to itself after 20 seconds
and continue from where it left off, but this seems kind of lame.
Since the client wants to enter extra data for products and not lose
it when he does a price update, I can't just replace everything on
every update, just the price.
Changing the hosting is out of the question (he paid 2 years in
advance and has like 100000 emails set up in the control panel).
Try using set_time_limit( 0) to see if that allows you to have an
unlimited execustion time. Although the default maximum execution time
is 30 seconds, not all hosts prevent you from extending it.
Read the manual page for more info: http://www.php.net/set_time_limit
--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
On 18 Dec 2004 11:57:46 -0800, a-**@consultant.c om wrote: My client has a product database od around 20000 items. And it is
updated every few days when he gets a catalog from the supplier.
It's supposed to work like this: if there already is a product in the
database with that ID (primary key), just UPDATE the price, and if
there is no product with that ID it should do an INSERT for that item.
Now I've tried it in several ways and I can't seem to get it to execute
in under 20 seconds (max execution time is 30s, so just to be sure).
I've tried doing an update for every item (UPDATE product SET
price=$price where ID=$id) and if I get a mysql_affected_ rows()==0 then
I do an insert. This takes too long for 20000 items.
Is there an index on the ID column?
I just knocked up a quick test to see how long it takes here; created a table
as follows:
mysql> desc product;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| product_name | varchar(255) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
Filled it with 20000 rows, then did a PHP loop to update the price on them
all. This is on a creakingly ancient 200MHz machine, it took 38 seconds. I'd
hope your server has a little more horsepower than this very old machine.
I then dropped the primary key (primary keys are automatically backed by
indexes) and repeated the test. As expected it took an enormously larger amount
of time; actually I gave up waiting after several minutes of MySQL stuck at
100% CPU usage.
I've also tried collecting all IDs in one array and then doing an
in_array() check for every ID value, and if it exists in the array I do
an update in MySQL, and if not I do an insert. This also takes too long
(although less than the first method).
Of course I can have the script redirect to itself after 20 seconds and
continue from where it left off, but this seems kind of lame.
Since the client wants to enter extra data for products and not lose it
when he does a price update, I can't just replace everything on every
update, just the price.
Changing the hosting is out of the question (he paid 2 years in advance
and has like 100000 emails set up in the control panel).
I'm sorry if this does not belong in comp.lang.php, sorry to take your
time.
Your question is OK here; I believe there's a comp.databases. mysql newsgroup
in the process of being created, but since you're using PHP to do this, it's
on-topic here according to the charter. Perhaps when the MySQL group is created
similar questions could belong there but it's fine for now.
--
Andy Hassall / <an**@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool
What version of MySQL is your client on?
After 4.1 you can do "ON DUPLICATE KEY UPDATE", which first tries to insert,
then updates if the record exists.
You can also do multiple values sets like this:
INSERT INTO table (ID,price) VALUES (1000,4.95),(10 01,5.95),etc... ON
DUPLICATE KEY UPDATE price=VALUES(pr ice);
Even if the client isn't on a recent MySQL version, you can still do
multiple VALUES sets in a single query. So you could INSERT all new values
in a single statement (be sure to check MAX_ALLOWED_PAC KET size to make sure
your query isn't too long). You must check that all your inserts are unique
first (which I believe you could do with a single SELECT statement).
- Kevin
<a-**@consultant.c om> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. . Hi,
My client has a product database od around 20000 items. And it is
updated every few days when he gets a catalog from the supplier.
It's supposed to work like this: if there already is a product in the
database with that ID (primary key), just UPDATE the price, and if
there is no product with that ID it should do an INSERT for that item.
Now I've tried it in several ways and I can't seem to get it to execute
in under 20 seconds (max execution time is 30s, so just to be sure).
I've tried doing an update for every item (UPDATE product SET
price=$price where ID=$id) and if I get a mysql_affected_ rows()==0 then
I do an insert. This takes too long for 20000 items.
I've also tried collecting all IDs in one array and then doing an
in_array() check for every ID value, and if it exists in the array I do
an update in MySQL, and if not I do an insert. This also takes too long
(although less than the first method).
Of course I can have the script redirect to itself after 20 seconds and
continue from where it left off, but this seems kind of lame.
Since the client wants to enter extra data for products and not lose it
when he does a price update, I can't just replace everything on every
update, just the price.
Changing the hosting is out of the question (he paid 2 years in advance
and has like 100000 emails set up in the control panel).
I'm sorry if this does not belong in comp.lang.php, sorry to take your
time.
TIA
How about using REPLACE INTO instead of INSERT INTO. This will work if the
records currently exist in the database or not.
--
Tony Marston http://www.tonymarston.net
"Kevin" <ke***@wxREMOVE 4SPAM3.com> wrote in message
news:BO0xd.2110 04$5K2.162710@a ttbi_s03... What version of MySQL is your client on?
After 4.1 you can do "ON DUPLICATE KEY UPDATE", which first tries to
insert,
then updates if the record exists.
You can also do multiple values sets like this:
INSERT INTO table (ID,price) VALUES (1000,4.95),(10 01,5.95),etc... ON
DUPLICATE KEY UPDATE price=VALUES(pr ice);
Even if the client isn't on a recent MySQL version, you can still do
multiple VALUES sets in a single query. So you could INSERT all new
values
in a single statement (be sure to check MAX_ALLOWED_PAC KET size to make
sure
your query isn't too long). You must check that all your inserts are
unique
first (which I believe you could do with a single SELECT statement).
- Kevin
<a-**@consultant.c om> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. . Hi,
My client has a product database od around 20000 items. And it is
updated every few days when he gets a catalog from the supplier.
It's supposed to work like this: if there already is a product in the
database with that ID (primary key), just UPDATE the price, and if
there is no product with that ID it should do an INSERT for that item.
Now I've tried it in several ways and I can't seem to get it to execute
in under 20 seconds (max execution time is 30s, so just to be sure).
I've tried doing an update for every item (UPDATE product SET
price=$price where ID=$id) and if I get a mysql_affected_ rows()==0 then
I do an insert. This takes too long for 20000 items.
I've also tried collecting all IDs in one array and then doing an
in_array() check for every ID value, and if it exists in the array I do
an update in MySQL, and if not I do an insert. This also takes too long
(although less than the first method).
Of course I can have the script redirect to itself after 20 seconds and
continue from where it left off, but this seems kind of lame.
Since the client wants to enter extra data for products and not lose it
when he does a price update, I can't just replace everything on every
update, just the price.
Changing the hosting is out of the question (he paid 2 years in advance
and has like 100000 emails set up in the control panel).
I'm sorry if this does not belong in comp.lang.php, sorry to take your
time.
TIA
Tony Marston wrote: How about using REPLACE INTO instead of INSERT INTO. This will work if the
records currently exist in the database or not.
Not quite, since REPLACE first deletes the existing row, before inserting
the new, and as he said, he didn't want to delete the existing data, only
update parts of the row.
--
Tommy
Sorry if this appears twice, I posted this yesterday and it didn't show up:
What version of MySQL is your client on?
After 4.1 you can do "ON DUPLICATE KEY UPDATE", which first tries to insert,
then updates if the record exists.
You can also do multiple values sets like this:
INSERT INTO table (ID,price) VALUES (1000,4.95),(10 01,5.95),etc... ON
DUPLICATE KEY UPDATE price=VALUES(pr ice);
Even if the client isn't on a recent MySQL version, you can still do
multiple VALUES sets in a single query. So you could INSERT all new values
in a single statement (be sure to check MAX_ALLOWED_PAC KET size to make sure
your query isn't too long). You must check that all your inserts are unique
first (which you could do with a single SELECT * WHERE ID IN () statement).
- Kevin
<a-**@consultant.c om> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. . Hi,
My client has a product database od around 20000 items. And it is
updated every few days when he gets a catalog from the supplier.
It's supposed to work like this: if there already is a product in the
database with that ID (primary key), just UPDATE the price, and if
there is no product with that ID it should do an INSERT for that item.
Now I've tried it in several ways and I can't seem to get it to execute
in under 20 seconds (max execution time is 30s, so just to be sure).
I've tried doing an update for every item (UPDATE product SET
price=$price where ID=$id) and if I get a mysql_affected_ rows()==0 then
I do an insert. This takes too long for 20000 items.
I've also tried collecting all IDs in one array and then doing an
in_array() check for every ID value, and if it exists in the array I do
an update in MySQL, and if not I do an insert. This also takes too long
(although less than the first method).
Of course I can have the script redirect to itself after 20 seconds and
continue from where it left off, but this seems kind of lame.
Since the client wants to enter extra data for products and not lose it
when he does a price update, I can't just replace everything on every
update, just the price.
Changing the hosting is out of the question (he paid 2 years in advance
and has like 100000 emails set up in the control panel).
I'm sorry if this does not belong in comp.lang.php, sorry to take your
time.
TIA This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sims |
last post by:
Hi,
I know this is slightly OT but before i can get to the programming part i
want to make sure that i have the structure covered.
I have a 2D map and items in my list/vector will be lines on the map, with a
start and finish point within the map, so an item would have a start pos
S(x,y) and an end pos E(x,y).
so my structure would be something like
|
by: karinski |
last post by:
Hi All,
I have a split f/e - b/e setup on my database with RWOP queries on the
front end. The code below gets a PO number(s), and vendor name from a
multi choice list box on another form. It then assigns these values,
plus a new receiving number, to the current form. The SQL string is an
append query that copies select fields from the PO line items table
into the receiving line items table and appends the current receiving
number to...
|
by: Jeremy Owens-Boggs |
last post by:
We are trying to implement a dual list box selection where you have two list
boxes, You highlight items in the right side list box, click a button and
this moves those items over to the left hand list box. The problem is that
if there are many items selected (thousands), then removing the items from
the right side list box takes for ever because we are removing them one at a
time, which causes the listbox to re-index everything before we...
|
by: Q. John Chen |
last post by:
I have Vidation Controls
First One: Simple exluce certain special characters:
say no a or b or c in the string:
*
Second One:
I required date be entered in "MM/DD/YYYY" format:
//+4 How ??
|
by: MrNobody |
last post by:
I want to have a boxed off area on my GUI which will behave essentially like
a very simple ListBox, with items stacking vertically in a list with a scroll
bar if the stacked items go beyond the bottom border.
The trick ypart is these items should be pretty large rounded -corner
rectangular panels which will contain a mix of Labels and a few simple form
elements such as TextFields, drop downs, etc.. These rounded rectangle panels
will be...
| | |
by: preethamkumark |
last post by:
- The program first creates a shared memory buffer containing an array
of 20 integers.
- Each slot of the buffer can have either 0 or 1, where 0 represents an
empty slot, and 1
represents an occupied one.
- Initially, the buffer is empty. Thus, all the slots are initialized
with 0.
|
by: mike |
last post by:
I help manage a large web site, one that has over
600 html pages... It's a reference site for ham radio
folks and as an example, one page indexes over
1.8 gb of on-line PDF documents.
The site is structured as an upside-down tree, and
(if I remember correctly) never more than 4 levels.
The site basically grew (like the creeping black blob) ...
all the pages were created in Notepad over the last
|
by: tizmagik |
last post by:
Database consists of the following 4 tables with respective
attributes:
CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is
ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is
BOUGHT(C#, I#, DATE, QUANTITY), the key is
PREFER(I#, C#), the key is
I'm trying to construct the following query (in SQL)
|
by: alivip |
last post by:
Is python provide search in parent folder contain sub folders and files
for example folder name is cars and sub file is Toyota,Honda and BMW and Toyota contain file name camry and file name corola, file name honda contain folder accord and BMW contain file name X5
Is there way to enter name of parent folder(cars) and search in all sub folder(Toyota,Honda and BMW) and files ?
how can I intgreat cod to be user interface (buttun ,text box...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
