Justtechjobs.com Find a programming school near you






Online Campus Both


php-db | 2005011

RE: [PHP-DB] Slow Query From: Bastien Koert (bastien_k <email protected>)
Date: 01/13/05

me thinks its time for a new machine ;-)

Bastien

>From: "Wendell Frohwein" <wendell <email protected>>
>To: "'Bastien Koert'"
><bastien_k <email protected>>,<graeme_foster <email protected>>
>CC: <php-db <email protected>>
>Subject: RE: [PHP-DB] Slow Query
>Date: Thu, 13 Jan 2005 13:21:33 -0800
>
>Thank You Bastien.
>
>My newest "Vision" that I had about taking out a step in the process has
>failed. Mainly because of the script that determines the radius. Since I
>was going to go off of the agents zipcode coverage area (to save a
>step), and not the zipcode table containing 45,000 + entry's. If an
>order was placed by a client with lets say the zip code 90606, and there
>was no agent in the entire system that had this exact zip code in there
>coverage area, the radius code would error out and return no matches.
>Even though in the 2-5 mile radius of 90606 there is matching agents. It
>needs to see that first zip codes (90606) longitude and latitude to even
>begin the radius function.
>
>So now I was bumped again. I rewrote some of my code to be a little more
>streamlined. So I began testing orders: 50, 100, 200, and 400. The time
>it takes to assign orders to agents is really had to determine because
>it is based on how many agents and how many zipcodes in those agents
>coverage area.
>
>With 400 orders within a heavy populated area it takes as much time as
>127 seconds to process. If there are fewer zip codes in an area such as
>mid east / west it takes as little time as 11 seconds to process.
>
>When I start the assignment of orders I open up a shell via ssh, monitor
>the system using the top command. Mysqld spikes between 55% and 99% cpu
>usage the entire time of the assignment process. Where it says User CPU
>usage its between 70% and 99% cpu usage. And finally where it says
>System CPU Usage, it rarely goes over 3% - 5%.
>
>The server is not mine, but one I manage for the company. I used dmesg
>to find out that the server this site is running on is a 700mhz celeron
>coppermine, 128MB of ram, 10gig western digital drive.
>
>So with this in mind, I think my problem behind slow queries is affected
>in other areas other then the script itself.
>
>
>Thank you all very much for all your help.
>
>
>-Wendell Frohwein
>
>
>
>
>-----Original Message-----
>From: Bastien Koert [mailto:bastien_k <email protected>]
>Sent: Thursday, January 13, 2005 10:04 AM
>To: wendell <email protected>; graeme_foster <email protected>
>Cc: php-db <email protected>
>Subject: RE: [PHP-DB] Slow Query
>
>Hi Wendell,
>
>What you've proposed is not a bad solution. There is some initial work
>to
>set up the stuff for the existing agents, but if you define a limit to
>the
>range that the agent works in, then you can take the result stuff it
>into a
>comma delimited string and place that into the agent_zip_codes
>field...then
>the realtime time query is a simple IN (zip1, zip2, zipN) statement and
>should be faster
>
>Bastien
>
> >From: "Wendell Frohwein" <wendell <email protected>>
> >To: "'graeme'" <graeme_foster <email protected>>
> >CC: <php-db <email protected>>
> >Subject: RE: [PHP-DB] Slow Query
> >Date: Thu, 13 Jan 2005 00:30:34 -0800
> >
> >Thank you Graeme.
> >
> >But unfortunately, there are a 700 + agents. This number keeps growing
> >every day as well. I had a vision of another idea. I don't know how
>much
> >time it will save. In the table that stores all the agents zipcodes
>that
> >they cover, I added longitude and latitude to the table.
> >
> >So when my zip code radius function kicks in, it will find agents with
> >there respective zip code.
> >
> >Before it would take the property zip, query the zipcodes table with
> >over 42,000 zip codes, then put those in an array, then search for
> >agents from there.
> >
> >The way I see it, I killed one step in the process. But at the time of
> >me writing this letter I am still trying to get it to work. If I do, I
> >will post up some numbers to see how much faster it is.
> >
> >But I am still looking for a better way to do it.
> >
> >Thanks once again to everyone.
> >
> >-Wendell Frohwein
> >
> >-----Original Message-----
> >From: graeme [mailto:graeme_foster <email protected>]
> >Sent: Thursday, January 13, 2005 12:02 AM
> >To: Wendell Frohwein
> >Cc: php-db <email protected>
> >Subject: Re: [PHP-DB] Slow Query
> >
> >How many agents do you have?
> >
> >If the number of agents is small then you could set up a temp table
> >which has a link to the agent, the client, and a calculation of the
> >distance between the two. Then do your search on this temp table. Once
> >you have the answer you want delete the records for this client. (that
> >should allow for more than one client entering data at the same time,
> >although you may want to properly think that scenario through)
> >
> >graeme.
> >
> >
> >Wendell Frohwein wrote:
> >
> > >First of all I would like to thank anyone who lends a hand in this
> > >matter. Here is what im working with. Redhat 9, PHP 5.0.2,
> > >Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for
> > >pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with
> > >mysql, and mysqli capability.
> > >
> > >This is how the script works:
> > >
> > >The client submits a property for an appraisal.
> > >Based on the appraisal type and property zip code, It searches the
> > >database for agents / appraisers with the zip code in question
> >(property
> > >zip code) in there coverage area (zip codes they are willing to do
> > >appraisal work for). So when it finds a match, It adds that order to
> > >there potential order list. It then waits for the to accept or
>decline
> > >the order. If accepted the order becomes there's and alerts all the
> > >other agents / appraisers that this order has already been accepted.
> >The
> > >do the work, send it back job is done.
> > >
> > >This works fine right now rather it be a single order placed by a
> > >client, or a spreadsheet imported to the system with 2000 orders or
> > >more. So now you're saying if it works, why are you here asking for
> > >help? Well instead of searching agents with the exact zip code match,
>I
> > >would like to search a radius of zip codes. The first search would be
>a
> > >2 mile radius, the next search (incase the first did not return any
> > >result) would be 5 mile radius.
> > >
> > >I purchased some software (php / mysql sql files) that contains every
> > >zipcode in the united states along with a longitude and latitude for
> > >each zip code. So the zip code script provided with this software
> >allows
> > >you to 1) enter a zip code and miles in radius, it will then spit out
> > >all the zip codes in that radius into an array. 2) you can give it 2
> >zip
> > >codes and it will tell you the distance in miles between the 2 (give
>or
> > >take).
> > >
> > >So I rewrote my script to do the follow:
> > >The client submits a property for an appraisal.
> > >Based on the appraisal type and property zip code, It does a radius
> > >search of 2 miles from the property zip. If no matches are found, it
> > >does a 5 mile radius search. For every zip code returned by each
>radius
> > >search, It searches the database for agents / appraisers with the zip
> > >code in question (zip codes from radius search) in there coverage.
> > >
> > >This is the most stressful part I am assuming. This works fine for 1
>-
> > >10 orders. But when I import 30 or more, the script pretty much hangs
> >as
> > >well as mysql. I have to kill mysql with signal 9, start mysql it up
> > >again. Then all is back to normal. I would paste the code in here but
> >it
> > >is really long and complicated.
> > >
> > >I was hoping on the based on the operation of the script, someone
>would
> > >suggest a better and faster way to search zip code radius, while
> > >matching the results to agents within the system.
> > >
> > >If I am asking for way to much time from someone, I apologize. If
> > >someone is really interested in helping me sort this out, I could
>send
> > >you code samples to see the process.
> > >
> > >
> > >
> > >-Wendell Frohwein
> > >
> > >
> > >
> >
> >--
> >Experience is a good teacher, but she sends in terrific bills.
> >
> >Minna Antrim
> >
> >--
> >PHP Database Mailing List (http://www.php.net/)
> >To unsubscribe, visit: http://www.php.net/unsub.php
> >
>
>
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php