滥用存储过程的思考
数据分页功能是经常讨论的话题，在很多人眼中在dotNet的Client端进行数据分页是非常低效，根本不可能和存储过程匹敌；即使很糟糕的存储过程代码也会比dotNet的Client要快。为了证实这一点于是编写了一个简单的dotNet分页代码和传统的MSSQL用临表进行分页的存储进行了比较。
测试描述：
对MSSQL NorthWind数据的Orders表进行分页操作，分页的总记录数830条。对相关分页代码进行10次调用，每次调用分别执行1页到5页的数据集获取。
dotNet分页代码:
const string getindex = "select orderid from orders";
const string getrecord = "select * from orders where orderid in ({0})";
static System.Data.DataTable GetDataSQL(int page)
{
System.Text.StringBuilder index = new StringBuilder();
SqlDataAdapter da;
System.Data.DataSet ds = new System.Data.DataSet();
SqlCommand cmd = new SqlCommand();
int start, end;
start = 10 * page;
end = start + 10;
int cur = 0;
using (SqlConnection conn = new SqlConnection("data source=.;initial catalog=northwind;user id=sa;pwd=;"))
{
cmd.Connection = conn;
cmd.CommandText = getindex;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (cur >= start && cur < end)
{
if (index.Length > 0)
{
index.Append(",");
}
index.Append("'" + reader[0].ToString() + "'");
}
if (cur > end)
{
cmd.Cancel();
break;
}
cur++;
}
}
cmd.CommandText = string.Format(getrecord, index.ToString());
da = new SqlDataAdapter(cmd);
da.Fill(ds, "table1");
return ds.Tables[0];
}
}
存储过程分页代码:
CREATE PROC testList
(
@PageIndex int,
@PageSize int
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
TempID int IDENTITY,
orderid int not null
)
Insert into #IDs(orderid) select orderid from orders
SELECT orders.*
FROM orders
INNER JOIN #IDS ON (orders .[orderid] = #IDS.orderid)
WHERE #IDS.TempID > @PageLowerBound
AND #IDS.TempID < @PageUpperBound
GO
static System.Data.DataTable GetDataSP(int page)
{
SqlDataAdapter da;
System.Data.DataSet ds = new System.Data.DataSet();
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection("data source=.;initial catalog=northwind;user id=sa;pwd=;"))
{
cmd.Connection = conn;
cmd.CommandText = "testList";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@PageIndex", page);
cmd.Parameters.Add("@PageSize", 10);
conn.Open();
da = new SqlDataAdapter(cmd);
da.Fill(ds, "table1");
return ds.Tables[0];
}
}
测试结果:
|
存储过程分页:74毫秒 |
dotNet分页:64毫秒 |
|
存储过程分页:21毫秒 |
dotNet分页:10毫秒 |
|
存储过程分页:1023毫秒 |
dotNet分页:11毫秒 |
|
存储过程分页:20毫秒 |
dotNet分页:11毫秒 |
|
存储过程分页:22毫秒 |
dotNet分页:12毫秒 |
|
存储过程分页:1031毫秒 |
dotNet分页:10毫秒 |
|
存储过程分页:20毫秒 |
dotNet分页:10毫秒 |
|
存储过程分页:21毫秒 |
dotNet分页:10毫秒 |
|
存储过程分页:20毫秒 |
dotNet分页:10毫秒 |
|
存储过程分页:21毫秒 |
dotNet分页:10毫秒 |
是什么情况导致效率相差这么远，而效率低下的却是存储过程而并不是dotNet代码。相信大家看一下存储过程的处理应该知道原因所在.
