I am testing my current application using D7 and DBISAM c/s 4.25 build 2
using various size databases. I am connecting using local and remote connections.

I find that a remote connection to a large database (500,000 records) fails.
I can connect ok, and I can do fast queries with no problems. When I run
a query that takes more than about 30 seconds, I get a DBISAMSessionRemoteTimeout
event triggered. I have tried to set the StayConnected variable to true,
but this does not do anything that I can see. What basically happens at this
point is that the application locks up - I have to kill it from the task
manager.

Everything is ok as long as I don't run into the remote timeout problem,
it seems. I have the Ping option turned on. I have the timeout period set
to 60. The Ping interval was 60, and is now 30.

I can look on the DBSAIM Server, and it still shows my client as being connected.

This particular query takes around 5+ minutes on a local database, which
is shockingly slow also. But that is another issue that I am looking into.

But I cannot have the remote database disconnect itself like this. The user
has to quit my application and restart it to get connected again, which is
not very good. And of course it should not be timing out anyway.

Any ideas?

Kerry,

<< I find that a remote connection to a large database (500,000 records)
fails. I can connect ok, and I can do fast queries with no problems. When I
run a query that takes more than about 30 seconds, I get a
DBISAMSessionRemoteTimeout event triggered. I have tried to set the
StayConnected variable to true, but this does not do anything that I can
see. What basically happens at this point is that the application locks up -
I have to kill it from the task manager. >>

How long are you waiting for the query to complete ?  IOW, are you sure that
the query is complete when you determine that the application has locked up
?   Setting the StayConnected parameter to the RemoteTimeout event should do
the trick as far as waiting indefinitely for the query to complete.

--
Tim Young
Elevate Software
www.elevatesoft.com

As I say, the DBISAMSessionRemoteTimeout event is triggered. I do not have
to wait for anything. This is with StayConnected as false (the default).


As an experiment, when I made it true, I waited a few minutes, then gave
up. It should return in much less time - at least it should start the OnProgress
event, which it does not seem to. It does not even start the progress - which
I feel only starts at 5% anyway. Perhaps it was taking more than a couple
of minutes to get to 5%? The table I am querying is only some 35,000 record
long, so it should be fairly fast.

The DBISAMSessionRemoteTimeout event is triggered every 90 seconds (I have
a ShowMessage in the event). It has triggered at least 6 times now, and I
am giving up and I am killing my application.

Locally, this query runs in around 50 seconds.


But the point is - it should NEVER timeout. This is not a timeout problem.
The server is still there, and I am still connected to it. The thing is I
have not received the answer to my query in the timeout period - I have NOT
disconnected. It is simply that the query is taking longer than the system
thinks is reasonable. To my mind, that is NOT a timeout problem - it is simply
a slow query. Perhaps you should have a background idler thread that monitors
the connection, or something. I do not see how you can set the timeout period
to (say) 60 seconds, and then assume that all queries will take less than
60 seconds.

Should I set my timeout period to (say) 30 minutes?

I presume that this is a recent problem. I ran these same queries on this
same database way back when I was first evaluating DBISAM for this project.
It worked ok then (otherwise I would not have used DBISAM). I would have
been using an earlier version, but still V4.


>
> << I find that a remote connection to a large database (500,000
> records) fails. I can connect ok, and I can do fast queries with no
> problems. When I run a query that takes more than about 30 seconds, I
> get a DBISAMSessionRemoteTimeout event triggered. I have tried to set
> the StayConnected variable to true, but this does not do anything that
> I can see. What basically happens at this point is that the
> application locks up - I have to kill it from the task manager. >>
>
> How long are you waiting for the query to complete ?  IOW, are you
> sure that the query is complete when you determine that the
> application has locked up ?   Setting the StayConnected parameter to
> the RemoteTimeout event should do the trick as far as waiting
> indefinitely for the query to complete.
>

Kerry,

<< As I say, the DBISAMSessionRemoteTimeout event is triggered. I do not
have to wait for anything. This is with StayConnected as false (the
default). >>

If you're setting the StayConnected parameter to False, then you're
effectively killing the query at that point since the session will then
disconnect.   You would have then have to retry the operation in order to
have it succeed.  Of course, if the query takes the same amount of time,
then you'll also have to set the StayConnected parameter to True or else
you'll run into the same problem again.

<< As an experiment, when I made it true, I waited a few minutes, then gave
up. It should return in much less time - at least it should start the
OnProgress event, which it does not seem to. It does not even start the
progress - which
I feel only starts at 5% anyway. Perhaps it was taking more than a couple of
minutes to get to 5%? The table I am querying is only some 35,000 record
long, so it should be fairly fast. >>

The number of records doesn't mean anything if the query is constructed in a
way as to make it very slow, such as is the case with joins that can't use
indexes or cross-joins that generate cartesian products of all involved
tables.  Are you sure that you're using the correct tables with the proper
indexes with the server so that the query is properly optimized ?

<< The DBISAMSessionRemoteTimeout event is triggered every 90 seconds (I
have a ShowMessage in the event). It has triggered at least 6 times now, and
I am giving up and I am killing my application. >>

Okay, then the connection timeout processing is working okay.  The query is
simply taking a very long time to execute.

<< But the point is - it should NEVER timeout. This is not a timeout
problem. >>

Of course it should.  If there is an issue with the server or network
connection, you most certainly want the option to disconnect and retry the
operation or close the application normally.  The client session has no idea
whether the operation is just taking a long time or whether there has been
an issue with the server or network connection.

<< The server is still there, and I am still connected to it. The thing is I
have not received the answer to my query in the timeout period - I have NOT
disconnected. It is simply that the query is taking longer than the system
thinks is reasonable. To my mind, that is NOT a timeout problem - it is
simply a slow query. Perhaps you should have a background idler thread that
monitors the connection, or something. >>

Monitors the connection for what purpose(s) ?

<< I do not see how you can set the timeout period to (say) 60 seconds, and
then assume that all queries will take less than 60 seconds. >>

We don't, which is why we have the OnRemoteTimeout event handler so that you
can decide how to proceed in your application as you see fit.  Or you can
just bump up the TDBISAMSession.RemoteTimeout property as necessary.

<< Should I set my timeout period to (say) 30 minutes? >>

It's up to you.  Personally, I would not recommend it.  Most queries
*should* execute within a minute or so.  If they don't, then you've got some
optimizing to do.

<< I presume that this is a recent problem. I ran these same queries on this
same database way back when I was first evaluating DBISAM for this project.
It worked ok then (otherwise I would not have used DBISAM). I would have
been using an earlier version, but still V4. >>

DBISAM 3.x and 4.x, i.e. all C/S versions of DBISAM, all behaved in the same
manner with respect to session timeouts when waiting for a response from the
database server.  The only difference is that 4.x allows you to respond to
the timeout situation via the OnRemoteTimeout event handler.

--
Tim Young
Elevate Software
www.elevatesoft.com

> The number of records doesn't mean anything if the query is
> constructed in a way as to make it very slow, such as is the case with
> joins that can't use indexes or cross-joins that generate cartesian
> products of all involved tables.  Are you sure that you're using the
> correct tables with the proper indexes with the server so that the
> query is properly optimized ?

The query is fully optimised (I put up the Plan in another thread). I will
pursue that aspect in that thread.


> << But the point is - it should NEVER timeout. This is not a timeout
> problem. >>
>
> Of course it should.  If there is an issue with the server or network
> connection, you most certainly want the option to disconnect and retry
> the operation or close the application normally.  The client session
> has no idea whether the operation is just taking a long time or
> whether there has been an issue with the server or network connection.

Fair enough.

>
> Monitors the connection for what purpose(s) ?

To differentiate between a lost connection, and a slow query. If I can (say)
ping the server every 30 seconds, then I have not lost connection. If the
ping still works, then I should not get a timeout error.

My problem now (apart from trying to get the query faster), is what to do
with the Timeout event. When I run the query, I disable most of my application.
This error does not return me to the application, so I basically have an
application that has to be killed from the task manager.

So - how do I handle it? Do I cancel the query, and return to my application
somehow? Or do I show a retry/abort dialog? Do I try a disconnect/reconnect?

The thing is - even if we can get this particular query down to less than
60 seconds here on my computer - there is no guarantee that on any furture
day, on another computer, it will not take 31 seconds (or 10 minutes). It
is likely, in fact.


> It's up to you.  Personally, I would not recommend it.  Most queries
> *should* execute within a minute or so.  If they don't, then you've
> got some optimizing to do.
>

Not always. Some queries simply are slow due to the number of records or
the query structure. Some of mine are VERY slow, to the extent that I enforce
the TOP nnn command.

> DBISAM 3.x and 4.x, i.e. all C/S versions of DBISAM, all behaved in
> the same manner with respect to session timeouts when waiting for a
> response from the database server.  The only difference is that 4.x
> allows you to respond to the timeout situation via the OnRemoteTimeout
> event handler.

Well, all I can say is that I have never seen this timeout problem before,
and I have run this particular query hundreds of times. When I first evaluated
DBISAM for this project, I compared it with MySQL, DBISAM local and DBISAM
remote. I still have my timing sheets for these exact same queries. BTW,
I found MySQL to be a lot faster than DBISAM, but that is another story!

I guess I will have to trap the event and get the user to retry/abort.

Kerry,

<< To differentiate between a lost connection, and a slow query. If I can
(say) ping the server every 30 seconds, then I have not lost connection. If
the ping still works, then I should not get a timeout error.  >>

Yes, but the issue is that we only have one connection to the server.  If
the current connection is waiting on a response from the server, and the
server session (and thread) is busy doing something, then a ping will not do
any good since it won't get a response either.

<< My problem now (apart from trying to get the query faster), is what to do
with the Timeout event. When I run the query, I disable most of my
application. This error does not return me to the application, so I
basically have an
application that has to be killed from the task manager.

So - how do I handle it? Do I cancel the query, and return to my application
somehow? Or do I show a retry/abort dialog? Do I try a disconnect/reconnect?
>>

My recommendation is to do what we do with DBSYS - display a dialog to the
user that says that the application hasn't heard back from the server in X
number of seconds, and ask the user if they want to keep waiting or to
disconnect.

<< The thing is - even if we can get this particular query down to less than
60 seconds here on my computer - there is no guarantee that on any furture
day, on another computer, it will not take 31 seconds (or 10 minutes). It is
likely, in fact. >>

Well, you're always free to increase the RemoteTimeout property for the
session.  I just recommend against increasing it to very large values, such
as a 1/2 hour.

<< Not always. Some queries simply are slow due to the number of records or
the query structure. Some of mine are VERY slow, to the extent that I
enforce the TOP nnn command. >>

TOP may not be buying you anything in a lot of cases.   See this thread:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=10&msg=2128&page=1#msg2128

<< Well, all I can say is that I have never seen this timeout problem
before, and I have run this particular query hundreds of times. When I first
evaluated DBISAM for this project, I compared it with MySQL, DBISAM local
and DBISAM remote. I still have my timing sheets for these exact same
queries. BTW, I found MySQL to be a lot faster than DBISAM, but that is
another story! >>

That's a very general statement about performance, so I really can't comment
on it.  If you were to show me a specific query and/or query execution plan,
then I could comment further on what DBISAM is doing.

--
Tim Young
Elevate Software
www.elevatesoft.com