From: "Wendell Frohwein" <wendell@xxxxxxxxxxxx>
To: "'Bastien Koert'"
<bastien_k@xxxxxxxxxxx>,<graeme_foster@xxxxxxxxxxxxxxxx>
CC: <php-db@xxxxxxxxxxxxx>
Subject: RE: 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@xxxxxxxxxxx]
Sent: Thursday, January 13, 2005 10:04 AM
To: wendell@xxxxxxxxxxxx; graeme_foster@xxxxxxxxxxxxxxxx
Cc: php-db@xxxxxxxxxxxxx
Subject: RE: 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@xxxxxxxxxxxx>
>To: "'graeme'" <graeme_foster@xxxxxxxxxxxxxxxx>
>CC: <php-db@xxxxxxxxxxxxx>
>Subject: RE: 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@xxxxxxxxxxxxxxxx]
>Sent: Thursday, January 13, 2005 12:02 AM
>To: Wendell Frohwein
>Cc: php-db@xxxxxxxxxxxxx
>Subject: Re: 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
>
