Hi all-

I have a reasonably large and complex FMP 8 voter database, and each
person's address info has a 1-character tag associated with it denoting
one of 4 "active" states. However, searching on this field (by itself
or as part of a more complex request) makes the search take ages to
complete -- often over a minute to find somewhere between 8,000 and
200,000 of some 300K records.

Indexing is on, and my early stabs and finding efficiency such as
requiring each field to be a member of a value list and removing that
field to a table of its own (with only fields VoterID and ActiveStatus)
haven't yielded any real benefits. Other searches (like city code, or
name) return their results almost immediately. Any thoughts/ideas?

scv-matt <matt.waggner@gmail.com> wrote:

> Indexing is on, and my early stabs and finding efficiency such as
> requiring each field to be a member of a value list and removing that
> field to a table of its own (with only fields VoterID and ActiveStatus)
> haven't yielded any real benefits. Other searches (like city code, or
> name) return their results almost immediately. Any thoughts/ideas?

The field isn't a calculation field by coincidence, is it? If so, see if
you get a considerable speedup by using a different field, fill that
with a replace-by calculation of the problematic field.


ard

Nope. Just 1 character stored per record.

I guess there is something weird with that field.
So you could create a new empty field, and script a loop to copy the 'old' 
into the 'new' - or even better rebuild the contents with the same logic 
that was used to fill up the first one, not by calculation, but by script, 
eventually with a 2-steps 'Set Field', one to a global, the next from that 
global to 'new' -.
You may even make several trials with different techniques, like creating a 
new table just for that (and an ID). With that many records it will take a 
while (make it run at night ?), but it could well solve the problem.
Don't ask me why, but I did that occasionally and it worked (sometimes).
Remi-Noel

"scv-matt" <matt.waggner@gmail.com> a �crit dans le message de news: 
1143656990.489138.261700@t31g2000cwb.googlegroups.com...
> Hi all-
>
> I have a reasonably large and complex FMP 8 voter database, and each
> person's address info has a 1-character tag associated with it denoting
> one of 4 "active" states. However, searching on this field (by itself
> or as part of a more complex request) makes the search take ages to
> complete -- often over a minute to find somewhere between 8,000 and
> 200,000 of some 300K records.
>
> Indexing is on, and my early stabs and finding efficiency such as
> requiring each field to be a member of a value list and removing that
> field to a table of its own (with only fields VoterID and ActiveStatus)
> haven't yielded any real benefits. Other searches (like city code, or
> name) return their results almost immediately. Any thoughts/ideas?
> 

scv-matt <matt.waggner@gmail.com> wrote:

> Nope. Just 1 character stored per record.

Made me wonder if extending the field with a unique (or at least a less
common) value would improve search times. E.g.
searchfield=concat(problemfield,recordID)
Indexing of a field with one character which can be one out of four
wouldn't deliver much indexing. 
Especially, if like yourself, one knows that searching is all but slow
even on larger databases one wonders how things can be sped up.

I've had cases of index corruption. Sometime it would take a long time 
for it to search and others it would tell me it didn’t find any records 
matching my criteria. Try turning the index back to none and leave auto 
indexing on. Then try the search again. This will force the system to 
create a new index. If this doesn’t work at least you have eliminated 
it as the problem.

Sean

On 2006-03-29 12:29:50 -0600, "scv-matt" <matt.waggner@gmail.com> said:

> Hi all-
> 
> I have a reasonably large and complex FMP 8 voter database, and each
> person's address info has a 1-character tag associated with it denoting
> one of 4 "active" states. However, searching on this field (by itself
> or as part of a more complex request) makes the search take ages to
> complete -- often over a minute to find somewhere between 8,000 and
> 200,000 of some 300K records.
> 
> Indexing is on, and my early stabs and finding efficiency such as
> requiring each field to be a member of a value list and removing that
> field to a table of its own (with only fields VoterID and ActiveStatus)
> haven't yielded any real benefits. Other searches (like city code, or
> name) return their results almost immediately. Any thoughts/ideas?

Before you mess with the indices (and Sean's advice makes sense, but can be 
a pain to implement), make certain you have upgraded to v3 (free download 
from the FMI site, but you must first download and install v2, which is 
big), which dealt with those areas.

-- 
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7


"Sean Walsh" <walshsr@yahoo.com> wrote in message 
news:2006082919204575249-walshsr@yahoocom...
> I've had cases of index corruption. Sometime it would take a long time for 
> it to search and others it would tell me it didn't find any records 
> matching my criteria. Try turning the index back to none and leave auto 
> indexing on. Then try the search again. This will force the system to 
> create a new index. If this doesn't work at least you have eliminated it 
> as the problem.
>
> Sean
>
> On 2006-03-29 12:29:50 -0600, "scv-matt" <matt.waggner@gmail.com> said:
>
>> Hi all-
>>
>> I have a reasonably large and complex FMP 8 voter database, and each
>> person's address info has a 1-character tag associated with it denoting
>> one of 4 "active" states. However, searching on this field (by itself
>> or as part of a more complex request) makes the search take ages to
>> complete -- often over a minute to find somewhere between 8,000 and
>> 200,000 of some 300K records.
>>
>> Indexing is on, and my early stabs and finding efficiency such as
>> requiring each field to be a member of a value list and removing that
>> field to a table of its own (with only fields VoterID and ActiveStatus)
>> haven't yielded any real benefits. Other searches (like city code, or
>> name) return their results almost immediately. Any thoughts/ideas?
>
>