Performance issue opening files.

[hjohnson]

I'm reviewing some code in our app that occasionally has some complaints from users about being slow. I've narrowed the problem down to a portion of code that opens a file, appends a new record, then closes the file.

My question is, does anyone know or has anyone experimented with what happens to a table as it is being opened in relation to other users. I've been trying to find a reference to this , but my thinking is that perhaps there is a very small lock placed on the table header each time the file is opened, causing a slowdown for other users.

I've made some improvement by replacing:
append blank
replace...

with

insert into..

If anyone has any information on this or anything to add to this, I'm always looking for tidbits of info that I can use for improvement.

Thanks
Hal

 

[Mike Lewis]

Hal,

You're on the right track. But it isn't the opening that's causing the lock -- it's the appending of the new record. An INSERT will be slightly faster -- but not much. In both cases, VFP will lock the file header, but only for as long as it takes to add the record.

That said, the opening also takes time, but it shouldn't be enough to cause a problem. Are you doing this operation in a loop? If so, it would be faster to open the table before the loop starts and close it again afterwards.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[foxdev]

I can't imagine that this operation would be noticably slow...milliseconds typically.

Are there a tremendous number of indexes or index tags on the subject file?

--------------
art prints

http://www.artlot.com

 

[alvechurchdata]

Just a few more thoughts:

Are you adding explicit values to the new record or are you embedding functions in the Insert command?

Is there an Insert trigger?

Have you got a filter on the table?

Where is the data? Does it run much faster when you put the data on a local disk?

Have you tried logging the events in the debugger to see exactly which line is causing the delay?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[hjohnson]

Thanks for all the comments. To answer some of the questions:

The table is a free table- no triggers or filters. There are 11 fields and only 1 index which is Compound on a char field + dtos(date fields).

The whole process takes place in a procedure to which is passed 5 of the 11 fields inserted.

This procedure is called in a loop and with each call the file was opened, append blank, replaced, then closed.

When run through the debugger, there doesn't seem to be a problem. it's only when several users happen to be calling the same procedure at the same time.

I've since change the process so that the file is no longer closed and changed from an append blank/Replace to an insert. The combination of which has greatly improved the performance.

I believe I had read someplace, but can no longer find it, that when tables in a DBC are opened, the header is momentarily locked. Is it possible that this could be the case for free tables as well?

Thanks

 

[Mike Lewis]

I feel sure that the problem was caused by constantly opening and closing the table inside the loop. As far as I know, opening a file does not lock anything, but I'm not 100 percent sure.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[alvechurchdata]

When run through the debugger, there doesn't seem to be a problem.

I've had strange behaviour in the past when opening and closing tables quickly. The process worked in the debugger but failed in production use.

The chain of buffers from Fox to the server's disk is beyond my understanding but I began to suspect that something along the process was lying to me and telling me it had closed a table when it hadn't. I "solved" the problem (for want of a better word) by inserting some Flush statements.

This procedure is called in a loop and with each call the file was opened, append blank, replaced, then closed.

It sounds as though you've done the right thing by taking the open/close processes out of the loop altogether. They might only take a few milliseconds but these milliseconds add up when they're in a loop.

Geoff Franklin

http://www.alvechurchdata.co.uk