php-general Digest 24 Nov 2007 16:38:40 -0000 Issue 5145
php-general Digest 24 Nov 2007 16:38:40 - Issue 5145
Topics (messages 264987 through 264997):
Performance question for table updating
264987 by: Jon Westcot
264988 by: Andrés Robinet
Re: File handling and different character sets
264989 by: Andrés Robinet
Re: Performance question for table updating (SOLVED)
264990 by: Jon Westcot
264995 by: Bastien Koert
Re: quicktime new window php
264991 by: Bill Guion
264992 by: kNish
264993 by: Jochem Maas
264994 by: Luca Paolella
Re: Basic question - PHP usage of SVG files [SOLVED]
264996 by: tedd
Re: Basic question - PHP usage of SVG files
264997 by: Al
Administrivia:
To subscribe to the digest, e-mail:
[EMAIL PROTECTED]
To unsubscribe from the digest, e-mail:
[EMAIL PROTECTED]
To post to the list, e-mail:
[EMAIL PROTECTED]
--
---BeginMessage---
Hi all:
For those who've been following the saga, I'm working on an application
that needs to load a data file consisting of approximately 29,000 to 35,000
records in it (and not short ones, either) into several tables. I'm using
MySQL as the database.
I've noticed a really horrible performance difference between INSERTing
rows into the table and UPDATEing rows with new data when they already exist in
the table. For example, when I first start with an empty table, the
application inserts around 29,600 records in something less than 6 minutes.
But, when I use the second file, updating that same table takes over 90 minutes.
Here's my question: I had assumed -- probably wrongly -- that it would be
far more expedient to only update rows where data had actually changed;
moreover, that I should only update the changed fields in the particular rows.
This involves a large number of if statements, i.e.,
if($old_row[field_a] !== $new_row[field_66] {
$update_query .= field_a = ' .
mysql_real_escape_string($new_row[field_66]) . ',;
}
Eventually, I wind up with a query similar to:
UPDATE table_01 SET field_a = 'New value here', updated=CURDATE() WHERE
primary_key=12345
I thought that, to keep the table updating to a minimum, this approach made
the most sense. However, seeing the two hugely different performance times has
made me question whether or not it would be faster to simply update every field
in the table and eliminate all of these test conditions.
And, before someone comments that indexes on the table can cause
performance hits, I DROP nearly all of the indexes at the start of the
processing, only keeping those indexes necessary to do the original INSERT or
the subsequent UPDATE, and then add all of the extra steroid indexes (you
know -- the performance-enhancing ones g) after all of the INSERTs and
UPDATEs have been finished.
So, long story short (oops -- too late!), what's the concensus among the
learned assembly here? Is it faster to just UPDATE the record if it already
exists regardless of the fact that maybe only one or two out of 75 or more
fields changed versus testing each one of those 75 fields to try and figure out
which ones actually changed and then only update those?
I look forward to reading all of your thoughts.
Sincerely,
Jon
---End Message---
---BeginMessage---
-Original Message-
From: Jon Westcot [mailto:[EMAIL PROTECTED]
Sent: Saturday, November 24, 2007 4:32 AM
To: PHP General
Subject: [PHP] Performance question for table updating
Hi all:
For those who've been following the saga, I'm working on an
application that needs to load a data file consisting of approximately
29,000 to 35,000 records in it (and not short ones, either) into
several tables. I'm using MySQL as the database.
I've noticed a really horrible performance difference between
INSERTing rows into the table and UPDATEing rows with new data when
they already exist in the table. For example, when I first start with
an empty table, the application inserts around 29,600 records in
something less than 6 minutes. But, when I use the second file,
updating that same table takes over 90 minutes.
Here's my question: I had assumed -- probably wrongly -- that it
would be far more expedient to only update rows where data had actually
changed; moreover, that I should only update the changed fields in the
particular rows. This involves a large number of if statements, i.e.,
if($old_row[field_a] !== $new_row[field_66] {
$update_query .= field_a = ' .
mysql_real_escape_string($new_row[field_66]) . ',;
}
Eventually, I wind up with a query similar to:
UPDATE table_01 SET field_a = 'New value here',
updated=CURDATE() WHERE primary_key=12345
I thought that, to keep the table updating to a minimum, this
approach made the most sense. However, seeing the two hugely
php-general Digest 25 Nov 2007 07:01:30 -0000 Issue 5146
php-general Digest 25 Nov 2007 07:01:30 - Issue 5146
Topics (messages 264998 through 265002):
Re: Performance question for table updating (SOLVED)
264998 by: Robert Cummings
URL Parsing...
264999 by: Amanda Loucks
265000 by: tedd
265001 by: Jochem Maas
Adv. photo scripts
265002 by: PHP-General
Administrivia:
To subscribe to the digest, e-mail:
[EMAIL PROTECTED]
To unsubscribe from the digest, e-mail:
[EMAIL PROTECTED]
To post to the list, e-mail:
[EMAIL PROTECTED]
--
---BeginMessage---
On Sat, 2007-11-24 at 04:03 -0700, Jon Westcot wrote:
Moral of the story? Two, really. First, ensure you always reference
values in the way most appropriate for their type. Second, don't make your
idiocy public by asking stupid questions on a public forum. g What's the
quote (probably attributed to Churchill)? It is better to be ignorant and
silent than to voice one's opinions and remove all doubt. ;)
I don't think that phrase applies to your situation... I'd say you did
the right thing. Far too often people are afraid of reprisal or seeming
stupid and so a problem doesn't get solved until it's far too late and
its effects become magnified. Here's a more apt quote for your
situation:
It's better to be an idiot for 10 minutes than a failure for
the rest of your life.
-- Me :)
Cheers,
Rob.
--
...
SwarmBuy.com - http://www.swarmbuy.com
Leveraging the buying power of the masses!
...
---End Message---
---BeginMessage---
Hi,
I'm working on redesigning the backend of the website for work. It was
originally done in ColdFusion, but I'm switching it over to PHP - probably
going to transfer the website from where it's currently hosted to something
a lot cheaper, too, hence the switching to PHP. Anyway.
Because we are a manufacturing company, we have a few different lines of
products. Currently, each different product line has it's own page (and own
meta tags). The current set up has ColdFusion grabbing the current page
from the URL stripping off the '.cfm' extension and adding '-meta.cfm'
before including it in the header.
I'm sure there is a way to do this in PHP, but I'm out of shape enough with
using PHP that I can't remember, and I can't seem to find anything that will
work for me. I just want to be able to pull whatever address is in the URL,
get the file name, and go from there.
Any ideas?
Anything would be helpful. :)
Thanks,
Amanda
---End Message---
---BeginMessage---
At 12:18 PM -0600 11/24/07, Amanda Loucks wrote:
Hi,
I'm working on redesigning the backend of the website for work. It was
originally done in ColdFusion, but I'm switching it over to PHP - probably
going to transfer the website from where it's currently hosted to something
a lot cheaper, too, hence the switching to PHP. Anyway.
Because we are a manufacturing company, we have a few different lines of
products. Currently, each different product line has it's own page (and own
meta tags). The current set up has ColdFusion grabbing the current page
from the URL stripping off the '.cfm' extension and adding '-meta.cfm'
before including it in the header.
I'm sure there is a way to do this in PHP, but I'm out of shape enough with
using PHP that I can't remember, and I can't seem to find anything that will
work for me. I just want to be able to pull whatever address is in the URL,
get the file name, and go from there.
Any ideas?
Anything would be helpful. :)
Thanks,
Amanda
From what I've read recently about meta tags, why?
Most SE's have dropped their dependance on meta tags because of their abuse.
Cheers,
tedd
--
---
http://sperling.com http://ancientstones.com http://earthstones.com
---End Message---
---BeginMessage---
Amanda Loucks wrote:
Hi,
I'm working on redesigning the backend of the website for work. It was
originally done in ColdFusion, but I'm switching it over to PHP - probably
going to transfer the website from where it's currently hosted to something
a lot cheaper, too, hence the switching to PHP. Anyway.
Because we are a manufacturing company, we have a few different lines of
products. Currently, each different product line has it's own page (and own
meta tags). The current set up has ColdFusion grabbing the current page
from the URL stripping off the '.cfm' extension and adding '-meta.cfm'
before including it in the header.
I'm sure there is a way to do this in PHP, but I'm out of shape enough with
using PHP that I can't remember, and I can't seem to find anything that will
work for me. I just want to be able to pull whatever address is in the URL,
get the file name, and go from there.
Any ideas?
one of these should give you something to go on:
echo preg_replace('\.cfm$', '-meta.cfm',
