首页 新闻 赞助 找找看

用 Hibernate 分页查询100w的数据,结果耗时10几秒,这是什么原因 (刚入门)

0
悬赏园豆:5 [已解决问题] 解决于 2014-02-09 18:54

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Cfg;

namespace DAL
{
    public class NHinbernateHelper
    {
        //
        private ISessionFactory _sessionfacotry;
        //构造函数,在new这个类的时候就调用GetSessionFactory这个方法获取到
        public NHinbernateHelper()
        {
            _sessionfacotry = GetSessionFactory();
        }
        private ISessionFactory GetSessionFactory()
        {
            Configuration cfg = new Configuration();
            ISessionFactory sf =
            cfg.Configure().BuildSessionFactory();
            return sf;
        }
        // 获得Session
        public IStatelessSession GetSession()
        {
         
            //
            IStatelessSession session = _sessionfacotry.OpenStatelessSession();

               
             //   _sessionfacotry.OpenSession();
            return session;
        }

    }
}

-----------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using DomainModel.Entities;
using System.Collections;

namespace DAL
{
    public class PersonHQL
    {
        private IStatelessSession _session;
        public IStatelessSession Session
        {
            get
            {
                return _session;
            }
        }
        //构造函数,在new这个类的时候,需要传一个ISession参数
        public PersonHQL(IStatelessSession session)
        {
            _session = session;
        }

        //增加一条记录
        /// <summary>
        /// 增加记录
        /// </summary>
        /// <param name="person">Person实体</param>
        /// <returns>int</returns>
        public int CreatePerson(Person person)
        {
           
            int newid = (int)_session.Insert(person);
            _session.Close();
            return newid;
        }

        //删除一条记录
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="person">Person实体</param>
        /// <returns>bool</returns>
        public bool DeletePerson(Person person)
        {
            _session.Delete(person);
            _session.Close();
            return true;
        }


        public IList ScalarStoredProcedure()
        {
            return _session.GetNamedQuery("ScalarSProcs")
                .SetInt32("number", 22).List();
        }

        public IList ParamStoredProcedure()
        {
            return _session.GetNamedQuery("ParamSProcs")
                .SetInt64(0, 10L)
                .SetInt64(1, 20L)
                //或者.SetInt64("second", 20L)
                .List();
        }

        public Person EntityStoredProcedure(int id)
        {
            return _session.GetNamedQuery("EntitySProcs")
                .SetInt32("ID", id)
                .UniqueResult<Person>();
        }


        //更新一条记录
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="person">Person实体</param>
        /// <returns>无返回值</returns>
        public void UpdatePerson(Person person)
        {
            _session.Update(person);
            _session.Close();
        }
        //获取Person整张表的数据
        /// <summary>
        /// 获取整张Person的记录
        /// </summary>
        /// <param>无参数</param>
        /// <returns>IList<Person></returns>
        public IList<Person> GetList()
        {
            return _session.CreateQuery("from Person").List<Person>();
        }
        //把查询某一段的数据,pagesize是第几条记录开始从0开始记起,pageindex读取多少条记录
        /// <summary>
        /// 查询某一段的记录
        /// </summary>
        /// <param name="pagesize">开始的位置</param>
        /// <param name="pageindex">记录数量</param>
        /// <returns>IList<Person></returns>
        public IList<Person> GetList(int pagesize, int pageindex)
        {
            return _session
                .CreateQuery("from Person")
                .SetFirstResult(pagesize)
                .SetMaxResults(pageindex)
                .List<Person>();
        }
        //每个页面10条记录,参数page为第几页从1开始
        /// <summary>
        /// 以每页为10条记录,查询第几页的记录,从1开始
        /// </summary>
        /// <param name="page">第几页</param>
        /// <returns>IList<Person></returns>
        public IList<Person> GetList(int page)
        {
            IList<Person> pers=GetList();
            return GetList((page-1)*10, 30);//第二个参数为每页的记录数量
        }
        //读取某条记录
        /// <summary>
        /// 读取
        /// </summary>
        /// <param name="ID">ID</param>
        /// <returns></returns>
        public Person GetPerson(int id)
        {
            var result = from c in this.GetList()
                         where c.ID == id
                         select c;
            return result.First();
        }
        //读取某条记录
        /// <summary>
        /// 读取
        /// </summary>
        /// <param name="ID">ID</param>
        /// <returns></returns>
        public Person Read(int id)
        {
            Person person = _session.Get<Person>(id);
            _session.Close();
            return person;
        }

    }
}

-------------------------------------

程序员编程日记的主页 程序员编程日记 | 初学一级 | 园豆:6
提问于:2014-02-08 20:31
< >
分享
最佳答案
0

建议跟踪一下最终实际执行的SQL语句

收获园豆:5
dudu | 高人七级 |园豆:31075 | 2014-02-09 12:41
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册