qq_32005083
山人无暇自哀
2018-01-11 07:19

今天遇到的关于SQL视图分页查询的效率问题的困惑,求解

40
  • 分页
  • sql

今天在敲代码过程中发现SQL视图分页查询会特别慢但是只有200来条数据
当时写的查询方法如下

  private string GetExecuteMissionShowListExport(HttpContext context)
        {
            BaseUserInfo userInfo = (BaseUserInfo)context.Session[Common.SessionUserInfo];
            int pageIndex = Convert.ToInt32(context.Request["pageIndex"]);
            int pageSize = Convert.ToInt32(context.Request["pageSize"]);
            string sZXBH = context.Request["sZXBH"];
            string state = context.Request["state"];
            string bDate = context.Request["bDate"];
            string eDate = context.Request["eDate"];
            string sXJRID = context.Request["sXJRID"];
            int recordCount = 0;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" sXJBMID='{0}'", userInfo.OrgID);
            if (!string.IsNullOrEmpty(sZXBH))
            {
                sb.AppendFormat(" AND sZXBH LIKE'%{0}%' ", sZXBH);
            }
            if (!string.IsNullOrEmpty(state))
            {
                sb.AppendFormat(" AND iZT={0} ", state);
            }
            if (!string.IsNullOrEmpty(sXJRID))
            {
                sb.AppendFormat(" AND sXJRID={0} ", sXJRID);
            }
            if (!string.IsNullOrEmpty(bDate) && string.IsNullOrEmpty(eDate))
            {
                sb.AppendFormat(" And dJHXJSJ >= '{0} 00:00:00'", Convert.ToDateTime(bDate).ToString("yyyy-MM-dd"));
            }
            else if (string.IsNullOrEmpty(bDate) && !string.IsNullOrEmpty(eDate))
            {
                sb.AppendFormat(" And dJHXJSJ < '{0} 23:59:59'", Convert.ToDateTime(eDate).ToString("yyyy-MM-dd"));
            }
            else if (!string.IsNullOrEmpty(bDate) && !string.IsNullOrEmpty(eDate))
            {
                sb.AppendFormat(" And dJHXJSJ BETWEEN '{0} 00:00:00' and '{1} 23:59:59'", Convert.ToDateTime(bDate).ToString("yyyy-MM-dd"), Convert.ToDateTime(eDate).ToString("yyyy-MM-dd"));
            }

            string sqlStr = string.Format(@"SELECT * FROM 
                                                (
                                                    SELECT ROW_NUMBER() OVER (ORDER BY sZXBH DESC) AS ROWID,* FROM 
                                                    (
                                                        SELECT A.*,B.sID FROM
                                                        (SELECT  sZXBH,sXJRXM,iZT,sXJBMID FROM V_XJ_ZXXMB ) A 
                                                        LEFT JOIN
                                                        (SELECT  sID,sZXBH FROM XJ_ZXB) B
                                                        ON A.sZXBH=B.sZXBH  
                                                    )tb WHERE {0}
                                                )AS sp 
                                                WHERE ROWID BETWEEN ({1}-1)*{2}+1 AND {1}*{2}",  sb.ToString(), pageIndex, pageSize);
            System.Diagnostics.Debug.WriteLine(sqlStr);
            DataTable dt = new CommonBLL().Query(sqlStr);

            sqlStr = string.Format(@"SELECT sID FROM 
                                    (
                                        SELECT A.*,B.sID FROM
                                        (SELECT  sZXBH,sXJRXM,iZT,sXJBMID FROM V_XJ_ZXXMB ) A 
                                        LEFT JOIN
                                        (SELECT  sID,sZXBH FROM XJ_ZXB) B
                                        ON A.sZXBH=B.sZXBH)tb WHERE {0}",  sb.ToString());
            System.Diagnostics.Debug.WriteLine(sqlStr);
            recordCount = new CommonBLL().Query(sqlStr).Rows.Count;
            return JsonClass.DataTableToJson(dt, recordCount);
        }
  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

7条回答