Sql语句与存储过程查询数据的性能测试实现代码

一.建立数据库Liezui_Test

ID int 主键 自增

Title varchar(100)

ReadNum int

二.向数据库中插入100万条数据

declare @i int

set @i=1

while @i<=500000

begin

insert into Liezui_Test(Title,ReadNum) values('执行总数统计',@i)

set @i=@i+1

end

GO

declare @i int

set @i=1

while @i<=500000

begin

insert into Liezui_Test(Title,ReadNum) values('毛巾因经常处于潮湿状态而极易滋生有害细菌',@i)

set @i=@i+1

end

GO

三.增加SelectByTitle存储过程

Create PROCEDURE [dbo].[SelectByTitle]

AS

BEGIN

Select top 10000 * from Liezui_Test where Title Like '%执行%'

END

三.开始测试

首先在页面内放一个repeater 用于绑定数据 二个label 用于显示结果

测试场景一 : 不绑定Repeater,只进行数据库源的绑定

代码如下：

Stopwatch st = new Stopwatch();

st.Start();

Repeater1.DataSource = Jinlong.Data.DBHelper.ReturnDataSet("Select top 10000 * from Liezui_Test where Title Like '%执行%'").Tables[0];

st.Stop();

Label1.Text = st.ElapsedMilliseconds.ToString() + "ms";

Stopwatch st2 = new Stopwatch();

st2.Start();

SqlParameter[] para = { };

Repeater1.DataSource = Jinlong.Data.DBHelper.RunProcedure("SelectByTitle", para, "ds");

st2.Stop();

Label2.Text = st2.ElapsedMilliseconds.ToString() + "ms";

结果如下：

Label1 Label2

52ms 48ms

39ms 46ms

45ms 44ms

43ms 42ms

37ms 40ms

43ms 44ms

结论：用Sql语句和存储过程的速度差不多.

测试场景二 : 绑定Repeater

代码如下：

Stopwatch st = new Stopwatch();

st.Start();

Repeater1.DataSource = Jinlong.Data.DBHelper.ReturnDataSet("Select top 10000 * from Liezui_Test where Title Like '%执行%'").Tables[0];

Repeater1.DataBind();

st.Stop();

Label1.Text = st.ElapsedMilliseconds.ToString() + "ms";

Stopwatch st2 = new Stopwatch();

st2.Start();

SqlParameter[] para = { };

Repeater1.DataSource = Jinlong.Data.DBHelper.RunProcedure("SelectByTitle", para, "ds");

Repeater1.DataBind();

st2.Stop();

Label2.Text = st2.ElapsedMilliseconds.ToString() + "ms";

结果如下：

Label1 Label2

161ms 192ms

205ms 191ms

142ms 208ms

153ms 198ms

134ms 209ms

280ms 335ms

结论：用存储过程的速度居然比直接用Sql语句还要慢.