Zipcode with almost 500K Records

[JamesBand]

I have purchased a Merge Table with close to 500,000 records. I have built a table and setup the relationship necessary to make lookups, calcs and portals work and it is working. However, interaction with table is SLOW. The table has

[Main DB]zip5 == [Zip DB]zip5 (5 digit zip)

[Main DB]zip4 >= [Zip DB]zip4low (4 digits/lowest in the range)

[Main DB]zip4 <= [Zip DB]zip4high (4 digits/highest in the range)

The three target fields in the Zip DB are fully indexed.

Pulling info this way is taking up to 25 secs per lookup. I am wondering if anyone sees a way to speed this up. Anyone have any ideas?

Jamie

[JamesBand]

I have simplified the Relationship to the 5 digit zip and it returns the results incredibly fast. However, when I add the extra conditions it slows back to a crawl.

I am using NUMBER fields for the zip codes... would it be faster to use TEXT fields?

Edited March 4, 2008 by Guest

[Vaughan]

Instead of using the multiple fields in the relationship, make an indexed calculation field in the Postcode file that does roughly the same thing with multi-key values. Link only to this. It may be faster.

[David Jondreau]

I'm not sure, but I think he is only using one field to compare but has 3 different relationships?

And he's using more than just the equals sign as a comparative operator, so I don't know how he'd set up the relationship.

Regardless, are all operations taking a long time, or just some (ie lookups)? If just lookups, try switching to an auto enter calc instead. Are the MainDB fields indexed (not sure if that would really matter though)? Can you eliminate the < and > and make them simply =?

Number fields would not be any slower than text fields.

[JamesBand]

I'm not sure, but I think he is only using one field to compare but has 3 different relationships?

And he's using more than just the equals sign as a comparative operator, so I don't know how he'd set up the relationship.

Regardless, are all operations taking a long time, or just some (ie lookups)? If just lookups, try switching to an auto enter calc instead. Are the MainDB fields indexed (not sure if that would really matter though)? Can you eliminate the < and > and make them simply =?

Number fields would not be any slower than text fields.

I am using 2 fields in the Main DB (Zip5 & Zip4) and 3 in the Zip DB (Zip5, Zip4Low & Zip4High.)

I see no way to concatenate the 3 from the Zip DB and it be useful in a relationship.

Jamie

[JamesBand]

For the moment, I have resigned myself to the use of a workaround.

Main_DB::Zip5 = Zip_DB::Zip5

I have used Evaluate() to Trigger a Script when the Zip field is updated.

The Script opens a new window, changes to layout to a special layout with a portal. It the loops through the records until it finds a match.

: Jamie

[bruceR]

Lookups are notoriously slow. You can get an equivalent result with an auto-enter calculation. Give that a try.

[JamesBand]

Lookups are notoriously slow. You can get an equivalent result with an auto-enter calculation. Give that a try.

I went to a calculation and it remained slow. The bottle-neck seems to be the use of <= and >= on a database of this size.

The current solution is not elegant, but it works for the moment. I am going to add a cumulative value-list calc field that will contain the range of zip, ex:

where:

field zip5 = 30303

field zip4low = 0125

field zip4high = 0132

then:

field zips would be =

303030125

303030126

303030127

303030128

303030129

303030130

303030131

303030132

Custom Function:

Zip_Range( _Zip5; _Zip4Low; _Zip4High; _Return )

If( _Zip4Low = "0001" and _Zip4High = "9999"; _Zip5 & "0000¶";



	Let( [low = GetAsNumber(_Zip4Low); high = GetAsNumber(_Zip4High)];

		Case(

			low > high; _Return;

				Zip_Range(_Zip5; low+1; high; _Return & _Zip5 & Right("0000" & GetAsText(low); 4) & "¶")

		)

	)

)

This way I can still use = in the relationship exclusively. This will hopefully keep the speed high, allowing me to drop the triggered script.

This will increase the import time, as the zips field will be an auto-enter calculation. But, I can deal with the slow import far better than I can deal with a slow relationship or a potentially problematic triggered script.

Edited March 10, 2008 by Guest

[Søren Dyhr]

The bottle-neck seems to be the use of <= and >= on a database of this size

No it's a bug with version 7 specificly, when ever possible upgrade to fm8+ until then, make the thetajoins via hashing:

http://www.onegasoft.com/tools/smartranges/index.shtml

It's a known issue, that LeCates brought up on Devcon '05 ... although clever developers discovered it a little earlier here:

http://www.clevelandconsulting.com/support/viewtopic.php?t=299&highlight=smartranges

--sd