In ADO.NET, the usual best-practice for DbConnection objects is to create
them only when needed and then dispose of them immediately afterward. e.g.

EDBConnection con = new EDBConnection(my_connection_str);
try
{
   // do some stuff with con...
}
finally
{
   con.Dispose();
   con = null;
}

The idea of is that the server engine itself will implement connection
pooling and will 'intelligently' re-use or drop connections.

However, I'm using EDB in local mode (i.e. direct file access) so there
isn't actually a server process to manage the connections. Is there some
level of connection pooling implemented in the EDB ADO.NET classes (or
indeed in the EDB engine itself) or do I have to think more carefully about
how I use the EDBConnection object in my code?

The reason I ask this is that some time ago I spent a lot of time and effort
with VistaDB (www.vistadb.com). BIG MISTAKE. The performance was (and
judging from the activity on their forums, still is) pretty dire.

(As an aside, it's not entirely their fault - they actually have very
talented developers working there. The problem is that the entire engine is
written in managed code and runs *inside* the .NET framework, which simply
doesn't lend itself to decent performance).

Anyway, because VistaDB is also file-based engine, the recommendation they
made was to have a global (persistent) DbConnection object and leave it open
most of the time, perhaps just closing it during periods of inactivity.

Everytime a VistaDBConnection was opened, the engine acquired the necessary
various file handles (to the database files) and everytime it was closed it
then released all the file handles. Thus, continually creating/disposing the
VistaDBConnection object (as per ADO.NET best-practice) had a massive impact
on performance.

Are there any similar considerations to bear in mind with regard to the
EDBConnection object in LOCAL access mode?

Thanks,

Hedley

Hedley,

<< The idea of is that the server engine itself will implement connection
pooling and will 'intelligently' re-use or drop connections.

However, I'm using EDB in local mode (i.e. direct file access) so there
isn't actually a server process to manage the connections. Is there some
level of connection pooling implemented in the EDB ADO.NET classes (or
indeed in the EDB engine itself) or do I have to think more carefully about
how I use the EDBConnection object in my code? >>

No, you'll want to instantiate one or more connections as necessary, and
then keep them open as long as necessary.  Constantly opening and closing
them will kill performance.

You should keep in mind one thing when dealing with the "best practices"
recommendations from Microsoft regarding ADO.NET and data access:

They have designed ADO.NET pretty much specificallty for *one* specific
class of applications - web applications that are hitting one more database
servers from ASP applications that only use cached, forward-only result
sets.   This why they got rid of bi-directional cursors in ADO.NET, why they
require custom pagination to be implemented in SQL in a lot of cases, why
they always discuss connection pooling in the context of database
connections, etc.

We get around a lot of this via our bi-directional EDBDataCursor class, and
by adding functionality that makes things much closer to the TDataSet
architecture.  But, all in all, Microsoft has *not* designed ADO.NET for
traditional database applications that run directly on the OS and access
database tables in a "live" fashion, and you have to keep this in mind when
dealing with their recommendations.

<< The reason I ask this is that some time ago I spent a lot of time and
effort with VistaDB (www.vistadb.com). BIG MISTAKE. The performance was (and
judging from the activity on their forums, still is) pretty dire.

(As an aside, it's not entirely their fault - they actually have very
talented developers working there. The problem is that the entire engine is
written in managed code and runs *inside* the .NET framework, which simply
doesn't lend itself to decent performance). >>

The ElevateDB .NET Data Provider is 100% managed code also, so you should be
able to get a decent comparison between the two by simply comparing them
with similar operations.

<< Anyway, because VistaDB is also file-based engine, the recommendation
they made was to have a global (persistent) DbConnection object and leave it
open most of the time, perhaps just closing it during periods of inactivity.
>>

There really is no reason to ever close such a connection with ElevateDB,
except at application shutdown.  There's no downside to leaving it open with
a single-user application (or a C/S application, for that matter).  Again,
the MS recommendation is geared around applications that are hitting a
database server where connections are scarce or are in high-demand and need
to be freed as quickly as possible.

<< Everytime a VistaDBConnection was opened, the engine acquired the
necessary various file handles (to the database files) and everytime it was
closed it then released all the file handles. Thus, continually
creating/disposing the
VistaDBConnection object (as per ADO.NET best-practice) had a massive
impact on performance. >>

Yes, that would kill the performance pretty effectively.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley-

> EDBConnection con =3D new EDBConnection(my_connection_str);
> try
> {
>    // do some stuff with con...
> }
> finally
> {
>    con.Dispose();
>    con =3D null;
> }

If you are using .Net 2.0 or later, you'll save yourself a heap of grief =
and typing by using the USING statement where possible. It calls the =
dispose method, and cleans up after a finally in a try catch.Your sample =
above would look like this in C#:'
Using( EDBConnection con =3D new EDBConnection(my_connection_str))
{
   try
    {
       // do some stuff with con...
   }
   catch (system.exception ex) { throw ex;}    (i added the catch 4u)
}



> However, I'm using EDB in local mode (i.e. direct file access) so =
there=20
> isn't actually a server process to manage the connections. Is there =
some=20
> level of connection pooling implemented in the EDB ADO.NET classes (or =

> indeed in the EDB engine itself) or do I have to think more carefully =
about=20
> how I use the EDBConnection object in my code?

This is what I did to compensate for no connection pooling: I open a =
connection and keep it open for the duration of the app as the app =
launches. All data tiers call that connection but never closes it. The =
EDB .Net data provider seems to be smart enough to only close the =
connections they open.If the connection was already open, it leaves it =
open. I close, dispose & nullify the connection as the app closes.

>=20
> The reason I ask this is that some time ago I spent a lot of time and =
effort=20
> with VistaDB (www.vistadb.com). BIG MISTAKE. The performance was (and=20
> judging from the activity on their forums, still is) pretty dire.

I too am a former VistaDB customer. Quite happy with EDB instead. My =
issues are with them claiming to be seamlessly scalable up to MS SQL... =
Their stored procs syntax isn't compatible at all.

Hope some of my 2 cents has a benefit to you.


jr

Hi James,

Yes, I always use USING myself. In the example code, I showed =
try...finally because I know most developers here are primarily Delphi =
enthusiasts and I don't think there's a USING equivalent in Delphi.

Thanks for the tip though!

Hedley