Speed up a stored procedure

[JohnnyLong]

Hi,

Does anyone know how I can improve the performance of this stored proc?

CREATE PROCEDURE sp_DocumentCategoryFind
@pCandidateID AS int,
@pClientID AS int,
@pContactID AS int
AS
SELECT rc_Documents.DocumentID, Subject, Category, FullPathName, ModifiedDate, ModifiedBy, CandidateID, ClientID, ContactID
FROM rc_Documents INNER JOIN rc_DocumentAttachments ON rc_Documents.DocumentID = rc_DocumentAttachments.DocumentID
WHERE (((CandidateID = @pCandidateID) OR ClientID = @pClientID) OR ContactID = @pContactID)
ORDER BY ModifiedDate DESC

Thanks,

John

 

[vongrunt]

Too few information - table structures would be nice, together with information about number of rows (in tables, result set) and response time.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JohnnyLong]

Thanks vongrunt,

I have just solved it by removing the OR operators. I've used 3 IF statements instead:

CREATE PROCEDURE sp_DocumentCategoryFind
@pCandidateID AS int,
@pClientID AS int,
@pContactID AS int
AS

IF @pCandidateID <> 0
SELECT rc_Documents.DocumentID, Subject, Category, FullPathName, ModifiedDate, ModifiedBy, CandidateID, ClientID, ContactID
FROM rc_Documents INNER JOIN rc_DocumentAttachments ON rc_Documents.DocumentID = rc_DocumentAttachments.DocumentID
WHERE CandidateID = @pCandidateID
ORDER BY ModifiedDate DESC

IF @pClientID <> 0
SELECT rc_Documents.DocumentID, Subject, Category, FullPathName, ModifiedDate, ModifiedBy, CandidateID, ClientID, ContactID
FROM rc_Documents INNER JOIN rc_DocumentAttachments ON rc_Documents.DocumentID = rc_DocumentAttachments.DocumentID
WHERE ClientID = @pClientID
ORDER BY ModifiedDate DESC

IF @pContactID <> 0
SELECT rc_Documents.DocumentID, Subject, Category, FullPathName, ModifiedDate, ModifiedBy, CandidateID, ClientID, ContactID
FROM rc_Documents INNER JOIN rc_DocumentAttachments ON rc_Documents.DocumentID = rc_DocumentAttachments.DocumentID
WHERE ContactID = @pContactID
ORDER BY ModifiedDate DESC


Probably not the most elegant solution, but lightning fast!

John

 

[hmckillop]

You could do it in one go.

SELECT rc_Documents.DocumentID, Subject, Category, FullPathName, ModifiedDate, ModifiedBy, CandidateID, ClientID, ContactID
FROM rc_Documents INNER JOIN rc_DocumentAttachments ON rc_Documents.DocumentID = rc_DocumentAttachments.DocumentID
WHERE (CandidateID = @pCandidateID or @pCandidateID = 0)
AND (ClientID = @pClientID or @pClientID =0)
AND (ContactID = @pContactID or @pContactID =0)
ORDER BY ModifiedDate DESC

I think this should produce the same results but in one query.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[JohnnyLong]

Thanks for that. That is also a quicker query but about a second slower than the my revised one.