Tuning FoxPro data

Question

We tune a SQL server database by index defragmentation, or re-indexing, or dropping and rebuilding an index. Are there any such data tuning techniques for Foxpro ?

Thanks, Chak.

Answer 1

Reindexing and packing tables helps. Even class libraries (.vcx) are tables that can be packed. But unfortunately i don't remember exact commands.

Answer 2

for defragmenting tables...

USE YourTable EXCLUSIVE
PACK

if your table has any memo fields do

PACK MEMO

if the table has indexes, the pack will automatically reindex them.

As Arnis mentioned, most stuff in VFP are based on tables... forms, classes, reports although they have different extensions. So you could do

use YourForm.scx exclusive
pack memo 

use YourClassLib.vcx exclusive
pack memo

use YourReport.frx exclusive
pack memo

use YourProject.pjx exclusive
pack memo

Additionally, if for your regular .dbf tables you want to kill individual indexes...

use YourTable exclusive
delete tag MyIndexTag

or, to delete ALL indexes

delete tag all

Answer 3

Another thing to bear in mind is that FoxPro databases are just a collection of files on the server. Therefore things like server disk fragmentation, and ensuring that anti virus is excluded from those files, can make quite a difference too.

Answer 4

For reindexing, you're better off doing it yourself with a procedure like this: REINDEX sometimes fails to fix index corruption.

procedure reindextable

lparameters cTable
local cDBC, nTagCount, cTag, nTag
local array arrTags[1]

if pcount() = 0
 ? "No parameter"
 return -1
endif

close tables all

use (cTable) exclusive

? "Reindexing " + alltrim(alias())

nTagCount = tagcount()
if nTagCount = 0
 ? "No tags found"
 return -1
endif

dimension arrTags[nTagCount, 7]
for nTag = 1 to nTagCount
 arrTags[nTag, 1] = tag(nTag)
 arrTags[nTag, 2] = key(nTag)
 arrTags[nTag, 3] = for(nTag)
 arrTags[nTag, 4] = unique(nTag)
 arrTags[nTag, 5] = primary(nTag)
 arrTags[nTag, 6] = candidate(nTag)
 arrTags[nTag, 7] = descending(nTag)
endfor

* OK, we have the info to re-create the tags. Now delete the existing tags.

delete tag all

* Now re-create the tags
for nTag = 1 to nTagCount
 if arrTags[nTag, 5]
  * Primary key; need to use ALTER TABLE
  cTag = "ALTER TABLE " + cTable + " ADD PRIMARY KEY " + arrTags[nTag, 2]

  * Thanks to Anders Altberg for the info that you can add a filter to a PK, as long
  * as the TAG appears *after* the filter.
  if not empty (arrTags[nTag, 3])
   cTag = cTag + " FOR " + arrTags[nTag, 3]
  endif

  cTag = cTag + " TAG " + arrTags[nTag, 1]
 else
  * Regular index (or possibly a Candidate)
  cTag = "INDEX ON " + arrTags[nTag, 2] + " TAG " + arrTags[nTag, 1]
  if not empty (arrTags[nTag, 3])
   cTag = cTag + " FOR " + arrTags[nTag, 3]
  endif

  if arrTags[nTag, 4]
   cTag = cTag + " UNIQUE "
  endif

  if arrTags[nTag, 6]
   cTag = cTag + " CANDIDATE "
  endif

  if arrTags[nTag, 7]
   cTag = cTag + " DESC "
  endif

 endif

 * This will create the tag
 &cTag

 ? cTag


endfor

? "Success."

return 0

Answer 5

If you don't have a reindexing procedure created, run out and get Stonefield Database Toolkit:

http://stonefield.com/sdt.aspx

One of the things it does is build metadata about the indexes. It has a command to reindex all the tables, or one table at a time. You add or drop an index, no need to keep track of it or alter your reindexing routine. Validate the metadata (built in feature), and ship out the updated metadata with your DBC files and update. Production tables (structures and indexes) are updated to match what you have in development.

Most VFP developers using database contained DBFs find this tool indispensable.

As for PACKing your source code (SCX, VCX, FRX, LBX, MNX, PJX), all you have to do is a Rebuild All when you build the project. VFP will pack all the source code behind the build. This will reduce the size of the resulting executable, not optimize or tune the database.

Rick

Answer 6

PACK can be dangerous- if something happens (crash, power outage, etc.) during the command, the table is likely to be corrupted. ALWAYS make a backup before you PACK a table.

We rarely use PACK at my office because we rarely delete anything other than records in temporary tables- everything else is kept for historical purposes.

Definitely use REINDEX every once in a while, though.