Multi user access of record slow 1

[dishati]

Friends,

I have developed a multiuser VFP 7 package, its pretty fast in the starting but after i add record to the dbf file it becomes slow, Once i index it again it works fast but than it becomes slow after some records added.

It is not recommend to index every day right ! Any suggestion.

I index the file as below:

SET EXCLUSIVE ON

SELECT A
USE CARE
INDEX ON FTCODE TAG FTCODE1
INDEX ON FFIRST TAG FFIRST1
INDEX ON FLAST TAG FLAST1

SELECT B
USE STUDENT
INDEX ON FTCODE TAG FTCODE1
INDEX ON JOINGRADE+SFIRST TAG JOINGRADE1
INDEX ON SFIRST TAG SFIRST
INDEX ON STCODE TAG STCODE

Is there any better way.

 

[Olaf Doschke]

>It is not recommend to index every day right

Why would you? An index is as permanent as a field. You add it, it's indexing existing data and updated together with the data.

Read the help chapters about rushmore, then you know what they can optimise and how your queries should look like to make use of the indexes.

Bye, Olaf.

 

[Mike Lewis]

This is one of the commonest mistakes I see among people on my training courses. A lot of developers seem to think that you must create the indexes every time you open the table.

Disahti, you only have to create each index once. After that, when you want to use a particular index, use SET ORDER TO.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[dishati]

Thanks OlafDoschke and MikeLewis

I understand that index has to be done once only.

My problem is that the software become very slow if i do a search on field it come in seconds x after continuos work response time is slow.

After that if i have to index again to make it normal.

Any suggestion ?

 

[JRB-Bldr]

My problem is that the software become very slow if i do a search on field it come in seconds x after continuos work response time is slow.

But you have not told us how you perform your 'search'.

Do you do a SEEK or a LOCATE?
And are you using a FILTER on your data table?

There are a lot of 'other' issues which might be contributing to the speed you are seeing.

Generally the issue is not the data table performance, but instead it is most often your code that is the issue.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

And - even more important - what value are you searching on?

In general, a SEEK or LOCATE on an expression that matches an index tag will be very much faster than one that doesn't.

