optimization of sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

optimization of sp

Author

Topic

Dinora
Starting Member

3 Posts

Posted - 2007-04-03 : 14:32:26

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 14:59:40

No index will be used for records searched with leading wildcard character.

Peter Larsson
Helsingborg, Sweden

Dinora
Starting Member

3 Posts

Posted - 2007-04-03 : 15:13:32

Can you please, be more explicit?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 15:22:51

When you use LIKE with a leading wildcard such as "%slip" to search, the present index (if any) can't be used for that column.
The query slows down.

However, if you search with "mart%", an existing index can be used.

Peter Larsson
Helsingborg, Sweden

Dinora
Starting Member

3 Posts

Posted - 2007-04-03 : 15:55:11

Yes, I agree with you, but when I am executing this sp given just 1 full parameter like ProductID and another are nulls, it takes around 15 seconds to retrieve 50507 rows.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-03 : 16:19:57

if sn.SerialNumber is never null in your data, you might get better results adding logic like this in the beginning:

IF @SerialNumber is null SET @SerialNumber = '%'

and then changing your WHERE to read:

WHERE (sn.SerialNumber LIKE @SerialNumber) and ...

do this for all non-nullable columns that you are searching this way. This will help a little since you are removing OR's from your WHERE clause, and it has the same effect.

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-04-04 : 01:27:07

I've mentioned this before on this forum, but I'm not sure whether its just Folklore! However:

Try changing:

(sn.SerialNumber LIKE @SerialNumber OR @SerialNumber IS NULL)

to

(@SerialNumber IS NULL OR sn.SerialNumber LIKE @SerialNumber)

because the first "case" is easier for SQL Server to resolve.

I would be VERY interested to hear if this makes any difference pls.

By the by, why do you do this?

IF @ShipDateFrom IS NULL SET @ShipDateFrom = CONVERT(SMALLDATETIME, '01/01/1950', 101)
...
AND (o.ShipDate BETWEEN DATEADD(day, -1, @ShipDateFrom) AND @ShipDateTo)

instead of

AND (@ShipDateFrom IS NULL OR o.ShipDate >= @ShipDateFrom)
AND (@ShipDateTo IS NULL OR o.ShipDate <= @ShipDateTo)

(beware of a Date part present on @ShipDateTo, in which case you will need to use "<" and modify @ShipDateTo to be the next day at 00:00:00)

Where we have this type of multiple-choice WHERE clause on very large tables we pre-process some of the parameters into a #TempTable and JOIN that in the WHERE clause for the remainder of the less-used parameters, so something like:


IF @SerialNumber IS NOT NULL
BEGIN
    INSERT INTO #TempTable (SerialNumber)
    SELECT sn.SerialNumber
    FROM   SNs AS sn
    WHERE  sn.SerialNumber LIKE @SerialNumber
END
ELSE
IF @ProductID IS NOT NULL
BEGIN
    INSERT INTO #TempTable (SerialNumber)
    SELECT sn.SerialNumber
    FROM   Products AS prod
           INNER JOIN SNs AS sn
               ON sn.ProductID = prod.ProductID
WHERE sn.ProductID = @ProductID
END
... etc

Kristen

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-04-04 : 02:07:14

in addition to Peter's comments about leading wildcards, this type of query can't utilize an index either, due to the "or @param is null"

select fieldList
from table
where (someField = @id) or (@id is null)

this will cause a table scan. when u have many conditions in a single proc, the proc should execute helper procs, which can be optimized for the particular conditions -- and utilize indexes

Kristen
Test

22859 Posts

Posted - 2007-04-04 : 02:49:23

EDIT: Added results from a bigger table. Sorry: original Northwind test is on SQL2000, MyBigTable is on SQL2005

Starting with:


DECLARE	@City	nvarchar(15),
	@Region	nvarchar(15)
SELECT	@City = 'London', @Region = NULL

