12.2.5. Indexed Views
Indexed views are an excellent alternative when you absolutely need to JOIN
data, and traditional indexing doesn't yield the performance you're
looking for. Indexed views behave like tables; the data covered is
materialized to disk so it can be retrieved quickly. Before jumping on
indexed views, understand that they have certain limitations and that
due to their nature, you may incur a performance hit through the usual Insert, Delete, and Update statements. Taking the previous statement as an example, let's see how to create an indexed view to support the JOIN operation.
First, create a view that contains the statement you want to tune. Make sure you include all the columns you need in the SELECT clause:
CREATE VIEW vTestUsersType WITH SCHEMABINDING AS
SELECT T.Name, T.UserType, T.AgeGroup, UT.UserTypeKey
FROM dbo.TestUsers T INNER JOIN dbo.TestUserType UT ON T.UserType = UT.UserType
Next, create a unique clustered index on the view:
CREATE UNIQUE CLUSTERED INDEX IDX_VIEW_TestUsers
ON vTestUsersType
(UserTypeKey, AgeGroup, Name, UserType)
Et voilà. When you run the statement again, you see a beautiful execution plan like the one in Figure 9.
Because the view contains all the necessary columns, and the clustered
index contains all the columns of the view, you obtain the fastest
possible data-retrieval technique, next to caching.
6. 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
int id = 15; // the product category
string sql = "proc_CalculateCost";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("categoryId", SqlDbType.Float);
cmd.Parameters[0].Value = id;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
try
{
if (dr.Read())
cost = (float)dr[0];
}
finally
{
dr.Close();
conn.Close();
}
The code for the stored procedure looks something like this:
CREATE PROC proc_CalculateCost
@categoryId int
AS
DECLARE @i intDECLARE @cost float
SET @cost = 0.0
SET @i = (SELECT count(*) FROM category WHERE ID = @categoryId)
WHILE (@i > 0)
BEGIN
SET @cost = @cost + 0.25*(SELECT Min(dollars) FROM ...)
SET @i = @i - 1
END
SELECT @cost
The advantage of calling a
stored procedure is that you don't need to fetch the necessary records
across the Internet to calculate the cost figure. The stored procedure
runs where the data is located and returns only a single value in this
case.
