Network / Multiuser very slow!

[JarekZ]

Hi all,

Situation:
3 computers running windows XP, connected via 1Gbit switch - no server.
database is on one of them and entire folder is shared for rest of the users.

When any of the computers opens the Data Base alone (no other computers are connected) everything works fine - on every computer the same performance - does not matter if the DB is on local disk or networked.
I have one form that does some calculation and I measure the time of that calculation - it is around 0.2 sec for average data.

The moment other computer over the network connects to the DB performance rapidly drops!!
Now the average time for the same calculation is around 2.0 sec! - 10 TIMES MORE! Whole application just slows down and is really annoying to use!

I have done more tests:
-> when several users open the same application from one computer (i have open 3 times runtime version on my desktop) everything works fine!
-> when two computers run the application from the networked hard drive (the shared one) both of them run slow!
-> when two computers run the same app. - one locally, one over the network the computer reading from local hard drive runs OK when the other runs SLOW.

-> it does not matter whether computer uses Shadow Database from its local hard drive or reads straight from the networked drive - it is small difference between shadow and reading from network but it is nothing comparing to the 10 times slowing down for more than one user.

one more thing - I can not blame the hardware - all the computers are new (2 x Intel Xeon 2GHz 4 cores).

If somebody has similar issue please share with me your solution(s) - if there is any.
I hope it is due to my poor development - not to Alpha5 problems - the first one can be easily changed ;)

[Dan Blank]

Re: Network / Multiuser very slow!

I'm thinking it is because you don't have a server. The one computer with the database on it is in use and has to use its resources to run that computer plus act as a server which deminishes the resoureces it has available to share the database with the other two users.

[Tom Cone Jr]

Re: Network / Multiuser very slow!

Check your anti-virus settings. Have you disabled real time checking for reads and writes to the database folder?

[csda1]

Re: Network / Multiuser very slow!

Hi Jarek,

The amount of memory on your database computer probably should be 1 Gigabyte of memory at least, 2 Gigabytes or more even better. You should also check if any virus scanning programs are active on that directory, as that can slow things down as well in some cases.

Where you run each computer's Alpha 5 exe, and the temporary and private directories should be on the local drive for all computers.

If anything else is using the network (doubtful), it should not be maxing out the network ever.

The indication you've outlined is that the moment the database is shared, the speed drops. Check if that is really so from each of the non-database computers while the database computer has Alpha 5 open on the database.

Finally, what is the expression/code of the calculation that increases 10 times? There may be something wrong there, e.g. no LQO. How many records are in the table that the expression uses?

[aschone]

Re: Network / Multiuser very slow!

This issue sounds very strange. Most definitely will be hard to pin point with the currently available information.

You will need to help troubleshoot and eliminate items in order to pin point the issue.

I would start with the Anti-virus software. Completely disable it on your test machines when testing.

Weather or not you need to bring a server into the enviroment I would be hesitant. If you machines are running Xeon processors you already have server spec'd processors. But in this situation I would hazard to guess that the processor's are not being over worked. The memory and networks could need an upgrade but honestly with Windows XP 512MB is plenty. If all you are running is alpha5 and AV you should have plenty of memory in an XP machine with 512MB. Vista needs a 1GB to start and then upgrade to 2GB but XP is much more efficient than Vista will ever be.

You say you have a gigabyte switch but are the network cards also gigabyte? Even if you are running 10Mbps or 100Mbps I do not believe that you should be seeing these issues based on network speeds.

In your testing you need to determine if the problem is hardware or software.
Hardware side you have...

Hard drive speed
Network speed
Memory speed and size
Processor

Software side you have...
Operating system (XP is proven reliable- safe to eliminate this)
Anti-virus (disable during testing)
Alpha5
Your database.


Here is how I would proceed.
Performance Monitoring - this tool will help eliminate if the issue is with your
hardware. http://www.windowsnetworking.com/art...-Counters.html There is alot of information out there explaining how to configure and read the tests / results. This only scratches the surface. These tests will be the deciding factor if you need to upgrade your hardware.

Single machine test. You mentioned that you tested on a single machine but did not mention the test procedure you used or if it was the same test that you used when testing across multiple machines. I would develop a set test routine that you know will generate the symptoms. Then using that test procedure I would test on a single machine with the database being local and then I would test on a single machine with the database being hosted on another machine.

When testing and using multiple machines what are the other machines doing during the test? Do you just have the control panel open or are people trying to open forms, input data, sort data, run reports? All of this should be part of your test routine. You will need to determine when something works and when it does not and what the differences / similarities are at those times.

With all of that said I will state I would start my investigation at the development side of your database. Two ways to do it, search the forums for the posts dealing making your database efficient and what issues others have done and how they have fixed them. Starting points are with set design (simple is best, many small sets are better than one big set). The next item is indexes.
The next way is to post a working sample of the database with fictitious data. So that others may have a look, might be something that you are simply over looking that a different set of eyes will be able to spot.

[JarekZ]

Re: Network / Multiuser very slow!

Thank you very much for all your replies!

I will try to make an example application and show what exactly I have in mind.
Yesterday I made some more tests and I have found out that when one computer makes an operation on the table - for example adds values from one filed for every record and at the same time other computer opens that table too then the speed drops dramatically.

I know that we will have to buy the server (sooner or later for other purposes- not mainly for alpha) but I don't know if this will help much?
Alpha5 is not really working as a database server - unless the Application server is created but just for the web pages applications?
Could somebody explain me that? What will be the use of the server for my Alpha5 application (I just use the forms and reports - no www pages)?
Will there be any difference from sharing the folder with database on one of the computers?
Has this performance issue something to do with windows XP and some limitations about reading one file by many users?

