SQL联表分页读取效率,该怎么解决
日期：2014-05-17 浏览次数：20564 次
SQL联表分页读取效率
在网上查了一些资料，似乎这样读取效率不高，希望各位大侠指点一下，看有什么更优的方式，我通常需要读取更多的联表~~~~
------解决方案--------------------
--ROW_NUMBER
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID
FROM TRANS_TABLE(NOLOCK)
)
SELECT TT.*
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID
--top
SELECT TOP 10 * FROM tb WHERE id>(
SELECT ISNULL(max(id),0) FROM
(SELECT TOP 20 idFROM tb ORDER BY id) t
)
/// <summary>
/// 读取标签列表（多表：Plus、Users）
/// </summary>
///
public DataSet GetList_TagUnion(string strWhere,int PageSize,int Page)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT top " + PageSize + " Plus.*,Users.* FROM Plus INNER JOIN Users ON Plus.Users_ID =Users.Users_ID ");
strSql.Append("and " + strWhere);
strSql.Append("where Plus.Plus_ID not in (select top (" + (Page - 1) * PageSize + ") Plus.Plus_ID FROM Plus INNER JOIN Users ON Plus.Users_ID =Users.Users_ID and " + strWhere + " order by Plus.Plus_ID desc) ");
strSql.Append(" order by Plus.Plus_ID desc");
return Common.DbHelperSQL.Query(strSql.ToString());
}
在网上查了一些资料，似乎这样读取效率不高，希望各位大侠指点一下，看有什么更优的方式，我通常需要读取更多的联表~~~~
------解决方案--------------------
--ROW_NUMBER
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID
FROM TRANS_TABLE(NOLOCK)
)
SELECT TT.*
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID
--top
SELECT TOP 10 * FROM tb WHERE id>(
SELECT ISNULL(max(id),0) FROM
(SELECT TOP 20 idFROM tb ORDER BY id) t
)
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
