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