First call's time

Topics: Data Access Application Block
May 12, 2008 at 9:16 AM
Hello!

I'm using Data Access Application Block in one application and everything works fine, except that the first execution last 15 seconds!. After that all the querys go fast, but this problem make the application startup very slow. The database is in my own computer, so I discard network issues. I'll explain what I'm doing, so you can help me.

I have an static class with generic methods to execute querys (selects, inserts, updates...). In that class I use a GenericDatabase object because the database may be SQL Server or Access. This is the code I use:
--------------------------------------------------------------------
static class Connection
{
        static GenericDatabase db;
        static DbConnection cnn;

        public static DataTable Select(string strSQL)
        {
            try
            {
                DataSet dsResult = new DataSet();
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                // Retrieve the initial data
                db.LoadDataSet(dbCommand, dsResult, "Table");
                return dsResult.Tables[0];
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static bool Insert(string strSQL)
        {
            try
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                int result = db.ExecuteNonQuery(dbCommand);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static bool Open()
        {
            try
            {
                switch (Configuracion.TipoBaseDatos)
                {
                    case Constantes.ACCESS:
                        string PathBDcgs = ConfigurationManager.AppSettings["PathBDcgs"].ToString();
                        db = new GenericDatabase(Configuracion.ConnectionStrings["Access"], OleDbFactory.Instance);
                        break;
                    case Constantes.SQLSERVER:
                        db = new GenericDatabase(Configuracion.ConnectionStrings["SQLServer"], SqlClientFactory.Instance);
                        break;
                    default:
                        return false;
                }               
                cnn = db.CreateConnection();
                cnn.Open();               
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static void Close()
        {
            cnn.Close();
        }
}
--------------------------------------------------------------------

What can be the problem? What am I doing wrong? I have made debug to see where is losing so many time and it lose all the time between calling the method and accessing to it (when I press F11 on VS2005).

Thanks in advance

      RaY
May 12, 2008 at 1:42 PM

Hi,

Your code keeps a connection open, so later requests will benefit from this. However, it is not recommended to keep connections open (see this entry on MSDN).

You don't mention if the slowdown happens with both SQL Server and Access. If your Access database is large, it might take a long time to open a connection to it. Btw, you don't need to use a GenericDatabase when you connect to SQL Server; just set the type on your field to Database, and it will be able to hold both a GenericDatabase and a SQLDatabase.

You should not swallow the exceptions in your data access code.

Hope this helps,
Fernando

May 12, 2008 at 3:29 PM
Edited May 12, 2008 at 4:06 PM
Hi,

I keep my connection open because I do a lot of insert queries and if I open & close it I lose performance. Anyway I'll take a look to the recommendations.

The issue happens in both cases (SQL Server & Access), but the database isn't large. I have tried to do a similar code using OleDbConnection & OleDbDataAdapter and the first execution last less than 1 second. As I said, it lose all the time accessing to the method, not doing the query. For example, if i put this line in my code "Connection.Select("Select * from table");", it takes 15 second to step into "Select" method.

About swallowing the exceptions, I agree with you. In fact I log that, but I removed that code to post here.

I didn't understand what you mean about using GenericDatabase and SQLDatabase at the same time. Do you say using 2 variables (one for the generic and another for the sql) and use the one I need? Then I lose the advantage of the GenericDatabase and if in the future I need to add support for Oracle I'll need 3 variables.

Thanks for your time!!
May 13, 2008 at 1:48 PM
Hi,

Regarding variables, you can assign an instance of any subclass of Database to a single variable of type Database. You will not keep instances of the different database types at the same time, you will just be able to keep an instance of any of the subclasses (but just one at any given time).

Tried the repro below and didn't get a noticeable slowdown in the call to LoadDataset. Can you try it on your end? You will probably need to fix the code layout...

using

 

System;

 

 

 

using

 

System.Data;

 

 

 

using

 

System.Data.Common;

 

 

 

using

 

Microsoft.Practices.EnterpriseLibrary.Data;

 

 

 

using

 

Microsoft.Practices.EnterpriseLibrary.Data.Sql;

 

 

 

namespace

 

ConsoleApplication20

 

{

 

class Program

 

 

 

 

{

 

static void Main(string[] args)

 

{

 

Database db = new SqlDatabase(@"Data Source=(local)\sqlexpress;Initial Catalog=Northwind;Integrated Security=True");

 

 

using (DbConnection connection = db.CreateConnection())

 

{

connection.Open();

 

DataSet dsResult = new DataSet();

 

 

DbCommand dbCommand = db.GetSqlStringCommand(@"select * from Customers");

 

 

// Retrieve the initial data

 

 

 

 

db.LoadDataSet(dbCommand, dsResult,

"Table");

 

 

DataTable table = dsResult.Tables[0];

 

}

}

}

}

May 14, 2008 at 8:16 AM
I don't know why, now I haven't the slowdown (without change any code of the class).

Anyway I'll try what you said if it happens again. Now I understand what you meant (use an abstract Database variable, not 2 "strong type") and it has the same advantages for me that the GenericDatabase. I only have a problem, for SQL Server you use SqlDatabase constructor but what database should I use for Microsoft Access?

Thanks again for your time.


May 14, 2008 at 7:20 PM
Weird. Please report your findings if this happens again.

Regarding the variable, you would still use the GenericDatabase for MS Access.

Fernando