Database Reference
In-Depth Information
Let's assume that the system has the data access tier implemented based on stored procedures, and one of these
procedures provides information about all of the active orders in the system. The stored procedure code is shown in
Listing 10-2.
Listing 10-2.
Code reuse: Stored procedure that returns the list of the active orders in the system
create proc dbo.usp_Orders_GetActiveOrders
as
select o.OrderId, o.ClientId, c.ClientName, o.OrderDate, o.OrderNumber, o.Amount
from dbo.Orders o join dbo.Clients c on
o.Clientid=c.ClientId
where IsActive=1
A client application can call this stored procedure whenever an order list is needed. For example, it can have a
page that displays the list with all order attributes as well as a drop-down control that shows only order numbers and
amounts. In both cases, the same stored procedure can be used—applications just need to ignore any unnecessary
columns in the output while populating the drop-down list.
While this approach helps us reuse the code, it also reuses the execution plan. When we run the stored
procedure, we will get the plan, as shown in Figure
10-1
.
Figure 10-1.
Execution plan of dbo.usp_Orders_GetActiveOrders stored procedure
This execution plan would be used in both cases. Nevertheless, the drop-down control does not need all of the
order attributes, and it can get the required information with the query shown in Listing 10-3.
Listing 10-3.
Code reuse: Select that returns the information required for drop-down control
select OrderId, OrderNumber, Amount
from dbo.Orders
where IsActive=1
Such a query would have a much more efficient execution plan without the join operator, as shown in Figure
10-2
.
Figure 10-2.
Execution plan of the query that returns the order numbers and amounts for the drop-down control
