CODEWITHSUNDEEP
performancefoxpro
Chakra
Chakra

Reputation: 2557

Tuning FoxPro data

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.

Upvotes: 0

Views: 1382

Answers (6)

nstenz
nstenz

Reputation: 117

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.

Upvotes: 0

Rick Schummer
Rick Schummer

Reputation: 1067

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

Upvotes: 0

stuartd
stuartd

Reputation: 73243

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

Upvotes: 1

kevinw
kevinw

Reputation: 2178

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.

Upvotes: 2

DRapp
DRapp

Reputation: 48139

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

Upvotes: 2

Arnis Lapsa
Arnis Lapsa

Reputation: 47567

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

Upvotes: 0

Related Questions