Day 16

Quiz

1:When could a stored procedure cause serious performance problems?
A1: Stored procedures can cause problems when a stored optimization plan is unsuitable for the current set of parameters.
2:Can you give an example of the previous answer?
A2: Certain parameter sets will call for the use of an index, and others are better suited to a table scan. Use of the wrong optimization plan will cause a significant performance problem. You should use the RECOMPILE option. A typical example is a search based on a date range as shown. 
Create procedure sp_datename (@startdate Datetime, @enddate Datetime)
As
Select *
From tablename
Where date Between @startdate and @enddate

Exercises

1:The following procedure seems to take a long time to process. Can you identify the reason and correct it? 
       Create Procedure Sales_by_State
  (@start_date datetime, @end_date datetime)
As
Select od.productid, od.quantity, od.unitprice, ord.customerid
  into #tp_Sales
From orders as ORD inner join
     [Order Details] as OD on
     ord.orderid = od.ordered
Where ORD.orderdate between @start_date and @end_date
Select cst.region,
       sum(tp.quantity * tp.unitprice)
From Customers as cst left outer join
     #tp_sales as tp on
     cst.customerid = tp.customerid
Group by tp.region

A1: As you have seen in this lesson, the reason this procedure is performing poorly is because the optimization of the procedure must guess at how large the temporary table will be. To fix this procedure, you need to break it up into two steps. First, you create two procedures; the first inner procedure will retrieve data from the temporary table, and the outer procedure will call the inner one to perform the complete process. The following SQL batch is an example of how this can be done: 
        Select od.productid,
       od.quantity,
       od.unitprice,
       ord.customerid
Into #tp_Sales
From orders as ORD inner join
     [Order Details] as OD on
     ord.orderid = od.ordered
Go
Create Procedure Sub_Sales_by_State
With Recompile
As
Select cst.region,
       sum(tp.quantity * tp.unitprice)
From Customers as cst left outer join
     #tp_sales as tp on
     cst.customerid = tp.customerid
Group by tp.region
Go
Drop Table #tp_sales
Go
Create Procedure SP_Sales_by_State
  (@start_date datetime, @end_date datetime)
As
Select od.productid,
       od.quantity,
       od.unitprice,
       ord.customerid
Into #tp_Sales
From orders as ORD inner join
     [Order Details] as OD on
     ord.orderid = od.ordered
Where ORD.orderdate between @start_date and @end_date
        Execute Sub_Sales_by_State
        Go

2:Create a procedure that updates the price of a product, passing the price and its product ID. This update must verify that the product is available (quantity >0) and the new price is greater than $12.00. If the update fails for any reason, return an error. Make sure to use transaction processing.
A2: There are many ways to work with transactions within a procedure. Using the methods discussed in this lesson, the following is an example of one way to use transactions: 
   Create Procedure sp_Update_products
  (@prodID int, @price Money)
As
Declare @err int, @rowcount int
If @price <= $12
Begin
   Raiserror ('Price entered must be greater than $12 dollars', 16,1)
   Return 99
End
Begin Transaction
   Save Transaction sp_update_products
     Update products
       Set unitprice = @price
       Where productid = @prodID
     Select @err = @@error
     Select @rowcount = @@rowcount
     If @err <> 0
     Begin
       Rollback Transaction sp_update_products
       Commit Transaction
       Return 98
     End
     If @rowcount = 0
     Begin
       Print 'Product ID does not exist, exiting procedure'
       Commit Transaction
       Return 97
     End
     If Isnull((Select UnitsInStock
        From Products
        Where productid = @prodID), 0) <= 0
     Begin
       Raiserror ('Product is no longer available', 16, 1)
       Rollback Transaction sp_update_products
       Commit Transaction
       Return 96
     End
Commit Transaction
Return 0

To test this procedure, you can use the following batch:

   Declare @ret_code int
Execute @ret_code = sp_update_products 146, $15
If @ret_code <> 0
Begin
  Raiserror ('Return Code is %d', 0, 1, @ret_code)
  Return
End
       Product is no longer available
Return Code is 96

3:Extra Credit: Create a system procedure that displays the usernames and login names of everyone using the current database.
A3: The usernames are in the system table Sysusers and the login names are in the Master..Sysusers table. (Hint: to create a system stored procedure, you must log in to the Master database as the system administrator SA, using the assigned password.) 
   Create Procedure sp_Users_New
As
Select users.name, login.name
From sysusers as users Inner Join
     Master..sysusers as login on
     users.uid = login.uid

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset