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

Question

今天在敲代码过程中发现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);
        }

Answer 1

然后问经理经理说他用的是封装的一个SQL条件拼接，那个就很快
这是他的一个方法

    private string GetListAudit(HttpContext context)
        {
            BaseUserInfo userInfo = (BaseUserInfo)context.Session[Common.SessionUserInfo];
            int pageIndex = Convert.ToInt32(context.Request["pageIndex"]);
            int pageSize = Convert.ToInt32(context.Request["pageSize"]);

            string title = context.Request["title"];
            string state = context.Request["state"];
            string tableID = context.Request["tableID"];
            string category = context.Request["category"];
            int recordCount = 0;

            DataTable dt;
            List<WhereCondition> whereList = new List<WhereCondition>();

            if (!string.IsNullOrEmpty(title))
            {               
                whereList.Add(new WhereCondition { Field = "sRWBT", Operation = "LIKE", Value = '%' + title + '%' });
            }
            if (!string.IsNullOrEmpty(state))
            {                
                whereList.Add(new WhereCondition { Field = "iZTBZ", Value = state });
            }
            else
            {
                if (tableID.Equals("tt3"))
                {
                    whereList.Add(new WhereCondition { Field = "iZTBZ", Operation = "<=", Value = 2 });
                    whereList.Add(new WhereCondition { Field = "iZTBZ", Operation = "<>", Value = 0 });
                    whereList.Add(new WhereCondition { Field = "iZTBZ", Operation = "<>", Value = 1 });
                }
                else
                {                    
                    whereList.Add(new WhereCondition { Field = "iZTBZ", Operation = ">=", Value = 3 });
                }
            }
            if (!string.IsNullOrEmpty(tableID))
            {
                if (tableID.Equals("tt1"))// tt0表示全部任务,tt1表示我的任务
                {                    
                    whereList.Add(new WhereCondition { Field = "sJSRID", Value = userInfo.UserID });
                }
                if (tableID.Equals("tt4"))//tt4表示我的登记
                {                    
                    whereList.Add(new WhereCondition { Field = "sDJRID", Value = userInfo.UserID });
                }
            }
            string categoryName = string.Empty;
            switch (category)
            {
                case "1":
                    categoryName = "故障";
                    break;
                case "2":
                    categoryName = "需求";
                    break;
                case "3":
                    categoryName = "任务";
                    break;
                default:
                    categoryName = "故障";
                    break;
            }

            string bDate = context.Request["begin"];
            string eDate = context.Request["end"];
            if (!string.IsNullOrEmpty(bDate) && string.IsNullOrEmpty(eDate))
            {                
                whereList.Add(new WhereCondition { Field = "dSQSJ", Operation = ">=", Value = Convert.ToDateTime(bDate).ToString("yyyy-MM-dd") + " 00:00:00" });
            }
            else if (string.IsNullOrEmpty(bDate) && !string.IsNullOrEmpty(eDate))
            {                
                whereList.Add(new WhereCondition { Field = "dSQSJ", Operation = "<", Value = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd") + " 23:59:59" });
            }
            else if (!string.IsNullOrEmpty(bDate) && !string.IsNullOrEmpty(eDate))
            {
                whereList.Add(new WhereCondition { Field = "dSQSJ", Operation = ">=", Value = Convert.ToDateTime(bDate).ToString("yyyy-MM-dd") + " 00:00:00" });
                whereList.Add(new WhereCondition { Field = "dSQSJ", Operation = "<", Value = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd") + " 23:59:59" });                
            }


            string bfDate = context.Request["finishBegin"];
            string efDate = context.Request["finishEnd"];
            if (!string.IsNullOrEmpty(bfDate) && string.IsNullOrEmpty(efDate))
            {                
                whereList.Add(new WhereCondition { Field = "dSJWCSJ", Operation = ">=", Value = Convert.ToDateTime(bfDate).ToString("yyyy-MM-dd") + " 00:00:00" });
            }
            else if (string.IsNullOrEmpty(bfDate) && !string.IsNullOrEmpty(efDate))
            {                
                whereList.Add(new WhereCondition { Field = "dSJWCSJ", Operation = "<", Value = Convert.ToDateTime(efDate).ToString("yyyy-MM-dd") + " 23:59:59" });
            }
            else if (!string.IsNullOrEmpty(bfDate) && !string.IsNullOrEmpty(efDate))
            {
                whereList.Add(new WhereCondition { Field = "dSJWCSJ", Operation = ">=", Value = Convert.ToDateTime(bfDate).ToString("yyyy-MM-dd") + " 00:00:00" });
                whereList.Add(new WhereCondition { Field = "dSJWCSJ", Operation = "<", Value = Convert.ToDateTime(efDate).ToString("yyyy-MM-dd") + " 23:59:59" });                
            }
            string responsePerson = context.Request["responsePerson"];
            if (!string.IsNullOrEmpty(responsePerson))
            {

                whereList.Add(new WhereCondition { Field = "sJSRID", Value = responsePerson });

            }
            string regPerson = context.Request["regPerson"];
            if (!string.IsNullOrEmpty(regPerson))
            {                
                whereList.Add(new WhereCondition { Field = "sDJRID", Value = regPerson });
            }
            string isReplenish = context.Request["isReplenish"];
            if (!string.IsNullOrEmpty(isReplenish))
            {                
                whereList.Add(new WhereCondition { Field = "iSFBL", Value = isReplenish });
            }
            string isSubscribe = context.Request["isSubscribe"];
            if (!string.IsNullOrEmpty(isSubscribe))
            {                
                whereList.Add(new WhereCondition { Field = "iSFYY", Value = isSubscribe });
            }

            whereList.Add(new WhereCondition { Field = "sJSKSID", Value = userInfo.OrgID });
            whereList.Add(new WhereCondition { Field = "sRWLB", Value = categoryName });

            dt = CommonBLL.GetDataPaged("V_RWB_Edit", "sBZ,sID,sRWBT, Convert(varchar(100),[dSQSJ],20) AS dSQSJ,iZTBZ,iSFBL,sJSRID,sJSRXM,sLXR,sLXDH,iZTBZ AS progress,iSFZLRK,iPJFS,sDJRID,sDJRXM,SJLB,sSQKSMC,Convert(varchar(100),[dSJWCSJ],20) AS dSJWCSJ,iSFYY,sYYSJ,ResolveNum", whereList, "dSQSJ DESC", pageIndex, pageSize, ref recordCount).Tables[0];



            return JsonClass.DataTableToJson(dt, recordCount);
        }

然后这是他用的封装类

    public class WhereCondition
    {
        public WhereCondition()
        {
            Operation = "=";
            Relation = "AND";
        }
        /// <summary>
        /// 字段名
        /// </summary>
        public string Field { get; set; }

        /// <summary>
        /// 关系，AND ，OR，
        /// </summary>
        public string Relation { get; set; }

        /// <summary>
        /// 操作符 > ,  >= ,!=, LIKE 等等  默认“=”
        /// </summary>
        public string Operation { get; set; }

        /// <summary>
        /// 字段对应的值
        /// </summary>
        public object Value { get; set; }
    }

还是回到标题的问题，这两种方式拼接后的SQL语句貌似是一样的，为什么后者会快好多，万条级别的都只要秒秒钟就行的，搞不懂，所以，求大神解答