今天在敲代码过程中发现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);
}
然后问经理经理说他用的是封装的一个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语句貌似是一样的，为什么后者会快好多，万条级别的都只要秒秒钟就行的，搞不懂，所以，求大神解答
你不把视图贴出来怎么知道？
先要确定影响速度的是SQL语句,还是你写的程序.你先把拼接好的SQL放到控制台比较下速度
大概的看了一眼，没仔细读。
第一段代码看到有表关联查询，第二段没注意到有关联表。
@param，一般用在存储过程传参里面，如果使用了存储过程来执行，那肯定比你使用 .Query(sqlStr)要快很多
你可以调整一下，看看在执行语句最后一步之前，两者的语句是什么样的，比较一下
最好的方法就是对比最后的sql语句的差异，然后拿到两种sql去单独执行看看差异，如果差异不大就是你的代码执行效率不高。
视图的本身就是sql块，不是表，对视图进行分页查询相当于嵌套了一条sql，视图不可以建索引，建议用实体表，或者优化生成视图的sql代码