SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Slow sproc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 07/29/2005 :  05:45:18  Show Profile  Send Bex an AOL message  Reply with Quote
I have a query that takes a number of optional parameters. The time in which it takes is over 20 seconds (and with only a few months of data). Could you please have a look at it and tell me what I can do to improve the return time:

/* 
PURPOSE: 	Procedure to search for document sets across clients
FUNCTIONALITY: The database is searched and returns all the documents that match 
		any of the variables provided. This can either be a single document
		set or multiple, depending on the level of the search.
NOTE: FOR INTERNAL USE ONLY
CREATOR: Rebecca Starr
CREATED: 28th June 2005
*/
CREATE      PROCEDURE [dbo].[usp_SEARCHDocuments]
(
   @clientID INT = NULL
,  @accountNumber VARCHAR(15) = NULL
,  @invoiceNumber VARCHAR(20) = NULL
,  @documentDate SMALLDATETIME = NULL
,  @handsetNumber VARCHAR(12) = NULL
,  @accountName VARCHAR(35) = NULL --HAS TO BE REMOVED
,  @recipientName VARCHAR(45) = NULL
,  @limit SMALLINT = NULL
)
AS
SET NOCOUNT ON
--Obtains the accountID from the account table based on ClientID and AccountNumber
DECLARE @accountID INT
SET @accountID = (SELECT AccountID FROM Account 
			WHERE AccountNumber = @accountNumber
			AND ClientID = @clientID
			)
/*Selects the information based on the parameters passed. If no variables are passed, 
then all the data in the database is returned.*/
--An invoice number, handset number, etc can identify a document set or a single document
--In order to return all the documents of a document set, a nested select is performed
--1) First Part: Applies a limit of how many document sets are to be returned using ROWCOUNT
--2) Second Part: Creates a table variable and all the document sets that match the search
--   criteria are inserted into the variable @docSetLimit
--3) Third Part: Selects all the documents that belong to the document sets contained in the
--   table variable (inner join @docSetLimit)
IF @limit IS NULL SET @limit = 0
SET ROWCOUNT @limit
DECLARE @docSetLimit TABLE
(
DocSetID BIGINT
)
INSERT INTO @docSetLimit (DocSetID)
SELECT DS.DocumentSetID
FROM Client C 
	LEFT OUTER JOIN Account A ON C.ClientID = A.ClientID
	LEFT OUTER JOIN DocumentRecipient DR ON A.AccountID = DR.AccountID
	LEFT OUTER JOIN Document D ON DR.DocumentRecipientID = D.DocumentRecipientID
	LEFT OUTER JOIN Handset H ON D.DocumentID = H.DocumentID
	INNER JOIN DocumentSet DS ON DS.DocumentSetID = D.DocumentSetID
	INNER JOIN [File] F ON F.FileID = DS.FileID
	WHERE (@clientID IS NULL OR A.ClientID = @clientID)
	AND (@accountNumber IS NULL OR AccountNumber = @accountNumber)
	AND (@invoiceNumber IS NULL OR DocumentInvoiceNumber = @invoiceNumber)
	AND (@documentDate IS NULL OR CONVERT(VARCHAR,DocumentDate,106) = @documentDate)
	AND (@handsetNumber IS NULL OR HandsetNumber = REPLACE(@handsetNumber, ' ', ''))
	AND (@accountName IS NULL OR DocumentRecipientName LIKE ('%' + @accountName + '%'))--THIS IS TO BE REMOVED
	AND (@recipientName IS NULL OR DocumentRecipientName LIKE ('%' + @recipientName + '%'))
	AND (F.StatusID = (SELECT StatusID FROM Status WHERE StatusName = 'Accessible'))
	GROUP BY DS.DocumentSetID
SET ROWCOUNT 0
SELECT    DocumentRecipientName
	, DocumentRecipientPostcode
	, DocumentInvoiceNumber
	, DocumentDate
	, DocumentPageCount	
	, D.DocumentID
	, HandsetNumber
	, AccountNumber
	, DocumentSequenceNumber
	, DocumentSetID
	FROM Client C LEFT OUTER JOIN Account A ON C.ClientID = A.ClientID
		LEFT OUTER JOIN DocumentRecipient DR ON A.AccountID = DR.AccountID
			LEFT OUTER JOIN Document D ON DR.DocumentRecipientID = D.DocumentRecipientID
				LEFT OUTER JOIN Handset H ON D.DocumentID = H.DocumentID
					INNER JOIN @docSetLimit T1 ON T1.DocSetID = D.DocumentSetID
	ORDER BY AccountNumber, DocumentInvoiceNumber, DocumentSetID, DocumentSequenceNumber, DocumentDate


GO


When running a sample query against the database using the sproc, it was performing many hash joins (due to the left outer joins) and many clustered index scans, etc. Is there any method to re-gig the format to improve performance???

Hearty head pats

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/29/2005 :  06:46:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
scans probably happen because of ('%' + @recipientName + '%')

