Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Sean Bora

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Hi,

I appreciate some help from the experience Pdox table users amoung you...

The problem:
==========
I have a D3.02 application that whizzes along when only one user is active,
but gets very (unusable) slow with two of more.

When it's slow a "last" can take 30 seconds (as opposed to instantanaeous in
single user). Also selecting a new index an "apply range" can take 30 secs
or more?

The setup:
========
The appllication is stored locally (win95), the paradox tables on an NT4
server, mounted via UNC. The main table is 1.2k/record with almost 5000
records. Not that big. A few tables are DBASE mounted readonly from another
application.
I have 3 secondary indices.

PrivDir is set to c:\temp, NET dir to a dedicated UNC on the server,
identical for all clients. All clients use the exact same Delphi app.
Client have between 32 & 64MB and are fast enough.
There are two BDE aliases, I set them from an ini file on start up.
All tables are close at design time and opened when the main form opens.
Queries are also closed as design time and closed and reopened at each
usage.
BDE settings: I set localshare=true and increased sharememsize to 4096 and
memsize to 24
The server is not overloaded.
I have about 30 tables, 80% or them are read only. The main table has lots
of reading & writing (reading via SQL local queries mostly), writing via
searching on one of the indices

I tried upgrading to BDE5.01, it didn't help..

What am I missing? Any ideas? It's really getting frustrating!

I would really appreciate help on this one.....

Thanks in advance,

Sean Boran

Markku Nevalaine

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Quote

Sean Boran wrote:

> The appllication is stored locally (win95), the paradox tables on an NT4
> server, mounted via UNC. The main table is 1.2k/record with almost 5000
> records. Not that big. A few tables are DBASE mounted readonly from another
> application.

Is it 1.2 kB * 5000 => 6 MB of table size?

If you scan through this table, from First to Last, then this means you
have to move the whole table from the server to the workstation.

Standard 10 Mbit/s ethernet network can move data with the theoretical speed
at 1 MByte/s and average speed something like 600 kByte/s. So the response
time can never be instantaneous, but better something like 10 seconds at
the minimum.
When you make an indexed search, then the reponse of course time does increase
this dramatically.

What kind of response times (in seconds) are you looking?

Markku Nevalainen

Sean Bora

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Quote

>> The appllication is stored locally (win95), the paradox tables on an NT4
>> server, mounted via UNC. The main table is 1.2k/record with almost 5000
>> records. Not that big. A few tables are DBASE mounted readonly from
another
>> application.

>Is it 1.2 kB * 5000 => 6 MB of table size?

Correct.

Quote

>If you scan through this table, from First to Last, then this means you
>have to move the whole table from the server to the workstation.

What's the alternative?

Quote

>Standard 10 Mbit/s ethernet network can move data with the theoretical
speed
>at 1 MByte/s and average speed something like 600 kByte/s. So the response
>time can never be instantaneous, but better something like 10 seconds at
>the minimum.
>When you make an indexed search, then the reponse of course time does
increase
>this dramatically.

Normally it does it in about one second (we have a quite network in a small
company).

One of the users just indicated that it gets slow even with only one user
after 5-10 minutes.. I wonder if it's that new QR3.04 that I upgraded from
QR2 a few weeks back? Maybe it has memory leaks?

I would appreciate your ideas or manipluating these tables quickly, so I can
check how I doing things.

"last" if often used because a user often wants to jump to the item he was
working on...
Anyway why does it take ages to change index or apply a range? Does that
involve a large net transfer too?

Thanks in advance,

Sean

Markku Nevalaine

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Quote

Sean Boran wrote:

> >If you scan through this table, from First to Last, then this means you
> >have to move the whole table from the server to the workstation.

> What's the alternative?

Alternatives are, use 100 MBit/s network adapters. Or move from Paradox
to SQL-database. Of course SQL-database can't move the data any faster,
but the SELECT -statements minimize the data that need to be moved via
network.

Maybe these are not the key to solve your problem quickly, at least
if you already once had better performance. At least you have some
direction where to go and test next. If nothing else helps, somehow try
get back the installation state you had 2 weeks backwards.

Markku Nevalainen

Sean Bora

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Well, I tried

- setting localshare=false, it's didn't make any noticeable difference.
- I also backed out QuickReport3 to QR2, no difference. [It's not easy to
back out!].
- Here's a example of some code that can take up to 40 seconds (with one
user it take about 1 second):
IndexName:='ByJob_status';
SetRangeStart; // set start
FieldbyName('Job_status').AsInteger:=1;
ShowStatus('Editing job: set range end');
SetRangeEnd;
FieldbyName('Job_status').AsInteger:=2;
ShowStatus('Editing job: apply range');
ApplyRange;

If 2 users access the system it gets very slow. To get fast again, both
users need to stop the application & reboot..

I appreciate any further help, I'm pretty stumped.

Thanks,

Sean

Sean Bora

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Quote

>The problem:
>==========
>I have a D3.02 application that whizzes along when only one user is active,
>but gets very (unusable) slow with two of more.

>When it's slow a "last" can take 30 seconds (as opposed to instantanaeous
in
>single user). Also selecting a new index an "apply range" can take 30 secs
>or more?

And quickreports can take 30 minutes!

Quote

>The setup:
>========
>The appllication is stored locally (win95), the paradox tables on an NT4
>server, mounted via UNC. The main table is 1.2k/record with almost 5000
>records. Not that big. A few tables are DBASE mounted readonly from another
>application.
>I have 3 secondary indices.

>PrivDir is set to c:\temp, NET dir to a dedicated UNC on the server,
>identical for all clients. All clients use the exact same Delphi app.
>Client have between 32 & 64MB and are fast enough.
>There are two BDE aliases, I set them from an ini file on start up.
>All tables are close at design time and opened when the main form opens.
>Queries are also closed as design time and closed and reopened at each
>usage.
>BDE settings: I set localshare=true and increased sharememsize to 4096 and
>memsize to 24
>The server is not overloaded.
>I have about 30 tables, 80% or them are read only. The main table has lots
>of reading & writing (reading via SQL local queries mostly), writing via
>searching on one of the indices

>I tried upgrading to BDE5.01, it didn't help..

I also tried switching to a meatier NT 4 server with 128MB ram on a 400MHz
CPU.
It makes no difference.

I've tried setting localshare=true or false. No help..

Now, when one user uses the app, it works fine. A second slows it
drastically.
When the second stops using the app it does not speed up, because the BDE
seems to keep some files open. If I go to the server and "disconnect" the
second user (via the server manager), the first user get back up to full
speed.

So it looks like BDE locking is causing the problem. Since the BDE doesn't
seem to be doing it's locking very well:
- is there a way of putting the BDE into "debugging" mode where it can
output detailed messages about all the operations it does? [Or is it wishful
thinking on my behalf?]
- how can I interrogate the BDE to know what records, tables etc are locked
by whom and how can I free up the locks?

This could get very hairy if I startprogramming the BDE directly...

Surely there are someone you have wrapped horn with paradox/BDE locking
before??

Thanks in advance,

Sean

Douglas Philli

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Sean,

Here's something you might try:
Use something like Visual FoxPro as the backend. One of the VFP case
studies had a system with several files, approx 10 mill recs each (If
my memory serves me right) and they were querying all w/ about 10sec
response time. If the BDE is on the client, working through ODBC and
VFP on the backend, you might just have a pretty speedy system there.
This is just my humble opinion. If someone else has had experience
with this and I am wrong, I would like to be corrected. I just know
from my experience developing in VFP that it is quite fast. If you
would like to contract me out to develop the backend, or for more
information, email me at solut...@removethis.fgi.net

Douglas G. Phillips, Owner
Simple Business Solutions

Custom Solutions to Meet Your Needs

On Fri, 15 Jan 1999 14:08:39 -0000, "Sean Boran"

Quote

<s...@boran.com.NOSPAM> wrote:

>>The problem:
>>==========
>>I have a D3.02 application that whizzes along when only one user is active,
>>but gets very (unusable) slow with two of more.

>>When it's slow a "last" can take 30 seconds (as opposed to instantanaeous
>in
>>single user). Also selecting a new index an "apply range" can take 30 secs
>>or more?

>And quickreports can take 30 minutes!

>>The setup:
>>========
>>The appllication is stored locally (win95), the paradox tables on an NT4
>>server, mounted via UNC. The main table is 1.2k/record with almost 5000
>>records. Not that big. A few tables are DBASE mounted readonly from another
>>application.
>>I have 3 secondary indices.

>>PrivDir is set to c:\temp, NET dir to a dedicated UNC on the server,
>>identical for all clients. All clients use the exact same Delphi app.
>>Client have between 32 & 64MB and are fast enough.
>>There are two BDE aliases, I set them from an ini file on start up.
>>All tables are close at design time and opened when the main form opens.
>>Queries are also closed as design time and closed and reopened at each
>>usage.
>>BDE settings: I set localshare=true and increased sharememsize to 4096 and
>>memsize to 24
>>The server is not overloaded.
>>I have about 30 tables, 80% or them are read only. The main table has lots
>>of reading & writing (reading via SQL local queries mostly), writing via
>>searching on one of the indices

>>I tried upgrading to BDE5.01, it didn't help..

>I also tried switching to a meatier NT 4 server with 128MB ram on a 400MHz
>CPU.
>It makes no difference.

>I've tried setting localshare=true or false. No help..

>Now, when one user uses the app, it works fine. A second slows it
>drastically.
>When the second stops using the app it does not speed up, because the BDE
>seems to keep some files open. If I go to the server and "disconnect" the
>second user (via the server manager), the first user get back up to full
>speed.

>So it looks like BDE locking is causing the problem. Since the BDE doesn't
>seem to be doing it's locking very well:
>- is there a way of putting the BDE into "debugging" mode where it can
>output detailed messages about all the operations it does? [Or is it wishful
>thinking on my behalf?]
>- how can I interrogate the BDE to know what records, tables etc are locked
>by whom and how can I free up the locks?

>This could get very hairy if I startprogramming the BDE directly...

>Surely there are someone you have wrapped horn with paradox/BDE locking
>before??

>Thanks in advance,

>Sean

Sean Bora

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Quote

Douglas Phillips wrote in message <36a26d05.7992...@news.fgi.net>...
>Sean,

>Here's something you might try:
>Use something like Visual FoxPro as the backend. One of the VFP case
>studies had a system with several files, approx 10 mill recs each (If
>my memory serves me right) and they were querying all w/ about 10sec
>response time.

Alot of effect has been put into the current implementation with Local SQL,
TTable, & Filters. I don't want to rewrite (time or expense).

Regards,

Sean

Dell Stinne

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Sean,
One of the things that has helped for us is to change the "MaxBufSize"
setting to 8092. Another thing we've found that affects the access
speed is whether there are multiple network protocols in use. TCP/IP
is faster than NetBeui but if you have both of them running it can
slow the system down. In our office we set up NetBeui for the network
cards on the workstations and the server and TCP/IP for dialup
networking only (not for the network cards). This has made a major
difference.

-Dell

On Fri, 15 Jan 1999 14:08:39 -0000, "Sean Boran"

Quote

<s...@boran.com.NOSPAM> wrote:

>>The problem:
>>==========
>>I have a D3.02 application that whizzes along when only one user is active,
>>but gets very (unusable) slow with two of more.

>>When it's slow a "last" can take 30 seconds (as opposed to instantanaeous
>in
>>single user). Also selecting a new index an "apply range" can take 30 secs
>>or more?

>And quickreports can take 30 minutes!

>>The setup:
>>========
>>The appllication is stored locally (win95), the paradox tables on an NT4
>>server, mounted via UNC. The main table is 1.2k/record with almost 5000
>>records. Not that big. A few tables are DBASE mounted readonly from another
>>application.
>>I have 3 secondary indices.

>>PrivDir is set to c:\temp, NET dir to a dedicated UNC on the server,
>>identical for all clients. All clients use the exact same Delphi app.
>>Client have between 32 & 64MB and are fast enough.
>>There are two BDE aliases, I set them from an ini file on start up.
>>All tables are close at design time and opened when the main form opens.
>>Queries are also closed as design time and closed and reopened at each
>>usage.
>>BDE settings: I set localshare=true and increased sharememsize to 4096 and
>>memsize to 24
>>The server is not overloaded.
>>I have about 30 tables, 80% or them are read only. The main table has lots
>>of reading & writing (reading via SQL local queries mostly), writing via
>>searching on one of the indices

>>I tried upgrading to BDE5.01, it didn't help..

>I also tried switching to a meatier NT 4 server with 128MB ram on a 400MHz
>CPU.
>It makes no difference.

>I've tried setting localshare=true or false. No help..

>Now, when one user uses the app, it works fine. A second slows it
>drastically.
>When the second stops using the app it does not speed up, because the BDE
>seems to keep some files open. If I go to the server and "disconnect" the
>second user (via the server manager), the first user get back up to full
>speed.

>So it looks like BDE locking is causing the problem. Since the BDE doesn't
>seem to be doing it's locking very well:
>- is there a way of putting the BDE into "debugging" mode where it can
>output detailed messages about all the operations it does? [Or is it wishful
>thinking on my behalf?]
>- how can I interrogate the BDE to know what records, tables etc are locked
>by whom and how can I free up the locks?

