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语句还要慢.