fpSQL and the "index is too big" error

[Richard Veith]

The problem is that I am running a 'simple' SQL query in the fpSQL window,
and before it completes, it aborts with "Index Is Too Big.  Rebuild the
index using a smaller key."    I am running FilePro on a Windows 7 pc with
16 GB of memory (64 bit operating system).  The database that I am querying
has 23.7 million records of 369 characters each.  The query is:  

           select @5, count(*)

           from mystuff14

           group by @5 

 

where Field 5 is a two character state field.  Basically, I want to get a
list of all the US states in the database and a count per state; I intend to
then direct the output to a file.

 

But I don't know how to get around this "index too big" problem.   I have
been searching on the Internet, and I have been adjusting all the
environmental variables that I thought might be relevant ( PFMAXALLOC,
PFMAXASIZE, PFMAXTFIL, PFMAXTEMP, PFNUMIXBUF, PFNUMIX BUILD, PFBIXNODESIZE).
I have even tried setting all of them to their maximum.  No matter what I
set, it does not seem to have any impact.   For example, I set PFMAXTFIL to
numbers greater than 4, (e.g., 8, 16, 32, etc.) and in my tempxxx directory
there are always only 4 files of 15,932 KB each.   I have made these
environmental variable changes using the configuration screen within the
FilePro windowed interface, as well as in setenv.bat and fulldev.bat.  But,
as I said, nothing I do seems to have any impact on this problem.

 

Any suggestions, comments?

Thanks much,

Rich

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.celestial.com/pipermail/filepro-list/attachments/20150121/2eb0a0c0/attachment.html>