Re: [SLUG] Outputing progress counters with PHP/HTML
On Wed, April 30, 2008 17:25, Matthew Hannigan wrote: You do a select before every insert?! Is the table indexed? If not that might explain the slowdown; a select WILL take longer the bigger the table. Perhaps better to put a unique index on the appropriate column(s), then just do an insert and throw away the error if the data is already there. Yes, even better! I was going to ask Howard whether it mattered whether the data was different or whether it just had to be one of them. ...and the answer is that there just as to be one unique entry. If duplicates occur then it is because someone in inputting a data file that has already been processed. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html -- Howard LANNet Computing Associates http://lannet.com.au When you want a computer system that works, just choose Linux; When you want a computer system that works, just, choose Microsoft. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Outputing progress counters with PHP/HTML
You do a select before every insert?! Is the table indexed? If not that might explain the slowdown; a select WILL take longer the bigger the table. Perhaps better to put a unique index on the appropriate column(s), then just do an insert and throw away the error if the data is already there. Yes, even better! I was going to ask Howard whether it mattered whether the data was different or whether it just had to be one of them. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Outputing progress counters with PHP/HTML
I'm sorry, I should have been more specific on this. It's PHP5 and the input is a text file from a data logger which means that I have to find the data records then decompose each into the individual data elements to insert into the database, so it's not just a case of a simple .SQL file as input. The process is: Read a line from the file. Decompose the line into the data elements. For each data element, do a select on the database to see whether it already exists, if not then do an insert into the database. Rinse and repeat... It certainly has the smell of being a PHP memory leak, but how I can work around it I am just not sure. On Tue, April 29, 2008 15:45, Howard Lowndes wrote: I have a need to output a progress counter from a PHP script that takes a while to run whilst writing a large number of records out to an SQL database, mainly so that the user knows that things are still happening and not hung. It seems a simple thing to do, but when I try it, the progress counter (say, every 100 records) instead of being output at the correct time, gets delayed until the whole process has finished. What is the best way to get around this problem. -- Howard LANNet Computing Associates http://lannet.com.au When you want a computer system that works, just choose Linux; When you want a computer system that works, just, choose Microsoft. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html -- Howard LANNet Computing Associates http://lannet.com.au When you want a computer system that works, just choose Linux; When you want a computer system that works, just, choose Microsoft. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Outputing progress counters with PHP/HTML
On Wed, Apr 30, 2008 at 12:08:50PM +1000, Howard Lowndes wrote: I'm sorry, I should have been more specific on this. It's PHP5 and the input is a text file from a data logger which means that I have to find the data records then decompose each into the individual data elements to insert into the database, so it's not just a case of a simple .SQL file as input. The process is: Read a line from the file. Decompose the line into the data elements. For each data element, do a select on the database to see whether it ^^^ already exists, if not then do an insert into the database. Rinse and repeat... It certainly has the smell of being a PHP memory leak, but how I can work around it I am just not sure. You do a select before every insert?! Is the table indexed? If not that might explain the slowdown; a select WILL take longer the bigger the table. Matt -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Outputing progress counters with PHP/HTML
On Wed, 2008-04-30 at 12:30 +1000, Matthew Hannigan wrote: You do a select before every insert?! Is the table indexed? If not that might explain the slowdown; a select WILL take longer the bigger the table. Also when adding 100K records the stats for the table could become incorrect quite rapidly; this can lead to bad query plans even on an indexed table. But I'm guessing this is being done in a single transaction which is not nice to the database :P -Rob -- GPG key available at: http://www.robertcollins.net/keys.txt. signature.asc Description: This is a digitally signed message part -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Outputing progress counters with PHP/HTML
On Wed, 2008-04-30 at 12:30 +1000, Matthew Hannigan wrote: On Wed, Apr 30, 2008 at 12:08:50PM +1000, Howard Lowndes wrote: The process is: Read a line from the file. Decompose the line into the data elements. For each data element, do a select on the database to see whether it ^^^ already exists, if not then do an insert into the database. Rinse and repeat... It certainly has the smell of being a PHP memory leak, but how I can work around it I am just not sure. You do a select before every insert?! Is the table indexed? If not that might explain the slowdown; a select WILL take longer the bigger the table. Perhaps better to put a unique index on the appropriate column(s), then just do an insert and throw away the error if the data is already there. -- Thanks, . Sonia Hamilton http://www.snowfrog.net http://training.snowfrog.net http://www.linkedin.com/in/soniahamilton . Your manuscript is both good and original; but the part that is good is not original and the part that is original is not good - Samuel Johnson. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
[SLUG] Outputing progress counters with PHP/HTML
I have a need to output a progress counter from a PHP script that takes a while to run whilst writing a large number of records out to an SQL database, mainly so that the user knows that things are still happening and not hung. It seems a simple thing to do, but when I try it, the progress counter (say, every 100 records) instead of being output at the correct time, gets delayed until the whole process has finished. What is the best way to get around this problem. -- Howard LANNet Computing Associates http://lannet.com.au When you want a computer system that works, just choose Linux; When you want a computer system that works, just, choose Microsoft. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Outputing progress counters with PHP/HTML
quote who=Howard Lowndes I have a need to output a progress counter from a PHP script that takes a while to run whilst writing a large number of records out to an SQL database, mainly so that the user knows that things are still happening and not hung. It seems a simple thing to do, but when I try it, the progress counter (say, every 100 records) instead of being output at the correct time, gets delayed until the whole process has finished. What is the best way to get around this problem. flush() liberally. :-) You might want to do it every loop, or every five or so depending on what kind of performance:visibility ratio you're aiming for. - Jeff -- GUADEC 2008: Istanbul, Turkey http://www.guadec.org/ No match for LINUSWEARSTHEPANTS.ORG. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Outputing progress counters with PHP/HTML
On Tue, Apr 29, 2008 at 3:45 PM, Howard Lowndes [EMAIL PROTECTED] wrote: I have a need to output a progress counter from a PHP script that takes a while to run whilst writing a large number of records out to an SQL database, mainly so that the user knows that things are still happening and not hung. It seems a simple thing to do, but when I try it, the progress counter (say, every 100 records) instead of being output at the correct time, gets delayed until the whole process has finished. What is the best way to get around this problem. not necessarily the *best* way: ?php $i = 0; do { print hai tharbr /; flush(); sleep(1); $i++; } while ($i 15); ? -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html