滥用存储过程的思考

存储过程在数据库本地编译本地运行显然是提高数据库访问效率的首选，但实际中有不少朋友误解了只要是存储过程就是高性能的表现，认为只要是存储过程实现的逻辑操作就肯定比客户端操作数据库效率高；但他们却忘记重要的一点就是效率很大程度是依赖于具体实现的代码。接下来做个简单的测试证明这一点。

数据分页功能是经常讨论的话题，在很多人眼中在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代码。相信大家看一下存储过程的处理应该知道原因所在.