Perhaps you could show us an example of a search that performs badly. (If so, please just show the relevant code; leave out anything that does not contribute to the problem.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

What Mike says about the importance of what and how you search also is reflected in the help topics about rushmore optimisation I seggest to read.

If I accept for a moment, that you get faster results after reindexing, then the reason are most probable not the indexes themselves, because they won't differ much before and afterwards. There is much memo bloat, but only few index bloat, that would contribute to that.

What experience I can share is, that VFP get's more sluggish, if you use up much memory and don't flush it, which a reindex indirectly does. So instead of indexing, what you could try is to close and reopen dbfs. Something that also would be done frequently in a programming style of private datasessions.

There are many parameters, which can contribute to the overall performance, also location of temp/workdir. Also see FLUSH FORCE, SYS(1104) and SYS(3050). I'd rtather suggest you limit buffer size to 16MB at start as in SYS(3050,1,16384*1024) and the same for SYS(3050,2,16384*1024), that's sufficient and grows automatic, when needed. You can try out on your own, see

http://fox.wikis.com/wc.dll?Wiki~sys3050

for further discussion.

Bye, Olaf.

 

[Nigel Gomm]

Olaf,

what's the thinking behind your SYS(3050,1,16384*1024) suggestion?

There's a lot of conflicting advice out there about SYS(3050) and i was struggling with an issue just yesterday that may be caused by memory allocation.

nigel

 

[Olaf Doschke]

The thinking to just reserve a small amount of 16MB is, that foxpro demand for memory hasn't changed much in time, and 16 MB where huge memory still in the early 90s.

The runtime is just about 8MB, the standard behaviour is to claim half or third of RAM, which is much too much in todays GB-8GB PCs. VFP will use 2GB at max as a 32bit process anyway and you might think to go for that max RAM, but the management also costs and you take RAM away from other processes and threads, which then need to swap. Don't be selfish, just take the amount you really need. And even if your tables are much larger in the area of GB, you don't read them fully into RAM anyway.

Foxpro will higher the buffers, if it needs more RAM, because the OS will give more RAM, if there still is, before swapping memory into pagefile.sys

You can experiment with SYS(3050), as the wikipedia article says. It will be very specific for your situation. I just see foxpro getting sluggish after lots of data crunching in a typical data migration task, pulling and writing lots of data without any user intervention. The mono thread nature of foxpro then makes it cache much and use lots of RAM and only after you flush it, foxpro will become responsive again, and closing and reopening tables also does this flushing.

In a normal application you will use private datasessions for forms, which naturally also closes and reopens tables. I never experienced foxpro getting sluggish in this normal situation. You might just have queries taking a bit longer and should then thinkk of splitting into smaller queries using Doevents Force inbetween, too, to keep the UI responsive.

Bye, Olaf.

 

[gabrield]

Olaf very nice explanation about sys(3050).Thank you

Dishati without database structure it is kind of guessing.
Can you post the structure of dbf's?

Meantime check index size versus dbf size kbytes/mbytes.
How big are the dbf's?
Is the cdx file bigger than 10 MB ?

 

[foxmuldr]

dishati,

There's nothing wrong with re-creating indexes from time to time, or even every time you open the table. It won't corrupt anything. It's just not often necessary and a user / developer having the mindset that it *IS* necessary is a problem. However, it certainly won't hurt anything.

In addition, if re-creating the index every day seems to solve your performance issue without any other code changes, I would continue to use that as a fix. Tracking down the real cause could become an exhaustive effort in isolating variables one-by-one until you happen upon the right one.

It comes down to what your goals are here. Is this an app that scores of separate users / offices will run, one which needs to behave properly because it needs to behave properly? Or is it something you're using in your own business and it's not a big deal to re-create the indexes and then, at some future date, when you have more free time to come back to investigating the actual cause of the error for the purposes of tracking down the real cause, to then do so?

It's up to you.

Best regards,
Rick C. Hodgin

 

[Olaf Doschke]

foxmuldr,

the pont is, index creation always is taking time. Time most probably unneeded, especially if this is done before each query.

Bye, Olaf.

 

[Mike Lewis]

Rick,

Since the essence of Dishati's problem is that his query is too slow, it seems obvious that he should refrain from re-creating the indexes every time he opens the table.

I agree that there might be occasions where it's desirable to re-index a table, but surely not every time you open it. At the very least, it should be done as an administrative task. As far as we can tell from his code, Dishati is re-indexing every time any user opens the tables in any form, and that might be many times a day.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Nigel Gomm]

Olaf,

Thanks for that.

It doesn't appear to make a measurable difference in my testing but i'll bear it in mind for the future.

n

 

[foxmuldr]

I was under the impression the query is slow only from time to time, and that re-creating the index brought it back up to speed.

My mistake.

Best regards,
Rick C. Hodgin

 

[Olaf Doschke]

foxmuldr,

maybe your impression is correct. I just said "IF it is done before each query", I don't know if that's the case, of course.

But my assumption is, the whole code get's slower, the more data is in the tables, and that's just natural, if you do something like indexing before each query. As long as the table is small it won't matter much, but in a large table fetching a few records can get very slow, if you always index beforehand.

Maybe "It is not recommend to index every day right !" was meant more literal. It's ok to index every night, just to get a clean index.

I just can't imagine the indexing being the main reason for things to suddenly become much faster again, it could be indireclty a reason, just because tables then are in use exclusive for a moment and buffers are flushed. That's why I talked about the memory all the time.

An index is not getting 10 times faster, just because you REINDEX. The index speed can't wear out this much, that it will play a role.

I need to see the queries made to see how the indexes would be used at all. If queries filter on fields and expressions not indexed, the indexing has no effect, but the index process also reads every record, whch might make queries not profiting of the indexes directly still faster by profiting of cached data.

I'd not be surprised if the reason for getting back to normal performace is not the refreshed index at all, but just a side effect.

Bye, Olaf.