Performance Tuning - Pro SQL Azure - page 253

Figure 12-9. Indexed view example

Stored Procedures
You've seen various ways to tune your statements and improve execution plans. However, keep in mind
that you also have stored procedures at your disposal.
Stored procedures can give you an edge if you need to execute logic that requires a large volume of
data. Because you know that returning lots of data turns into a performance problem in SQL Azure, you
can place the business logic that needs the data in a stored procedure, and have the procedure return a
status code. Because you aren't charged for CPU time, this becomes an affordable option.
Stored procedures can also be an interesting security tool, allowing you proxy the calls to underlying
tables through a procedure and never allowing direct access to the tables.
Imagine that you need to calculate the cost of an item; however, in order to calculate the cost, you
must loop to obtain certain values and perform advanced operations. You can make a call from your
application and calculate the cost in the application code as follows:
float cost = 0.0; // the total cost
int id = 15; // the product category

string sql = "SELECT * FROM category WHERE catergoryId = " + id.ToString();
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();

try
{
while (dr.Read())
{
cost += 0.25 * ...; // calculation logic goes here
}
}
finally
{
dr.Close();
conn.Close();
}

Or you can calculate the cost in a stored procedure and change the previous code to call the stored
procedure instead:
float cost = 0.0; // the total cost