SQL Tuning w/Optional Parameters 1

[benvegiard]

Hi All...

I have a proc that is fired off frequently (about twice a second) and am wrestling w/some tuning issues. I thought I'd get some community input! Following is the proc and my questions:

Proc Parameters: (@UserID varchar(32), @OrgLinkID int)

SELECT TOP 100
d.Consumer_Data_ID as ConsumerID,
a.DMHID ,
c.Last_Name + ', ' + isnull(c.First_Name,'') + ' ' +
isnull(c.Middle_Name,'') + ' '+ isnull(h.CD_Name_Suffix_Value,'') AS ConsumerName
FROM dbo.Recent_Client_List a
INNER JOIN dbo.Person_Identifier b
ON a.DMHID = b.Person_Identifier_Value
INNER JOIN dbo.Person_Data c
ON b.Person_Data_ID = c.Person_Data_ID
INNER JOIN Consumer_Data d
ON d.Person_Data_ID = c.Person_Data_ID
LEFT JOIN CD_Name_Suffix h ON h.CD_Name_Suffix_ID = c.CD_Name_Suffix_ID
WHERE
(@UserID IS NULL OR a.UserID = @UserID)
AND (@OrgLinkID IS NULL OR a.Organization_Link_ID = @OrgLinkID)
ORDER BY a.Recent_Client_List_ID DESC

My main issue is the where clause. Since either, both, or neither parameters may be passed as NULL, handling the where is awkward. As it stands now, I am getting an index scan on the "Recent_Client_List" table. It as about 350,000 rows in it. Obviously, I'd like to convert that to a seek. The only ways I can think of to do that is:

1) Convert to Dynamic SQL. My question is will I kill my performance gains due to having to compile plans for each call.
2) Put in If/Then statements such as:

If @UserID is Null
If @OrgLinkID Is Null
[SQL]
else
[SQL]
else
If @OrgLinkID Is Null
[SQL]
else
[SQL]

Will this option cost me more in re-compiles since SQL will want to re-compile each time the parameters differ enough to force a different path? Yes, I know this is ugly and harder to maintain.

Is there another approach I am missing? Any thoughts on these?

Thanks,
Ben

 

[AlexCuse]

I would rewrite as separate procs for each condition. Then have an 'umbrella' proc that is called to handle your parameters and pass them to the appropriate proc. This will make things easier to maintain and prevent things from being recompiled (if they in fact would be, I just like 4 short procs better than one very long one).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom