Remote Database response very slow of asp.net appl

Question

I may be missed something to track in the bellow code, but it is slowing down remote database server response time. Application is working fine while tried with Database from the same machine where application deployed. Also, verified all the nHibernate tasks using NH-Profiler. All queries are under controlled. The only reason is response time of DB server. Also, analyzed the DB server, server is fine but this is the only application which is slow while running through Remote Database server.

Following is the Common Class, used for all nHibernate operations :

public class CommonRepository : IDisposable
    {
        protected static Configuration Config;
        protected static ISessionFactory SessionFactory;
        protected static ITransaction Transaction;
        static readonly object Factorylock = new object();

        /// <summary>
        /// Configure your repositories. It uses default DB string 
        /// Create singleton thread safe : found good stuff on Stackoverflow
        /// Referrence : http://stackoverflow.com/questions/2362195/ensure-nhibernate-sessionfactory-is-only-created-once
        /// </summary>
        public CommonRepository()
        {
            lock (Factorylock)
            {
                if (SessionFactory == null)
                {
                    // SessionFactory = NonFluentConfiguration();
                    SessionFactory = FluentConfig(msdnh.util.Config.ConnectionString); //Grab default string from Database
                }
            }

            Session = SessionFactory.OpenSession();
            Transaction = Session.BeginTransaction();

        }

        /// <summary>
        /// Configure your repositories
        /// </summary>
        /// <param name="connectionString">Connection string</param>
        public CommonRepository(string connectionString)
        {
            if (SessionFactory == null)
            {
                SessionFactory = FluentConfig(connectionString);
            }
            Session = SessionFactory.OpenSession();
            Transaction = Session.BeginTransaction();
        }
        private static ISessionFactory NonFluentConfiguration()
        {
            Config = new Configuration();
            Config.Configure();
            Config.AddAssembly(typeof(CommonRepository).Assembly);
            return Config.BuildSessionFactory();
        }

        private static ISessionFactory FluentConfig(string connString)
        {

            IPersistenceConfigurer persistenceConfigurer =
                MsSqlConfiguration
                    .MsSql2008
                .ConnectionString(connString)
                .ShowSql();

            // initialize nhibernate with persistance configurer properties
            Configuration cfg = persistenceConfigurer
                .ConfigureProperties(new Configuration());

            // add mappings definition to nhibernate configuration
            var persistenceModel = new PersistenceModel();
            persistenceModel.AddMappingsFromAssembly(Assembly.Load("Girlpower"));
            persistenceModel.Configure(cfg);

            // set session factory field which is to be used in tests
            return cfg.BuildSessionFactory();


        }

        protected static ISession Session { get; private set; }
        public bool Commit()
        {
            if (Transaction.IsActive)
            {
                try { Transaction.Commit(); return true; }
                catch (Exception exception)
                {
                    //Error log
                    return false;

                }
            }
            return false;
        }
        public bool Rollback()
        {
            if (Transaction.IsActive)
            {
                try { Transaction.Rollback(); return true; }
                catch (Exception exception)
                {
                    //Error log
                    return false;
                }
            }
            return false;
        }
        public void GenerateSchema()
        {
            new SchemaExport(Config).Execute(true, false, false);
        }
        private void BuildSchema(Configuration cfg)
        {
            new SchemaExport(cfg)
              .Create(false, true);
        }

        #region Repository Functions
        /// <summary>
        /// Saves or updates the object to the database, depending on the value of its identifier property.
        /// </summary>
        /// <param name="value">
        /// A transient instance containing a new or updated state.</param>
        public void Save(object value)
        {
            Session.Save(value);
        }
        /// <summary>
        /// Save object to repository
        /// </summary>
        /// <param name="value">
        /// Object for save</param>
        public void SaveorUpdate(object value)
        {
            Session.SaveOrUpdate(value);
        }
        /// <summary>
        /// Merge / copy state of given object to persit object
        /// </summary>
        /// <param name="value">Entity</param>
        public void SaveorUpdateCopy(object value)
        {
            Session.Merge(value);
        }
        /// <summary>
        /// Removes a persistent instance from the database.
        /// </summary>
        /// <param name="value">
        /// The instance to be removed.</param>
        public void Delete(object value)
        {
            Session.Delete(value);
        }
        /// <summary>
        /// Delete records from specific entity
        /// </summary>
        /// <typeparam name="T">Entity name</typeparam>
        /// <param name="id">value of record id</param>
        public void Delete<T>(int id) where T : class
        {
            Session.Delete(string.Format("from {0} where id = {1}", typeof(T), id));

        }
        /// <summary>
        /// Update specific fields of entity using IQuery
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="updateColName"></param>
        /// <param name="updateColValue"></param>
        public void Update<T>(int id, string updateColName, object updateColValue) where T : class
        {
            var query = CreateQuery<T>(id, updateColName, updateColValue);
            query.ExecuteUpdate();
        }

        private static IQuery CreateQuery<T>(int id, string updateColName, object updateColValue) where T : class
        {
            return Session.CreateQuery(string.Format("Update {0} set {1} = {2} where id = {3} ", typeof(T), updateColName, updateColValue, id));
        }

        /// <summary>
        /// Delete records from specific entity by speciying column name
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="colName"></param>
        public void Delete<T>(int id, string colName) where T : class
        {
            Session.Delete(string.Format("from {0} where {1} = {2}", typeof(T), colName, id));

        }
        /// <summary>
        /// Returns a strong typed persistent instance of the given named entity with the given identifier, or null if there is no such persistent instance.
        /// </summary>
        /// <typeparam name="T">The type of the given persistant instance.</typeparam>
        /// <param name="id">An identifier.</param>
        public T Get<T>(object id)
        {
            T returnVal = Session.Get<T>(id);
            return returnVal;
        }
        /// <summary>
        /// Find Results by Name
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name"></param>
        /// <returns></returns>
        public T FindByName<T>(string name)
            where T : class
        {
            var criteria = Session.CreateCriteria<T>();
            var targetEntity = typeof(T);

            if (targetEntity == typeof(Article))
                criteria.Add(Restrictions.Eq("Title", name));
            else if (targetEntity == typeof(Category))
                criteria.Add(Restrictions.Eq("CatName", name));
            else if (targetEntity == typeof(News))
                criteria.Add(Restrictions.Eq("Title", name));
            else if (targetEntity == typeof(Video))
                criteria.Add(Restrictions.Eq("Title", name));
            else if (targetEntity == typeof(NewsCategory))
                criteria.Add(Restrictions.Eq("NewsCatName", name));
            else if (targetEntity == typeof(VideoCategory))
                criteria.Add(Restrictions.Eq("VideoCatName", name));
            else
                criteria.Add(Restrictions.Eq("Name", name));


            return FindUnique<T>(criteria);
        }
        public T FindBySpecificColumn<T>(string colname, string findValue)
           where T : class
        {
            var criteria = Session.CreateCriteria<T>();
            criteria.Add(Restrictions.Eq(colname, findValue));
            return FindUnique<T>(criteria);
        }
        public T FindBySpecificColumn<T>(string colname, int findValue)
          where T : class
        {
            var criteria = Session.CreateCriteria<T>();
            criteria.Add(Restrictions.Eq(colname, findValue));
            return FindUnique<T>(criteria);
        }
        public IList<T> FindRandomBySpecificColumn<T>(string colname, int findValue)
         where T : class
        {
            var criteria = Session.CreateCriteria<T>();
            criteria.Add(Restrictions.Eq(colname, Convert.ToBoolean(findValue)));
            criteria.AddOrder(new RandomOrder());
            return Find<T>(criteria);
        }
        public IList<T> FindTopRecents<T>(int recCount, string colName) where T : class
        {
            return Session.CreateQuery(string.Format("from {0} order by {1} DESC", typeof(T), colName))
                .SetFirstResult(0)
                .SetMaxResults(recCount)
                .List<T>();


        }
        /// <summary>
        /// Returns a list of all instances of type T from the database.
        /// </summary>
        /// <typeparam name="T">The type of the given persistant instance.</typeparam>
        public IList<T> GetAll<T>()
            where T : class
        {
            IList<T> returnVal = Session.CreateCriteria<T>().List<T>();
            return returnVal;
        }
        /// <summary>
        /// Returns a list of all instances of type T from the database for pagination
        /// </summary>
        /// <typeparam name="T">The type of the given persistant instance.</typeparam>
        /// <param name="pageIndex">Number- page index.</param>
        /// <param name="pageSize">Number -  maximum page size.</param>
        /// <returns>List of type the given persistent instance</returns>
        public IList<T> GetAll<T>(int pageIndex, int pageSize)
            where T : class
        {
            var criteria = Session.CreateCriteria(typeof(T));
            if (pageSize > 0)
            {
                criteria.SetMaxResults(pageSize);
            }
            return criteria.List<T>();
        }
        /// <summary>
        /// Find Unique values
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="criteria"></param>
        /// <returns></returns>
        public T FindUnique<T>(ICriteria criteria)
            where T : class
        {
            return (T)criteria.UniqueResult();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="criteria"></param>
        /// <returns></returns>
        public IList<T> Find<T>(ICriteria criteria) where T : class
        {
            return criteria.List<T>();
        }
        public IList<T> Find<T>(IEnumerable<string> strs)
            where T : class
        {
            IList<ICriterion> objs = strs.Select(Expression.Sql).Cast<ICriterion>().ToList();
            var criteria = Session.CreateCriteria(typeof(T));
            foreach (var rest in objs)
                criteria.SetFirstResult(0);
            return criteria.List<T>();
        }
        public IList<T> Find<T>(ICriteria criteria, PagingInfo paging) where T : class
        {
            paging.RowCount = Count(criteria);

            if (paging.PageSize > 0)
            {
                criteria.SetMaxResults((int)paging.PageSize);
                criteria.SetFirstResult((int)(paging.PageSize * paging.CurrentPage));
            }

            return criteria.List<T>();
        }
        protected int Count(ICriteria criteria)
        {
            //TODO check performance of this method (probably is better to use HQL with the COUNT(*) command)
            return criteria.List().Count;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <returns></returns>
        public IList<T> GetListByQuery<T>(IQuery query)
            where T : class
        {
            var list = query.List<T>();
            return list;
        }

        public ICriteria CreateCriteria<T>() where T : class
        {
            return Session.CreateCriteria(typeof(T));
        }

        #endregion
        #region IDisposable methods
        public void Dispose()
        {
            if (Session != null)
            {
                if (Session.IsOpen) Session.Close();
            }
        }
        #endregion
    }

We are calling above class in following manner :

public static bool CheckIfUserNameAlreadyExists(string username)
    {
        bool isExist;
        using (var userRepo = new CommonRepository())
        {
            isExist = userRepo.FindBySpecificColumn<User>("LoginId", username) != null;
        }
        return isExist;
    }

We are using Fluent Mapping in the application.

Please suggest if there are any need to change in the Common repository class or need to implement some other pattern.

Thanks in advance!

Answer 1

Some things to check:

• In your mappings - are you eager loading the entire user graph by specifying not lazyload
• Check to see the SQL generated to confirm the above
• Are there a large amount of users - Do you have an index in place
• If you are using logging tools like log4net - make sure you are not logging NHibernate info in debug mode or similar

It also seems like you are potentially creating a session factory on every request (depending on how that repository is scoped), which is expensive. Your session factory should be a singleton and dish out a session per web request scoped to lifetime of the web request.

The configuration should be really be run once at application startup.