Once again there is no problems with the hardware - 2 quad core processors @ 2.0GH, 4Gb of RAM and dissent graphic card and 1Gbit working network on manageable switch can not be too week for alpha purposes.

Thanks,

[Tom Henkel]

Re: Network / Multiuser very slow!

We run a very large network using servers. It is paramount to make sure real time file system scanning is OFF for your data folders. Antivirus Software will bring any activity to its knees because each time anyone attempts to access even a single record, Antivirus kicks in and does its thing.

Tom

[JarekZ]

Re: Network / Multiuser very slow!

Thanks Tom,

I'm pretty sure that the antivirus is disabled.

Will use of the server give me better performance at all? - for desktop application use of Alpha five?


If yes then how the server will be configured - Installing alpha on server will have no use at all, In my opinion!?

[Tom Cone Jr]

Re: Network / Multiuser very slow!

Yesterday I made some more tests and I have found out that when one computer makes an operation on the table - for example adds values from one filed for every record and at the same time other computer opens that table too then the speed drops dramatically.

This kind of test is very different from what you described initially. When you run the operation Alpha fires record changes at the table at a speed that resembles dozens and dozens of separate workstations sending single record changes in rapid succession. A slow down should be expected, given the file sharing model Alpha Five desktop apps employ. If running a table wide update operation against a live table used for transactions by other workstations is required, you might want to rethink your design, or look for ways to speed up the operation, and lock out other users altogether (until the op has finished).

[JarekZ]

Re: Network / Multiuser very slow!

Attached simple application.
One table, one form and some script counting sum of all records.

Compared two methods - loop and tablesum function.
Attached also my results for:
- desktop computer (where the database is)
- connected laptop (reading the db from desktop) when no other user opens the table
- laptop when both computers open the form.

Is this normal behavior?

[csda1]

Re: Network / Multiuser very slow!

Hi Jarek,

Attached simple application.
One table, one form and some script counting sum of all records.

Compared two methods - loop and tablesum function.
Attached also my results for:
- desktop computer (where the database is)
- connected laptop (reading the db from desktop) when no other user opens the table
- laptop when both computers open the form.

Is this normal behavior?

I ran some tests on my A5V8 systems and saw similar times that you saw.

I tried tbl.batch_begin() and tbl.batch_end() with no improvements and it apparently doesn't work with Read-Only table opens (I'd say that this should not matter and would say it should still work for R/O)

However, it seems that by some process step I can't identify, at points the table seemed to go back to the faster speed after editing the script. So there might be a bug here, but I've yet to identify the cause

On the other hand, one might ask what the issue is with using tablesum() for you? It is a lower-level (read as faster) function of Alpha 5. It would potentially always be faster than an XBasic loop.

[JarekZ]

Re: Network / Multiuser very slow!

Thanks Ira for reply,

good that somebody gets the same results - it is not my antivirus then..

On the other hand, one might ask what the issue is with using tablesum() for you? It is a lower-level (read as faster) function of Alpha 5. It would potentially always be faster than an XBasic loop.

yes you are right, for this particular example tablesum is perfect, however in my real application I have to do some more complicated operation than just sum up the values. Ive just put it here to make my point more visible - but difference in time can be also visible for tablesum.

So the question now is ... what do we do about it? and will server with real server software help here at all - Im pretty sure that this situation will not take place for 'real' database like MySQL for example - weekend is coming and ill test it too.

Any more opinions?

[JarekZ]

Re: Network / Multiuser very slow!

Hello,

If somebody is still interesting in that topic:

I have tried the same thing using MySQL & PHP and off course situation like that (slowing down when more users reads the same table) has no place.

I have done some more test and I found out that quering table that is opened by other user is ridiculously slow!

I have attached a simple app that is showing what i have in mind.
When you run script2 on the form you will see times for 3 different methods that can be use to achieve the same result. In my case all times are similar when one computer uses the table. When other user connects then quering the table is almost impossible.

The worst thing is that when you develop and application everything seems ok but when application starts to work on network then speed problem is an issue!

Can somebody point my mistake(s) and show me better way than in the attached app?

In my real application I use a lot of onFetch events (that by the way seems not working in the newer patches than 3212) where script is counting different things (and very often quering some tables many times).That application is almost impossible to work on the network.
Now I have to rebuild all the application and search for alternative approaches - I wish I know about it before I have started to work with Alpha!

Thanks for help!

[MikeC]

Re: Network / Multiuser very slow!

Jarek,
I took a look---Is it necessary to have your queries inside your loop?? I would think not and if not by placing them prior to your loop the query (and the others as well) significantly speed up.....also made your two indices for ALL instead of Unique so as to satisfy LQO--I will have to check out if this actually helped at all though.

Try this and if the results are still what you want, then the speed increase should help....

[JarekZ]

Re: Network / Multiuser very slow!

Mike,
Thank you very much for your interest in that post.

Is it necessary to have your queries inside your loop?? I would think not and if not by placing them prior to your loop the query (and the others as well) significantly speed up.....

I'm not sure if I understand what you are saying... I will try to play with it though.

If you have some time to spare, could you modify my application and post it back here? - My problem is that I don;t really get the Idea of indexes and how to properly use them... (it is sad i know)

also made your two indices for ALL instead of Unique so as to satisfy LQO

After doing that alpha is showing an error that some temporary file can not be created...

Your help is greatly appreciated.