Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread Local database connection speed issue
Mon, Jun 4 2007 1:13 AMPermanent Link

Kerry Neighbour

I am using DBSAIM 4.25 build 4 with Delphi 7.

I have an application that can connect to a database via Remote or Local
settings - ie to a mapped drive path or to a DBISAMServer. The DBISAMServer
connection works just fine. The Local connection does not. The problem is
complicated, but it was reported by a customer, and I have confirmed the
problem here.

The problem is that one user can connect to a database with no problems.
When a second user connects, the first user becomes VERY slow. The second
user also slows down. Even is User B disconnects, USer A remains slow. User
A has to quit and restart to get back to his normal speed.

This is on a local network. User A is on one computer. User B is on another
computer. The database is on yet another computer and that path is mapped
to a common drive (ie something like G:\dbisam\data). Each user runs the
application on the local machine, and simply points to the mapped database
path.


To give you some actual figures, here are some measured times

User A starts up - runs the query : 26 seconds

User B starts up (do not do anything, do not run query, but connect using
DBISAMDatabase)

User A runs same query again - query is now 90 seconds!

User B quits.

User A runs same query again - still 90 seconds

User A quits. Then starts again.

User A runs same query again - back to 26 seconds

I tried this with DBSYS, and it does not have the same problem. Which is
good - it means that it is something that I can probably fix!

What I do im my application is use a single DBISAMDatabase component, connected
to a DBISAMSession component. Everything else simply connects to these components.
Nothing unusual, I would think.

With this same application, I can connect to a remote DBISAMServer with no
problems - ie it does not have this problem (as you would expect).

One funny thing is that once User A slows down, it stays slow. Which is weird.
Simply disconnecting the DBISAMDatabase component does not fix the problem.
You actually have to quit the application.

Anyone have any ideas about what I could try?

Mon, Jun 4 2007 1:35 AMPermanent Link

Kerry Neighbour
One thing I note from looking at DBSYS is that you do not use a Session component
when you connect to a Local database. You use the internal .Session value.
But then, you do not use a DBISAMDatabase component either.

I use a DBISAMDatabase component (on the Datamodule form), connected to a
DBISAMSession component. I simply switch the Session component from Local
to Remote as required. This works, but could this be the cause of the speed
issue?

Mon, Jun 4 2007 1:35 AMPermanent Link

Kerry Neighbour
One thing I note from looking at DBSYS is that you do not use a Session component
when you connect to a Local database. You use the internal .Session value.

I use a DBISAMDatabase component (on the Datamodule form), connected to a
DBISAMSession component. I simply switch the Session component from Local
to Remote as required. This works, but could this be the cause of the speed
issue?

Mon, Jun 4 2007 1:35 AMPermanent Link

Kerry Neighbour
One thing I note from looking at DBSYS is that you do not use a Session component
when you connect to a Local database. You use the internal .Session value.

I use a DBISAMDatabase component (on the Datamodule form), connected to a
DBISAMSession component. I simply switch the Session component from Local
to Remote as required. This works, but could this be the cause of the speed
issue?

Mon, Jun 4 2007 2:48 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Kerry


If you search these newsgroups you'll find a number of posts about this issue. Essentially its down to Windows. Memory says its how Windows treats file locking. One user and its not a problem two or more and the file locking issue kicks in. The good news is that the performance slowdown after the second user is low.

I can't remember is there was any solution posted. Sorry.

Roy Lambert
Mon, Jun 4 2007 2:44 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Kerry,

<< The problem is that one user can connect to a database with no problems.
When a second user connects, the first user becomes VERY slow. The second
user also slows down. Even is User B disconnects, USer A remains slow. User
A has to quit and restart to get back to his normal speed. >>

What you're seeing is opportunistic locking in action.  The speed when both
users are connected is the real network speed.  The speed with only one user
connected is an "exclusive" mode access which uses caching and does not
actually represent reality.

http://www.elevatesoft.com/bulletin_6.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Jun 4 2007 7:23 PMPermanent Link

Kerry Neighbour
>
> What you're seeing is opportunistic locking in action.  The speed when
> both users are connected is the real network speed.  The speed with
> only one user connected is an "exclusive" mode access which uses
> caching and does not actually represent reality.
>
> http://www.elevatesoft.com/bulletin_6.htm
>

Thanks for the heads-up. I have tried the registry changes mentioned by Microsoft,
but they do not seem to do anything. It seems a real problem and not one
easily solved.

It is a real selling point for the client/server version, of course.

Mon, Jun 4 2007 8:01 PMPermanent Link

"Robert"

"Kerry Neighbour" <kerry@dojitraders.com> wrote in message
news:639022cae2cf8c97569f5a454ce@elevatesoft.com...
>>
>> What you're seeing is opportunistic locking in action.  The speed when
>> both users are connected is the real network speed.  The speed with
>> only one user connected is an "exclusive" mode access which uses
>> caching and does not actually represent reality.
>>
>> http://www.elevatesoft.com/bulletin_6.htm
>>
>
> Thanks for the heads-up. I have tried the registry changes mentioned by
> Microsoft, but they do not seem to do anything. It seems a real problem
> and not one easily solved.
>

It's unsolvable. When you only run one user, M$ knows not to bother with all
the checking necessary to process multi-user correctly, and your program
runs much faster. I hate to admit it, but it was a clever feature on the
part of M$, since there are many single-user applications running on
servers. But of course the moment you have more than one user, you lose that
advantage.

> It is a real selling point for the client/server version, of course.

My experience is that DBISAM CS is not that much faster until you get to a
fairly large number of concurrent users. But I'm sure it depends on the
application.

Robert

>

Mon, Jun 4 2007 10:54 PMPermanent Link

Gregory Sebastian
Hi Kerry

Kerry Neighbour <kerry@dojitraders.com> wrote:
<<One thing I note from looking at DBSYS is that you do not use a Session component
when you connect to a Local database. You use the internal .Session value.
But then, you do not use a DBISAMDatabase component either.

I use a DBISAMDatabase component (on the Datamodule form), connected to a
DBISAMSession component. I simply switch the Session component from Local
to Remote as required. This works, but could this be the cause of the speed
issue?>>

Just curious but where does your PrivateDir in the session component point to ? Does it point to a local path or to the network folder. If the same query under the same conditions runs much
faster on DBSys then on your app then this could be the problem.

regards
gregory
Image