一.建立数据库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语句还要慢.
