2 qq 32005083 qq_32005083 于 2018.01.11 15:19 提问

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

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

6个回答

qq_32005083
qq_32005083   2018.01.11 15:19

然后问经理经理说他用的是封装的一个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语句貌似是一样的,为什么后者会快好多,万条级别的都只要秒秒钟就行的,搞不懂,所以,求大神解答

weixin_39749945
weixin_39749945   2018.01.11 15:42

你不把视图贴出来怎么知道?

qq_26187371
qq_26187371 先吧视图贴出来看看!
6 天之前 回复
qq_32005083
qq_32005083 sql数据库View视图贴上也没用啊,我只是想问问以上两种方法上的wherecondition拼接差别在哪
6 天之前 回复
wjl7126180
wjl7126180   2018.01.11 16:00

先要确定影响速度的是SQL语句,还是你写的程序.你先把拼接好的SQL放到控制台比较下速度

xiyangyu
xiyangyu   2018.01.11 16:52

大概的看了一眼,没仔细读。
第一段代码看到有表关联查询,第二段没注意到有关联表。

qq_32005083
qq_32005083 是我搞混了,不过语句拼接方式后面的那种执行的确实快一些,翻好多个方法看到最后SQL语句是用什么@param的方式组合的,是这个原因执行才快的吗?
5 天之前 回复
starmilan
starmilan   2018.01.12 17:07

@param,一般用在存储过程传参里面,如果使用了存储过程来执行,那肯定比你使用 .Query(sqlStr)要快很多
你可以调整一下,看看在执行语句最后一步之前,两者的语句是什么样的,比较一下

u011649691
u011649691   2018.01.14 14:28

最好的方法就是对比最后的sql语句的差异,然后拿到两种sql去单独执行看看差异,如果差异不大就是你的代码执行效率不高。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!