How do you make your stored procedure run faster

How do you make your stored procedure run faster

Make your stored procedure run faster

Microsoft recommends that you should use the CreateParameter in your scripts.

CREATE PROCEDURE ProductInfo1

@ProductID int

AS

SELECT p.ProductName,

c.CategoryName, s.CompanyName,

p.UnitPrice

FROM Products p INNER JOIN Suppliers s ON

p.SupplierID = s.SupplierID

INNER JOIN Categories c ON

p.CategoryID = c.CategoryID

WHERE p.ProductID=@ProductID

Referring to the stored procedure above but this time we’re going to test it out with our ASP here.

set cn=server.CreateObject("ADODB.connection")

set cmd=server.CreateObject("ADODB.command")

set pProductID= _

cmd.CreateParameter("ProductID",adInteger, _

adParamInput)

set pProductName= _

cmd.CreateParameter("ProductName",adVarChar, _

adParamOutput,40)

set pCompanyName= _

cmd.CreateParameter("CompanyName",adVarChar, _

adParamOutput,40)

set pCategoryName= _

cmd.CreateParameter("CategoryName",adVarChar, _

adParamOutput,15)

set pUnitPrice= _

cmd.CreateParameter("UnitPrice",adCurrency, _

adParamOutput)

cn.Open "Provider=SQLOLEDB.1;Password=;User ID=sa;" & _

"Initial Catalog=Northwind;Data Source=laptop"

set cmd.ActiveConnection=cn

cmd.CommandText="ProductInfo2"

cmd.Parameters.Append pProductID

cmd.Parameters.Append pProductName

cmd.Parameters.Append pCompanyName

cmd.Parameters.Append pCategoryName

cmd.Parameters.Append pUnitPrice