>This could get very hairy if I startprogramming the BDE directly...

>Surely there are someone you have wrapped horn with paradox/BDE locking
>before??

>Thanks in advance,

>Sean

Rob Dav

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

I think I'd start to look at the design rather than investigating
changing flags on the server. In other words load SQL Monitor, run
the app from the IDE and see what SQL is being sent to your databases,
you might find that using SQL with parameters and prepared queries is
faster than the sql generated by using setRange.

If your datastructures are stable then setting 'Enable Schema Cache'
to true can increase speed since the app no longer needs to send
information about the structure of the data it's going to send back.

Good luck and don't forget to post your findings. Having some answers
posted rather than questions could be a useful shift of emphasis for
the group.

Regards, Rob

Quote

"Sean Boran" <s...@boran.com.NOSPAM> wrote:

>Well, I tried

>- setting localshare=false, it's didn't make any noticeable difference.
>If 2 users access the system it gets very slow. To get fast again, both
>users need to stop the application & reboot..

>I appreciate any further help, I'm pretty stumped.

>Thanks,

>Sean

--------------------------------
email: Rob.Da...@unn.ac.uk
Web: http://www.unn.ac.uk/~isu8

Douglas Philli

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Sean,

You wouldn't really have to rewrite. The only thing you would have to
do is redo the tables in VFP, put that DB on the server, and use a
TDatabase component to connect. The BDE would only have to be on the
front-end (client).

