>>>>>I have this table with about 90,000 records. The table is about 47 mb in size.Some of the fields are Invoice number, invoice date, active flag. If I do a select on these fields as so:
>>>>>
>>>>>SELECT inv_number from INVTable WHERE YEAR(inv_date) = 1997 and REC_active into a cursor/table/array
>>>>>
>>>>>There is an index on inv_number and the table is type FPW2.6
>>>>>
>>>>>The result set is about 30000 records. In VFP 5.0a this is slow. Takes about>2 mins and sometimes runs out of memory if into an array. I read somewhere some time ago that there is a sys() function that improves the memory management of vfp and speeds up the select.
>>>>>
>>>>>I dread to think what would happen if I needed to extract from 2 tables using a join condition!
>>>>>
>>>>>Does someone out there working with large tables have any ideas? A view is not what I'm looking for, parametised or otherwise.
>>>>>
>>>>>Thanks in advance
>>>>>
>>>>>Bernard
>>>>
>>>>You should have index tag on YEAR(inv_date) and another one on Rec_active.
>>>
>>>Lets say I want to do it without indexes! Why is it still so slow??
>>>Do you know about or have you read about that sys() command??
>>
>>You must use indexes to optimize any query. I see that you heard something about SYS(3054), but this is just for checking/tuning purposes.
>
>There is an index on inv_number and even if I do a select as so
>
>SELECT * from inv_table WHERE BETWEEN(inv_number,20000,60000) into...
>
>it is still sloooow!
>
>What are the settings for sys(3054)?? or is there any other way!
>
>Besides there will be times when I do adhoc queries on this table and cannot create an index for every type of query!!

Hi Bernard. You are missing some fundamentals here. Even SQL server cannot do a query quickly without indexes. You must create indexes for optimization to take place. Look at my Foxpro Advisor article in June 1997 and an earlier article in September 1996. As far as your SQL above, the proper SQL syntax is SELECT * FROM INVOICE WHERE INV_NUMBER BETWEEN 20000 AND 60000. The VFP Between function wouldn't be useful in a SQL server environment and probably isn't completely optimized when called from an SQL query.

You will have to build indexes on some fields so that if the adhoc queries use any of the fields you will get partial optimization.

Mike Yearwood, GE Capital Consulting