How to speed this up

[slstrother]

I have a mail list project where I will need to append the 3 closest dealers to each recipient record. I have created a test that does what I want. The problem is the time it takes to perform the task. My test file has a little over 1,000 records in both the recipient and dealer files. The test took 99 minutes to complete. The production data will have 75,000 recipients and 1,300 dealers. Basing a time estimate on the test db, I am looking at well over 100 hours to perform this task on the production data.

I am looking for ideas, database design changes that will speed this process up. I have attached my test dbs so you can see what I have done. The recipient_list.fp5 is the main file.

Thanks in advance,

Steve

processing_test.zip

[stanley]

Steve:

I downloaded your files, but don't have the time to dig out what's happening - I notice that your "Master" script calls about a dozen scripts in the related file. What's happening there? If you could post a simple explanation of the process you're attempting to accomplish, I imagine some nifty answers will appear pretty promptly.

If I get a chance (I doubt it, but...) I'll look at your DBs again over the weekend.

-Stanley

[stanley]

Steve:

Also, after running a quick test, it seems to me that you could eliminate a lot of the run-through by using the State field (or, actually, a hybrid of the State field) to exclude most of the records. For example, if a recipient is in California, there's no point including dealers in Massachusetts in the loop. You could divide the country into, say, 10 overlapping zones, and only search within that zone. For example, New England might be a zone, but you'd include NY state (and maybe NJ as well) as an overlap into the next zone, to accomodate borderline cases. Conversely, if the next zone was something like NY, PA, NJ, OH, that zone would include all bordering states, for the same reason.

That might be a dumb idea, but it just struck me while I'm beginning to absorb what you're after.

-Stanley

[ernst]

Hi all,

With Stanleys idea as a starting point, you could create an extra database with two fields: 'state' and 'state and neighboring states'

This database would have 51 (?) records like -using Stanley's example-

state: NY

state and neighboring states:

NY

PA

NJ

and so forth...

Next step would be to add a 'state and neighboring states' field to your recipients database and have the value in that field looked up from the extra database.

Then you could define a relation from the 'state and neighboring states' field in recipients to the dealers database and use a portal to select the dealers that are closest to that recipient.

Regards,

Ernst.

[slstrother]

Stanley,

Here is what I have set up.

In the recipients db, goes to the first record, a 1 is set in the key field to establish a two way relationship to the dealers db. Then a looping script is run to calculate the distance of each record in the dealers db from the record in the recipients db. The dealers db is then sorted ascending and serial numbered. Then 3 find and set field scripts are run to append the dealer name and phone number to the recipient record. The distance and serial fields are cleared. The recipient is marked complete, the key is cleared, then goes to the next record and whole process is completed until done.

Limiting the distance calculation to the same and surrounding states as the recipient is a good idea.

Ernst, the multi key field is also a good idea, I'll look into that. The portal will not work however, once the data is appended I will need to export the data for use in our printing process.

[ernst]

hey Steve,

To jabber on a bit about the portal...

You could have one portal which shows dealers in the same state as the recipient and a second that shows dealers in the neighboring states.

In the looping script you would then copy name and phone number of the first three dealers from the same state portal, appended with dealers from the neighboring state portal if necessary.

Good luck,

Ernst.

[Fenton]

I managed to do it a little differently, setting the latitude, longitude into global fields; setting the resulting matches into a multi-line text field (which allows showing matches in a portal; seems good enough). I also optimized the loop to be as fast as I could.

All this managed to cut the time by almost half. But that's probably about it.

DealerList2.zip

[slstrother]

I believe that I have the solution. Taking Stanley's idea of limiting the dealer records to calc the distance. I made a state db with all of the state abbreviations, then made a surrounding state field containing the abbreviations of the surrounding states. The surrounding states field is used as the source of a look up in the recipients db to set the values of a multi key field. This way the distance calc is limited to the current state and surrounding states only. I also removed all of the finds, used omit record instead. After all of this, the 1,000 records processed in 1 min 10 sec.. A substantial improvement over what I had to begin with.

Thank you Stanley, Ernst and Fenton for your help.

I am attaching a copy in case anyone wants look.

processing_test_rev1.zip

[stanley]

Excellent. Glad to have been of help.

-Stanley