Really slow table access

[chpicker]

I have a table with over 2 million records in it currently and has a single computer adding up to 100+ records per second, depending on current call volume. Now, this table is mainly for debug purposes so I can watch how my automated system is thinking.

When I open this table from another computer, then try to BROWSE, it seems to lock up for around 10 seconds before finally displaying the window. It also locks up like that if I try to execute an SQL query on it, or anything else that actually tries to access the table.

Does anyone have any suggestions on what could be causing it? Is it the fact that there is a computer that sometimes dumps 100 records a second into it? Is there something else I might have overlooked? This is the only table it happens on.

 

[TomasDill]

Hi!

Did you tried the FOPEN() on the DBF/FPT/CDX file and see how it works through network? If it still delay, than it is a networking or server OS issue. If it is quick, then this is a problem in the table. Try to remove indexes and see how it will work. Guess the problem could be in indexes, but, indeed, its stramge because I did not met such problem before.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[ChrisCarroll]

Or, does it have a lot of deleted records? In which case finding the first window-full of records to display involves a lot of searching...

 

[EzLogic]

Are you accessing the table through a Network? if so, do you have a Fast HUB? (Switch 10/100MBS switch)?. Do you have Exclusive right? or shared?

 

[chpicker]

Vlad: using FOPEN(), FSEEK(), and FREAD() on the table and index files is near-instantaneous. I haven't tried deleting the indexes and recreating them, but I might do that next. Do you think PACK would affect it, since it basically copies all the records into a new table and renames it? Does it create a new index when it does this, as well?

Chris: No, there are no deleted records. But I've certainly seen what you're talking about before...I learned that one the hard way. (

Tekno: The table is accessed over a 10/100 HUB, not a switch. The network is small (12 computers total, including the server). I have to use shared access because I'm trying to access the table at the same time that another computer is constantly writing to it (sometimes as many as 100 records per second).

I'll give Vlad's idea a shot and see what happens. I'm not particularly optimistic about it, but hey, I asked you guys for help, and I'm not about to turn my back on what's offered. )

 

[TomasDill]

If FOPEN(FileName,12)/FSEEK()/FREAD() are quick, than the problem is in the table or in the VFP (exactly, in the way VFP reads that table). I recommend you to validate its structure and see if it is not corrupted by some way. Also, are there memo fields? PACK could help. Yes, PACK updates index file, but I still recommend to remove all indexes and create them again. There are also certain settings in VFP that tell VFP about the langth of internal memory buffer for data caching (some sys() function). If it is too small or too large, the performance for large tables could degree too.

Have no other idea...


Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[EzLogic]

I agree with vald on this one

Tekno

 

[chpicker]

No, there are no memo fields in the table. How can I validate the structure? It's a free table, if that makes a difference. I'll see if I can find that sys() function for the data cache buffer.

 

[TomasDill]

To assure valid structure, you can just COPY TO that table into another table but use another fields order to assure DBF is not just copied as is.

sys() function is SYS(3050)

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[chpicker]

Ok, I deleted the CDX file, and table access is instantaneous once again. I'm in the process of rebuilding the index file now, but it takes awhile to index 2 million records 4 times, so we'll see how it goes.

Any suggestions for what could cause the index file to have problems like this and how to prevent it?

 

[PETO]

Index file slowness. How many tags are in your index ?
Sometimes the order of your tags can slow things down.
Put your most oft used tags at the top. Are you on Win2000 computer? Someone recently had trouble with 3 Dell computers arriving to their office configured with the same MAC address....slowed things to a halt when all 3 were accessing same tables over network.