Best way to subset a table? 1

[Loomis]

Using VFP9, I have a table with 200,000 records. A recordtype field in the table is used to hold one of five values (A, B, C, D, E). Then, one of five pages in a pageframe is used to display each of the five recordtypes. For example, page 1 displays records of recordtype='A', page 2 displays records of recordtype='B', etc. Once the appropriate subset is displayed, records are updated by the end user. My question: What is the quickest way to display a subset of the table so that the results appear almost instantaneously. Should I use the "Set Filter To" command? Create a view for each page? I don't want to split the big table into five small ones. Thanks!

 

[crewchiefpro6]

Set filter is very slow. I would use a SELECT statement into an updateable cursor or a View.

Select <<A>> from <<yourtable>> order by <<yourorder>> into cursor csrPageA

Then use that cursor in a grid for each page.

This is a Job for Andy or Marcia, and the other heavy hitters, I am quite sure they have "Been There Done That".





Don Higgins

http://www.crewchiefpro.com

 

[alvechurchdata]

Do as Don says but make sure you've got an index on the field you're using for the selection. The query will run faster if the index exists and faster again if you use SET ORDER TO to leave the table unordered.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MarciaAkins]

Should I use the "Set Filter To" command?

If you are using Vidual FoxPro version 9, you can do this. If you are displaying the filtered view in a grid, just remember to set the grid's optimize property to .T. (it is .F. by default)

Marcia G. Akins

http://www.TightlineComputers.com

 

[SGLong]

I ran into the 24 item limitation and came up with this solution.

1] Create an array of the key values
2] Do a SELECT where the WHERE clause uses an ASCAN to match up with the array list.

SELECT myField from myTable WHERE ... INTO ARRAY myList

SELECT field1, field2, ... field99 FROM ;
myOtherTable INTO CURSOR curResults ;
WHERE ASCAN(myList,myField1) <> 0

Steve

 

[SGLong]

Ignore this... I put it in the wrong thread....

 

[alvechurchdata]

Query speed is not affected by the current SET ORDER.

Thanks for that little tip, I'd always assumed that the behaviour was the same as in xBase commands.

Geoff Franklin

http://www.alvechurchdata.co.uk