Stored Procedure - A better way?

Question

Post reply

Stored Procedure - A better way?

2Tall

SSChampion

Points: 14644
More actions
November 17, 2009 at 1:44 am

#140035

Hi. I have a basic stored procedure that allows users to search by either ProductID or Product Description on a web form.
I was asked to modify to allow for a more advanced search on Product Description (allow users to search on Product Description or Supplier Description).
The products table has 34000 records.
Stored Procedure as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROC [dbo].[bsp_product_search]
@ProductId NVARCHAR(100),
@ProductDescription NVARCHAR(300)
AS
SELECT DISTINCT TOP 100 ProductId, ProductDescription, CrossReference,SupplierDescription,
STR(ISNULL(SUM(QuantityOutstanding),0),10,2) AS Quantity
FROM Products
LEFT OUTER JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product
LEFT OUTER JOIN dbo.SupplierCrossReferences ON dbo.SupplierCrossReferences.Product = dbo.Products.Product
WHERE (IsNull(RTrim(@ProductId), '') = '' OR [ProductId] LIKE RTrim(@ProductId) + '%')
AND (IsNull(RTrim(@ProductDescription), '') = '' OR [ProductDescription] LIKE RTrim(@ProductDescription) + '%'
OR [SupplierDescription] LIKE RTrim(@ProductDescription) + '%')
GROUP BY Products.ProductId, Products.ProductDescription, Products.CrossReference, Products.StandardPrice,SupplierCrossReferences.SupplierDescription
ORDER BY ProductId ASC
[/Code]
I added another JOIN + OR:
LEFT OUTER JOIN dbo.SupplierCrossReferences ON dbo.SupplierCrossReferences.Product = dbo.Products.Product
OR [SupplierDescription] LIKE RTrim(@ProductDescription) + '%'
The stored proc now takes between 16 & 18 seconds to execute.
Should I write a udf to extract the concatenated product description then call this in the stored procedure?
i.e. ProductDescription + ' ' + SupplierDescription AS ProductDescription
Any ideas on how I might improve on what I have?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

See this link for Catch-All queries

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Clear Sky SQL
My Blog[/url]

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks Dave, I will take a look.

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

DISTINCT TOP 100? If run without distinct does it really return duplicates? If so, why are there duplicates? If not, remove that distinct, it's expensive.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

2Tall SSChampion Points: 14644 More actions · Answer 4

DISTINCT

There must have been a reason at the time....I will check and remove if not required.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper