Hi,

I have some performance problems with an app when more than one user is active. I know about the Opportunistic Locking but I am not sure if this is the problem here.

When user A opens app, a query takes 1 second.
If user B opens app and runs the query, it takes 24 seconds. I know this can be because of Opportunistic Locking.
But if user B in addition opens a second copy of the same datatable, he is the only user of this table and the query should be finsished in 1 second. But it still uses 24 seconds.
Then he opens a third copy of the same datatable. But this time the table is located in another folder. Now the query is finished in 1 second.

Why? I have read multible threads regarding multiuser performance and Opportunistic Locking but have not found any explaining this. Is there a way of solving this? For performance I have organized the database with several different tables and normally there is only one user accesing each datatable.

Regards
Jon R.

Hi Jon,
Your left out some important details. Which user A or B was opening the db
on the local hard disk and which one was opening it on a shared network
folder? Also what was the speed of the same query on user A after user B had
already opened the DB ?

<<But this time the table is located in another folder. Now the query is
finished in 1 second.>>
Again you didn't say whether this DB was local hard disk or network share ?

Regards
Gregory Sebastian

Hi Gregory,

The db is on a Windows 2003 server. Both user A and B is opening the db on the remote shared network drive. It makes no difference if A or B is opening the DB first. When there is only one user the performance is ok, when there is two users the performance is bad for both of them. But if we also open the db directly on the server (locally) the performance is good for the user sitting on the server even i other users also is connected to the db.

I have just done some more testing now, both users are using the remote shared network drive:
1. User A connects the dbisamdatabase and opens a table. He traverses the table. Performance is ok. (1 sec)
2. User B starts a little testprogram with only a dbisamdatabase component, nothing else. He connects the dbisamdatabase. The directory property is the same for user A and user B.
3. User A traverses the table again. He is still the only user of this table, but now the performance is bad. (14 sec). He is only reading the table, no editing.

Is there anything we can do here?

Best regards, Jon R.

Jon,

<< When user A opens app, a query takes 1 second. >>

Yes, and if user A is the only one using the table(s) referenced in the
query, then this is normal for OpLocks.

<< If user B opens app and runs the query, it takes 24 seconds. I know this
can be because of Opportunistic Locking. >>

Correct.

<< But if user B in addition opens a second copy of the same datatable, he
is the only user of this table and the query should be finsished in 1
second. But it still uses 24 seconds. >>

Are you opening the second copy in the same process, or in a new process ?
If the latter, then the OpLocks will probably be broken by the Windows
network redirector.

<< Then he opens a third copy of the same datatable. But this time the table
is located in another folder. Now the query is finished in 1 second. >>

It's a different set of table files, thus the user is the only user
accessing the table files, and OpLocks will still be in effect.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Jon,
<< Is there anything we can do here?>>
The one thing that you must confirm is whether all your tables are properly
optimized with indexes. If your tables/ queries have lookups or joins and
they are not properly indexed on the key field you will get very poor
performance even just to move from one record to the next . Sometimes its
not very noticeable on small tables with few records, local/ single user
access to DB etc but it gets very significant when tables are larger, slower
networks and multi-user access. If the join/ lookup is on a text field, it
MUST be a "Case Sensitive" index.

<<The db is on a Windows 2003 server.>>
Unfortunately I don't know if there's any tweaking etc that can be done on
the server to help when more than one user opens the db. Hopefully someone
else can help with this. I the days of Windows NT 4.0, I think there were
some registry tweaks that were useful but I'm not sure if it applies to the
modern Windows Servers.

Regards
Gregory Sebastian