help with sp

[Dinora]

Hi everyone,

Can you please help me optimize this sp. All indexes on WHERE fields exist. It takes too long to get results.

CREATE PROCEDURE [dbo].[SearchSNs]
@SerialNumber serialnumber = NULL,
@ProductID smallint = NULL,
@CustomerID int = NULL,
@SalesOrderNumber varchar(25) = NULL,
@InvoiceNumber varchar(15) = NULL,
@PackingSlip varchar(15) = NULL,
@BusinessVerticalCode smallint = NULL,
@ShipDateFrom smalldatetime = NULL,
@ShipDateTo smalldatetime = NULL,
@WarrantyDateFrom smalldatetime = NULL,
@WarrantyDateTo smalldatetime = NULL
AS
BEGIN
SET @SerialNumber = REPLACE(@SerialNumber, '*', '%')
SET @SalesOrderNumber = REPLACE(@SalesOrderNumber, '*', '%')
SET @InvoiceNumber = REPLACE(@InvoiceNumber, '*', '%')
SET @PackingSlip = REPLACE(@PackingSlip, '*', '%')

IF @ShipDateFrom IS NULL SET @ShipDateFrom = CONVERT(SMALLDATETIME, '01/01/1950', 101)
IF @ShipDateTo IS NULL SET @ShipDateTo = CONVERT(SMALLDATETIME, '12/31/2078', 101)
IF @WarrantyDateFrom IS NULL SET @WarrantyDateFrom = CONVERT(SMALLDATETIME, '01/01/1950', 101)
IF @WarrantyDateTo IS NULL SET @WarrantyDateTo = CONVERT(SMALLDATETIME, '12/31/2078', 101)

SELECT sn.SerialNumber, sn.SNID, convert(varchar(100), sn.WarrantyDate, 1) AS [WarrantyDate], prod.ProductNumber,
o.InvoiceNumber, o.PackingSlip, c.[Name], sn.SNStatusCode, psc.Description, sn.WarrantyStatusCode, wsc.Description AS WarrantyStatus,
prod.Description AS ProductDescription, o.SalesOrderNumber, convert(varchar(100), o.SalesOrderDate,1) AS [SalesOrderDate],
convert(varchar(100), o.EstimatedShipDate, 1) + ' Est.' AS [EstimatedShipDate], convert(varchar(100), o.ShipDate,1) AS [ShipDate],
bvc.[Description] AS [BusinessVertical], c.Comments AS [Company Notes]
FROM SNs AS sn
INNER JOIN Products AS prod ON sn.ProductID = prod.ProductID
INNER JOIN OrderDetails AS od ON sn.SNID = od.SNID
INNER JOIN Orders AS o ON o.OrderID = od.OrderID
INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID
INNER JOIN SNStatusCodes AS psc ON sn.SNStatusCode = psc.SNStatusCode
INNER JOIN WarrantyStatusCodes AS wsc ON sn.WarrantyStatusCode = wsc.WarrantyStatusCode
INNER JOIN BusinessVerticalCodes AS bvc ON o.BusinessVerticalCode = bvc.BusinessVerticalCode

WHERE (sn.SerialNumber LIKE @SerialNumber OR @SerialNumber IS NULL)
AND (sn.ProductID = @ProductID OR @ProductID IS NULL)
AND (c.CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (o.SalesOrderNumber LIKE @SalesOrderNumber OR @SalesOrderNumber IS NULL)
AND (o.InvoiceNumber LIKE @InvoiceNumber OR @InvoiceNumber IS NULL)
AND (o.PackingSlip LIKE @PackingSlip OR @PackingSlip IS NULL)
AND (o.BusinessVerticalCode = @BusinessVerticalCode OR @BusinessVerticalCode IS NULL)
AND (o.ShipDate BETWEEN DATEADD(day, -1, @ShipDateFrom) AND @ShipDateTo)
AND (sn.WarrantyDate BETWEEN DATEADD(day, -1, @WarrantyDateFrom) AND @WarrantyDateTo)

ORDER BY sn.SerialNumber Asc
END

Thanks.

[mattyblah]

Too many joins on the query. I'd have a max of 5 joins and even that is pushing it. Yes, it is possible, but the overhead is ridiculous.