i have improved performance by changing the order of parameters in the where
instead of
AND (@accountNumber IS NULL OR AccountNumber = @accountNumber)
try
AND (AccountNumber = @accountNumber OR @accountNumber IS NULL)

play with that... it can speed up things.
try changing the order of left and inner joins... sometimes that may help.

i assume you have all neccessary indexes in place...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/29/2005 :  07:26:29  Show Profile  Reply with Quote
Couple of suggestions:

Put a PK on @docSetLimit

Put any of there WHERE clause bits that relate to specific JOINs in the JOIN instead

You've got an INNER JOIN to DocumentSet that has a reference to a column in Document that was the target of an OUTER JOIN - I think you'll have a problem with that!

Re-gig the "LIKE '%' + @recipientName + '%'" - that's an index killer. One answer to that might be to create a TEMP table up front of PKs for records that match the recipient name, and then join that TEMP table instead of the wildcard.

AND (@documentDate IS NULL OR CONVERT(VARCHAR,DocumentDate,106) = @documentDate)

Convert @documentDate to a datetime upfront - this way no index etc. can be used for the DocumentDate column because of the CONVERT on it

AND (@handsetNumber IS NULL OR HandsetNumber = REPLACE(@handsetNumber, ' ', ''))

Ditto - preprocess the @handsetNumber variable to remove spaced. SQL might be smart enough to optimise this into the query for you, but it might not ...

AND (F.StatusID = (SELECT StatusID FROM Status WHERE StatusName = 'Accessible'))

This looks useful, how selective is it? [What percentage of records have this StatusID?)

Other thoughts:

Prefix all your table names by the owner (i.e. "dbo.")

You could do a multiple pass into your temporary table; we use that on massive wildcard searches. You need to do the most selective ones first though .... otherwise you start manipulating huge temporary tables needlessly

(Its not obvious to me which tables the various where clause column names live in, as they don;t have Alias prefixes, so I may have this example a bit screwy!)

IF @clientID IS NOT NULL
BEGIN
    INSERT INTO @docSetLimit (DocSetID)
    SELECT DS.DocumentSetID
    FROM Client C 
    WHERE ClientID = @clientID
END
ELSE
IF @invoiceNumber IS NOT NULL
BEGIN
    INSERT INTO @docSetLimit (DocSetID)
    SELECT DS.DocumentSetID
    FROM Client C 
	JOIN Account A ON C.ClientID = A.ClientID
	JOIN DocumentRecipient DR ON A.AccountID = DR.AccountID
	JOIN Document D ON DR.DocumentRecipientID = D.DocumentRecipientID
    WHERE DocumentInvoiceNumber = @invoiceNumber
END
ELSE
...

then join @docSetLimit to the original tables with a complete WHERE clause as in your original - that will then hopefully be working with a much reduced record set, and hopefully be quicker

Kristen
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 07/29/2005 :  08:09:15  Show Profile  Send Bex an AOL message  Reply with Quote
Thank you both for your replies. I have printed them out and I am going to go through your suggestions and try them all out. I shall post back the (hopefully successful) results!

Thanks again for taking the timeout to help me! Gosh, I have SOOOOO much to learn!

Hearty head pats
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 07/29/2005 :  08:32:08  Show Profile  Send Bex an AOL message  Reply with Quote
Hiya Kristen

I have been going through the recommendations and am a little confused on how to put the where clauses into the joins. Would it be possible for you to give me an example??

Hearty head pats
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 07/29/2005 :  08:47:44  Show Profile  Reply with Quote
ORDER BY AccountNumber, DocumentInvoiceNumber, DocumentSetID, DocumentSequenceNumber, DocumentDate


That is another time chewer right there. Does it NEED to be returned in any specific order? Can the client order it instead?

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/29/2005 :  11:16:42  Show Profile  Reply with Quote
"how to put the where clauses into the joins"

Change

...
LEFT OUTER JOIN Handset H ON D.DocumentID = H.DocumentID
...
WHERE ...
	AND (@handsetNumber IS NULL OR HandsetNumber = REPLACE(@handsetNumber, ' ', ''))

to

...
LEFT OUTER JOIN Handset H 
	 ON D.DocumentID = H.DocumentID
	AND (@handsetNumber IS NULL OR HandsetNumber = REPLACE(@handsetNumber, ' ', ''))
...
WHERE ...

its only an educated guess, but if there are no matching rows in the Handset table I think this will be faster than the WHERE clause having to deduce it, and if you do NOT need any rows returned if @handsetNumber IS NULL.

There might be some extra stuff needed because if the ONLY parameter given was @handsetNumber and there were NO matches, AND you used an OUTER JOIN you will match all rows in the Client table, but if you use an INNER JOIN then you will NOT get any rows UNLESS the Client table has at least one row in the Handset table ... but that's down to you to adjust that according to your needs.

"Does it NEED to be returned in any specific order"

As there's a

SET ROWCOUNT @limit

I reckon a repeatable order is probably important!

Kristen
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 08/01/2005 :  07:55:47  Show Profile  Reply with Quote
SET ROWCOUNT @limit

Yea, that MIGHT be important.



*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000