SELECT	*
FROM	dbo.Customers
WHERE	(City = @City)
GO

I get:
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Customers]))
|--Index Seek(OBJECT:([Northwind].[dbo].[Customers].[City]), SEEK:([Customers].[City]=[@City]) ORDERED FORWARD)

Table 'Customers'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.

|--Nested Loops(Inner Join, OUTER REFERENCES:([MyDB].[dbo].[MyBigTable].[MyPK_ID], [Expr1003]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([MyDB].[dbo].[MyBigTable].[IX_MyInt]), SEEK:([MyDB].[dbo].[MyBigTable].[MyInt]=[@MyInt]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[MyBigTable].[PK_MyBigTable]), SEEK:([MyDB].[dbo].[MyBigTable].[MyPK_ID]=[MyDB].[dbo].[MyBigTable].[MyPK_ID]) LOOKUP ORDERED FORWARD)

Table 'MyBigTable'. Scan count 1, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


WHERE	    (@City IS NULL OR City = @City)

gives me a table scan on the Clustered PK:

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), WHERE:([@City]=NULL OR [Customers].[City]=[@City]))

Table 'Customers'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

(logical reads is smaller 'coz the table has very few rows, I suppose)

|--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyBigTable].[PK_MyBigTable]), WHERE:([@MyInt] IS NULL OR [MyDB].[dbo].[MyBigTable].[MyInt]=[@MyInt]))

Table 'MyBigTable'. Scan count 1, logical reads 60468, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


WHERE	    (@City IS NULL OR City = @City)
	AND (@Region IS NULL OR Region = @Region)

uses the same approach:

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), WHERE:(([@City]=NULL OR [Customers].[City]=[@City]) AND ([@Region]=NULL OR [Customers].[Region]=[@Region])))

Table 'Customers'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

|--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyBigTable].[PK_MyBigTable]), WHERE:(([@MyInt] IS NULL OR [MyDB].[dbo].[MyBigTable].[MyInt]=[@MyInt]) AND ([@MyDate] IS NULL OR [MyDB].[dbo].[MyBigTable].[MyDate]=[@MyDate])))

Table 'MyBigTable'. Scan count 1, logical reads 60440, physical reads 3, read-ahead reads 60326, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Turning the WHERE clause round, so that the @Parameter test is first:


WHERE	    (City = @City OR @City IS NULL)

gives:

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Customers]))
|--Index Scan(OBJECT:([Northwind].[dbo].[Customers].[City]), WHERE:([Customers].[City]=[@City] OR [@City]=NULL))

Table 'Customers'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.

|--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyBigTable].[PK_MyBigTable]), WHERE:([MyDB].[dbo].[MyBigTable].[MyInt]=[@MyInt] OR [@MyInt] IS NULL))

Table 'MyBigTable'. Scan count 1, logical reads 60469, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Changing the WHERE clause to:


WHERE	    (City = @City OR @City IS NULL)
	AND (Region = @Region OR @Region IS NULL)

gives:

|--Filter(WHERE:([Customers].[Region]=[@Region] OR [@Region]=NULL))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Customers]))
|--Index Scan(OBJECT:([Northwind].[dbo].[Customers].[City]), WHERE:([Customers].[City]=[@City] OR [@City]=NULL))

Table 'Customers'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.

|--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyBigTable].[PK_MyBigTable]), WHERE:(([MyDB].[dbo].[MyBigTable].[MyInt]=[@MyInt] OR [@MyInt] IS NULL) AND ([MyDB].[dbo].[MyBigTable].[MyDate]=[@MyDate] OR [@MyDate] IS NULL)))

Table 'MyBigTable'. Scan count 1, logical reads 60440, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

~~I guess I should try a larger table ...~~


COUNT(*)    COUNT(DISTINCT MyInt)
----------- -----------
2,873,581   43,179

There are 10 rows WHERE MyInt = 6630180

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources