SQL Server vs Firebird (speed performance)

azerty · **Posted:** Wed Oct 19, 2005 8:35 am

I'm writing an application which runs under SQL server 2000 database and Firebird database (embedded server or not).

I remark that when I use the Firebird database, the application runs slowlier (significantly).

I use the same source code, the only thing I change is the hbm xml.
For SQL Server :

Code:

For Firebird :

Code:

<generator class="sequence">
  <param name="sequence">"SocieteGenerator"
  </param>
</generator>

Does anyone had a matter as this and know where I can search to resolve it ?

azerty · **Posted:** Wed Oct 19, 2005 10:22 am

As I read at this forum, I open session, do job and close session immediatly. It seems to be this that take a long time on Firebird because if I open session only at the start of the application, the speed is equal to SQL Server.

Is it THE solution ?

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

At least it indicates that opening connections with Firebird appears to be slow or there is no connection pooling.
Maybe there are some tweaks around to speed up making connections.

benster · **Joined:** Sat Apr 17, 2004 1:11 am **Posts:** 36

I manage my own database connections and use them when creating ISession instances so that the session creation overhead is next to nothing. Sounds like this would help you tremendously...

benster

azerty · **Posted:** Thu Oct 20, 2005 2:05 am

Could you give me an example of your code ?

benster · **Joined:** Sat Apr 17, 2004 1:11 am **Posts:** 36

It's pretty straightforward - just keep and manage one or more (if you would like to pool them) connections in a Singleton or static class that also contains the SessionFactory. You would wrap the connection in an object so that you can mark it as being in use or not. Calling GetSession() on that class would use an unused connection in calling the SessionFactory to open an ISession. A CloseSession() on your static class would close the ISession and mark the connection as not being in use so that it's available for the next caller. I use a growable pool and plan to implement a timeout on the connections so that they get closed after a configurable time period so that the pool doesn't run away.

HTH...

benster

Quicoli · **Posted:** Thu Oct 20, 2005 11:19 am

Benster, you said:

Quote:

Calling GetSession() on that class would use an unused connection in calling the SessionFactory to open an ISession. A CloseSession() on your static class would close the ISession and mark the connection as not being in use so that it's available for the next caller.

i've searching something about pooling in Firebird database and i found this example:

Code:

public static void Main(string[] args)
{
    // Set the ServerType to 1 for connect to the embedded server
    string connectionString =
        "User=SYSDBA;"                  +
        "Password=masterkey;"           +
        "Database=SampleDatabase.fdb;"  +
        "DataSource=localhost;"         +
        "Port=3050;"                    +
        "Dialect=3;"                    +
        "Charset=NONE;"                 +
        "Role=;"                        +
        "Connection lifetime=15;"       +
        "Pooling=true;"                 +
        "MinPoolSize=0;"                +
        "MaxPoolSize=50;"               +
        "Packet Size=8192;"             +
        "ServerType=0";

    FbConnection myConnection1 = new FbConnection(connectionString);
    FbConnection myConnection2 = new FbConnection(connectionString);
    FbConnection myConnection3 = new FbConnection(connectionString);

    try
    {
        // Open two connections.
        Console.WriteLine ("Open two connections.");
        myConnection1.Open();
        myConnection2.Open();

        // Now there are two connections in the pool that matches the connection string.
        // Return the both connections to the pool. 
        Console.WriteLine ("Return both of the connections to the pool.");
        myConnection1.Close();
        myConnection2.Close();

        // Get a connection out of the pool.
        Console.WriteLine ("Open a connection from the pool.");
        myConnection1.Open();

        // Get a second connection out of the pool.
        Console.WriteLine ("Open a second connection from the pool.");
        myConnection2.Open();

        // Open a third connection.
        Console.WriteLine ("Open a third connection.");
        myConnection3.Open();

        // Return the all connections to the pool.  
        Console.WriteLine ("Return all three connections to the pool.");
        myConnection1.Close();
        myConnection2.Close();
        myConnection3.Close();
    }
    catch(Exception e)
    {
       Console.WriteLine(e.Message);
    }
}

So, in Firebird:

