SQL statement and stored procedure data query performance test implementation code

1. Create Database Liezui_Test
ID int primary key auto-Increment
Title varchar (100)
ReadNum int
2. Insert 1 million data records into the database
Declare @ I int
Set @ I = 1
While I <= 500000
Begin
Insert into Liezui_Test (Title, ReadNum) values ('total execution statistics', @ I)
Set @ I = @ I + 1
End
GO
Declare @ I int
Set @ I = 1
While I <= 500000
Begin
Insert into Liezui_Test (Title, ReadNum) values ('towels are prone to harmful bacteria because they are often wet. ', @ I)
Set @ I = @ I + 1
End
GO
3. added the SelectByTitle stored procedure.
Create PROCEDURE [dbo]. [SelectByTitle]
AS
BEGIN
Select top 10000 * from Liezui_Test where Title Like '% execution %'
END
Iii. Start testing
First, put a repeater in the page to bind two data labels for display results.
Test scenario 1: bind the database source only without Repeater
The Code is as follows:
Stopwatch st = new Stopwatch ();
St. Start ();
Repeater1.DataSource = Jinlong. Data. DBHelper. ReturnDataSet ("Select top 10000 * from Liezui_Test where Title Like '% execute %'"). 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 ";
The result is as follows:
Label1 Label2
52 ms 48 ms
39 ms 46 ms
45 ms 44 ms
43 ms 42 ms
37 ms 40 ms
43 ms 44 ms
Conclusion: The speed of using SQL statements is similar to that of stored procedures.
Test Scenario 2: bind Repeater
The Code is as follows:
Stopwatch st = new Stopwatch ();
St. Start ();
Repeater1.DataSource = Jinlong. Data. DBHelper. ReturnDataSet ("Select top 10000 * from Liezui_Test where Title Like '% execute %'"). 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 ";
The result is as follows:
Label1 Label2
161 ms 192 ms
205 ms 191 ms
142 ms 208 ms
153 ms 198 ms
134 ms 209 ms
280 ms 335 ms
Conclusion: the speed of using stored procedures is slower than that of using SQL statements directly.