The reason that I suggested VFP (or you could go with Interbase, SQL
Server, etc - Just that they're more expensive) is that the BDE and
paradox tables were really designed for local use, not client/server.
This makes it slower because of all the record locking etc. that they
were not designed for. Any DB that is designed for more interaction
as C/S, such as FoxPro, Access, and some others, would be able to
handle not only the current accessing needs but also as your company
grew.

Another note - If you want to contract me out to redo the DBs in VFP
5, I can do that. You can get some more information or talk to me
some more on the matter at solut...@fgi.net.

Douglas G. Phillips, Owner
Simple Business Solutions

Custom Solutions to Meet Your Needs

On Mon, 18 Jan 1999 09:57:20 -0000, "Sean Boran"

Quote

<s...@boran.com.NOSPAM> wrote:

>Douglas Phillips wrote in message <36a26d05.7992...@news.fgi.net>...
>>Sean,

>>Here's something you might try:
>>Use something like Visual FoxPro as the backend. One of the VFP case
>>studies had a system with several files, approx 10 mill recs each (If
>>my memory serves me right) and they were querying all w/ about 10sec
>>response time.

>Alot of effect has been put into the current implementation with Local SQL,
>TTable, & Filters. I don't want to rewrite (time or expense).

>Regards,

>Sean

Robert Cern

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Quote

>You wouldn't really have to rewrite. The only thing you would have to
>do is redo the tables in VFP, put that DB on the server, and use a
>TDatabase component to connect. The BDE would only have to be on the
>front-end (client).

>The reason that I suggested VFP (or you could go with Interbase, SQL
>Server, etc - Just that they're more expensive) is that the BDE and
>paradox tables were really designed for local use, not client/server.

Same states for FoxPro. It is file based and client has to load indexes,
lock records,....
Don't mislead people. The only real solution is SQL server and rewrite the
database logic part
of the application. You'll also learn a lot. Well, unless there is a very
short deadline.

Quote

>This makes it slower because of all the record locking etc. that they
>were not designed for. Any DB that is designed for more interaction
>as C/S, such as FoxPro, Access, and some others, would be able to
>handle not only the current accessing needs but also as your company
>grew.

Access?? It is much slower in local mode and forget it in C/S.

Quote

>Another note - If you want to contract me out to redo the DBs in VFP
>5, I can do that. You can get some more information or talk to me
>some more on the matter at solut...@fgi.net.

>Douglas G. Phillips, Owner
>Simple Business Solutions

>Custom Solutions to Meet Your Needs

Admit it. THAT'S the reason you're suggesting VFP.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Juergen Metzdor

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

Quote

Sean Boran wrote:

> Hi,

[ .. ]

Quote

> I have about 30 tables, 80% or them are read only. The main table has lots
> of reading & writing (reading via SQL local queries mostly), writing via
> searching on one of the indices

BDE 5.01 is very slow with Local SQL on Paradox Tables -> switch back to BDE
4.51 (10 times faster!!!)

cyal8er
Juergen

--
** Juergen "Cursor" Metzdorf ** Words of advice for young people **
** juer...@in-trier.de ** Avoid sympathy for normal people **
** Trier, Germany ** (Johan Sjerpstra) **

Douglas Philli

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

On Wed, 20 Jan 1999 01:45:40 GMT, "Robert Cerny"

Quote

<johnsm...@microsoft.com> wrote:

...

Quote

>Same states for FoxPro. It is file based and client has to load indexes,
>lock records,....
>Don't mislead people. The only real solution is SQL server and rewrite the
>database logic part
>of the application. You'll also learn a lot. Well, unless there is a very
>short deadline.

...

Quote

>Admit it. THAT'S the reason you're suggesting VFP.

No that's not. I don't want to start a big long thread about this,
but I was only offering my services. I am not greedy nor am I only
suggesting VFP. I did mention SQL server. If I worked in SQL server,
I would offer that, too. But I don't have any experience in SQL
server as yet. I was only meaning to help.

About Access - I was only going on the fact that I have seen several
suggestions to use Access as a front-end/back-end solution using two
separate files. I was not suggesting that it be used - only that it
is one of several options that **could** be used.

But, now that I have had my say on this, and since I have been
corrected (and I take my correction - live & learn), I end the
discussion. If you have anything more to say to me, you can email me
and discuss it in private.

Quote

>--
>----------------------
>Regards
>Robert Cerny
>Remove both qwe when replying
>email: robert.qwe.ce...@neosys.xrs.qwe.si

>No questions via email, unless explicitly invited.

dave bull

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Multiuser Paradox tables dog slow (D3.03, BDE 5.01)

On Tue, 12 Jan 1999 11:41:52 -0000, "Sean Boran"

Quote

<s...@boran.com.NOSPAM> wrote:
>Hi,

>I appreciate some help from the experience Pdox table users amoung you...

>The problem:
>==========
>I have a D3.02 application that whizzes along when only one user is active,
>but gets very (unusable) slow with two of more.

>When it's slow a "last" can take 30 seconds (as opposed to instantanaeous in
>single user). Also selecting a new index an "apply range" can take 30 secs
>or more?

>The setup:
>========
>The appllication is stored locally (win95), the paradox tables on an NT4
>server, mounted via UNC. The main table is 1.2k/record with almost 5000
>records. Not that big. A few tables are DBASE mounted readonly from another
>application.
>I have 3 secondary indices.

>PrivDir is set to c:\temp, NET dir to a dedicated UNC on the server,
>identical for all clients. All clients use the exact same Delphi app.
>Client have between 32 & 64MB and are fast enough.
>There are two BDE aliases, I set them from an ini file on start up.
>All tables are close at design time and opened when the main form opens.
>Queries are also closed as design time and closed and reopened at each
>usage.
>BDE settings: I set localshare=true and increased sharememsize to 4096 and
>memsize to 24
>The server is not overloaded.
>I have about 30 tables, 80% or them are read only. The main table has lots
>of reading & writing (reading via SQL local queries mostly), writing via
>searching on one of the indices

>I tried upgrading to BDE5.01, it didn't help..

>What am I missing? Any ideas? It's really getting frustrating!

>I would really appreciate help on this one.....

>Thanks in advance,

>Sean Boran

I am really puzzled by what you say Sean. I have almost exactly the
same set-up with 6 users and about the same size tables. But mine are
all Paradox, not mixed with DBase. The speed is remarkably fast. If
any workstation has to wait more than 1 second. I study it carefully
as to why. My tables are not read-only. I use tables rather than SQL
because I find them quicker unless you only want a much restricted
number of fields.

I store all auxiliary data that will not change during the day down to
the workstation at startup. Where I have a 'Local' database. Perhaps
the network is misbehaving and taking forever to send the data around.

Go to page: [1] [2]

Other Threads

1. Corrupted tables using SQL functions with Paradox tables and BDE 5.01

2. DbiAddAlias D3/BDE 5.01/Paradox - How create et permanent Alias

3. D3.02 / BDE 5.01 support of MSAccess tables

4. BDE 5.01, Paradox 7 table and Fat32

5. BDE 5.01 slower than BDE 451 !

6. BDE 5.01 Slower Than BDE 5.00??

7. PDXStream V1.03 Released - Native Paradox Driver (No BDE, DLLs)

8. Slow performance using BDE 5.01 with some language drivers

9. BDE 5.01 Query much slower than 4.00

10. To TeamB - Query with BDE 5.01 slow