* The connection pools are created per connection string. If you modify the connection string a new connection (and a pool) will be created.
* The connection is returned to the pool when calling Close() method of FbConnection.
* When you use connection pooling it is better to open the connection as late as possible and close as soon as possible.

How NHibernate handle this in the Core ? Or, how approach would make firebird create ISession faster ?

Thanks ![/b][/quote]

benster · **Joined:** Sat Apr 17, 2004 1:11 am **Posts:** 36

The app I'm working on is not specific to Firebird but we may support it. My comments were meant to be general in nature and a technique that I think would work across the board without regard for what database you're connected to. Plus it's a WinForms app and I don't necessarily want the DB to handle the pooling due to the usage scenarios for the app. That's why my implementation is growable so that if do have to open more than one connection for an application run I can recover the connections after a period of time because I manage them.

As far as your specific questions go, I would assume that if you use pooling in your connection string and use a single configured SessionFactory which would mean a single connection string that you can let Firebird handle the pooling for you and you'd see some benefits after the connections have been created initially. I don't think NH cares about where the connections come from - it asks for an IDbConnection using the connection string or uses the one you pass in. How you setup the connection string is up to you. So if Firebird gives NH a pooled connection you wouldn't incur that overhead at all.

In short, I'd try pooling in the connection string and see what performance benefits you get and let us know...

benster

azerty · **Posted:** Fri Oct 21, 2005 5:52 am

I always don't know how to resolve my matter.
I will give you more details.

When I want to access data, the source code is as following.

Code:

public void doTreatement(object o)
{
   prepareSessionX();
   doJob(); // ex : session.Delete(o);
   unprepareSessionX();
}

To prepare or unprepare the session, I first use the following code because I read that it is better to open, do job and close the session.

Code:

//////////////////////////////////////
// FIRST
// performance : good with SQL Server, very slow with Firebird
// error : none
private void prepareSession1()
{
   session = factory.OpenSession();
}
private void unprepareSession1()
{
   if (session != null)
      session.Close();
}

Because of the very slowness of this code with Firebird, I try to keep the connection as a static member of the class to use only one connection.

Code:

//////////////////////////////////////
// SECOND
// performance : good with SQL Server, very slow with Firebird
//  => the session.Close() function close the connection
// So, the connection.Open() is always executed.
// error : none
private void prepareSession2()
{
   if (session == null)
   {
      session = factory.OpenSession();
      connection = session.Connection;
   }
   else
   {
      if (connection.State != System.Data.ConnectionState.Open)
         connection.Open();
      session = factory.OpenSession(connection);
   }
}
private void unprepareSession2()
{
   if (session != null)
      session.Close(); // but this close the connection
}

When I remark that the connection is closed when session.Close() is called, I decided not to close the session but only disconnect it.

Code:

//////////////////////////////////////
// THIRD
// performance : good with SQL Server and Firebird
// => fast because the session is always the same
// and keep track of last objects load.
// error : because the session is always the same, 
// the session keep track of deleted objects for example
// and an exception is thrown.
private void prepareSession3()
{
   if (session == null)
   {
      session = factory.OpenSession();
   }
   else
   {
      session.Reconnect();
   }
}
private void unprepareSession3()
{
   if (session != null)
   {
      session.Disconnect(); // but this close the connection
   }
}

What is the best way to manage session ?
Thanks.

benster · **Joined:** Sat Apr 17, 2004 1:11 am **Posts:** 36

The connection is not closed when a session is closed if you passed it in to OpenSession() yourself.

Again - I'd try a few things to keep the session from opening a new connection each time if that's what's causing you the performance problems.

benster

Quicoli · **Posted:** Fri Oct 21, 2005 10:23 am

Quote:

The connection is not closed when a session is closed if you passed it in to OpenSession() yourself.

So i think a test approach would be to has a Singleton for the Firebird connection, wich if connection is null or closed, connect to database and return a Openned FbConnection.

That FbConnection would be passed in a Factory for hibernate sessions...

I dindt see NHibernate code for OpenSession method, but i guess it should check connection state out before opening the passed one, or starting a new one.

thanks !!!!!!!!!!!!!!

basj · **Joined:** Sat May 14, 2005 2:17 pm **Posts:** 18

I have also noticed that firebird is really much slower then MySql for example. Did you find a solution and if so can you post it?