asp.net 性能笔记

Returning multiple result sets

ADO.NET allows you to receive a number of result sets in one go. Take for example
the following stored procedure:
CREATE PROCEDURE [dbo].[GetBooksAndAuthors]
AS
BEGIN
SET NOCOUNT ON;
SELECT [BookId]
,[Title]
,[AuthorId]
,[Price]
FROM [dbo].[Book]
SELECT [AuthorId]
,[Name]
,[Address]
,[Phone]
,[Email]
FROM [dbo].[Author]

END

This returns two result sets, one with the book records and the other with the author
records. You can access the second result set using SqlDataReader.NextResult,
like this:
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// read first result set ...
}
reader.NextResult();
while (reader.Read())
{
// read second result set ...
}
}
This means that if you need to show multiple result sets on a page, you can get them
all in one go rather than one at a time.

Sending multiple inserts in one go
You may have a situation where you need to call a stored procedure a variable
number of times. For example, you could have a grid where a visitor enters one or
more new records, and then clicks a button to save them all in one go in the database.
If you have a stored procedure that inserts a record, you would call that stored
procedure for each new record to be inserted.
The conventional approach would be to send each call to the stored procedure
individually. However, you can also send them all in one go. First, create the SQL
with parameters as shown in the following code:
const string singleExec = "EXEC dbo.InsertData @Title{0}, @Author{0},
@Price{0};";
StringBuilder sql = new StringBuilder();
for (int j = 0; j < nbrInserts; j++)
{
sql.AppendFormat(singleExec, j);
}
This creates a single string with multiple EXEC statements, separated by a semi-colon
(;). To distinguish the parameters for each EXEC, AppendFormat is used to append the
sequence number in variable j to the parameter names.
Assigning values to the parameters would look like the following:
for (int j = 0; j < nbrInserts; j++)
{
cmd.Parameters.AddWithValue("@Title" + j.ToString(), ...);
cmd.Parameters.AddWithValue("@Author" + j.ToString(), ...);
cmd.Parameters.AddWithValue("@Price" + j.ToString(), ...);
}
Then send all EXEC statements in one go to the database:
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
Note that the command text contains SQL text (the EXEC statements), not a stored
procedure name. So, you use the command type CommandType.Text.
Chapter 4
[ 73 ]
The working test code is in the page MultiInsert.aspx in the folder CPU in the
downloaded code bundle. It sends four stored procedure calls, repeating this
100 times. The results on my machine were:
Test Time taken for
total database
access (in ticks)
Time taken excluding
waiting for response
from database (in ticks)
100 * sending an individual stored
procedure four times (making 400 sends)
3022521 99387
100 * sending four stored procedure calls in
one go (making 100 sends)
2096341 54767
This shows that it is attractive to send multiple stored procedure calls in one go,
rather than one by one. You not only save time spent waiting for the database, but
also CPU cycles, worker thread switching delays, and so on, as evidenced by the
right-most column.

Revealing the time taken by exceptions
Take for example converting a string to an integer. You can use either Int32.Parse,
which throws an exception if the conversion failed, or Int32.TryParse, which
returns a success boolean.
CPU
[ 74 ]
Test code with Int32.Parse:
for (int i = 0; i < 1000; i++)
{
int targetInt = 0;
try
{
targetInt = Int32.Parse("xyz");
}
catch
{
}
}
Test code with Int32.TryParse:
for (int i = 0; i < 1000; i++)
{
int targetInt = 0;
if (!Int32.TryParse("xyz", out targetInt))
{
}
}
On my machine, these were the results:
Test Time taken (ticks)
Int32.Parse—throw 1000 exceptions 1052207
Int32.TryParse—no exceptions 1522
This illustrates that you do not want to use exceptions for anything other than true
exceptions, which is what they are meant for in the first place.