SQL speed SSRS optional fields

Question

I'm returning results from the following query which is taking too long when running. I'm not sure how to eliminate the where parameters when they are not used in SSRS. All the @variables are strings

select S.SBSB_ID, LTRIM(RTRIM(M.MEME_FIRST_NAME)) + ' ' + 
LTRIM(RTRIM(M.MEME_LAST_NAME)) AS Names,
(CASE M.MEME_REL WHEN 'M' THEN 'Subscriber'
                             WHEN 'S' THEN 'Son'
                             WHEN 'D' THEN 'Daughter'
                             WHEN 'W' THEN 'Wife'
                             WHEN 'H' THEN 'Husband'
                             WHEN 'O' THEN 'Other'
                             ELSE M.MEME_REL END) AS Relation,
(CASE A.PRPR_ID WHEN 'NONASSIGNED' THEN A.CLCL_PA_ACCT_NO
                          ELSE LTRIM(P.PRPR_NAME) END) AS ProvName,
LTRIM(RTRIM(L.CDDL_CUR_STS)) AS Status
FROM CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M 
ON S.SBSB_CK = M.SBSB_CK INNER JOIN CMC_CDDL_CL_LINE L 
ON L.MEME_CK = M.MEME_CK INNER JOIN CMC_PRPR_PROV P 
ON P.PRPR_ID = L.PRPR_ID INNER JOIN CMC_CLCL_CLAIM A 
ON A.CLCL_ID = L.CLCL_ID
WHERE 
S.SBSB_ID LIKE (CASE @subscriberID 
WHEN '' THEN '%' ELSE @subscriberID END) AND
M.MEME_REL IN (@Relation) AND
UPPER(M.MEME_FIRST_NAME) LIKE '%' + 
UPPER(CASE @firstName WHEN '' THEN '%' ELSE @firstName END) + '%' AND
UPPER(M.MEME_LAST_NAME) LIKE '%' + 
UPPER(CASE @lastName WHEN '' THEN '%' ELSE @lastName END) + '%' AND
(L.CGCG_ID IN (@Category) OR L.CGCG_ID = '') AND

(CASE WHEN (@Tooth) = '' THEN L.CDDL_TOOTH_BEG
WHEN ISNUMERIC(@Tooth) = 0 THEN UPPER(@Tooth)
WHEN LEN(@Tooth) = 1 THEN '0' + @Tooth
ELSE @Tooth END) >= L.CDDL_TOOTH_BEG AND

(CASE WHEN (@Tooth) = '' THEN L.CDDL_TOOTH_BEG
WHEN ISNUMERIC(@Tooth) = 0 THEN UPPER(@Tooth)
WHEN LEN(@Tooth) = 1 THEN '0' + @Tooth
ELSE @Tooth END) <= L.CDDL_TOOTH_END AND

S.SBSB_CK IN (select SBSB_CK FROM CMC_MEME_MEMBER 
WHERE MEME_SSN LIKE (CASE @SSN WHEN '' THEN '%' ELSE @SSN END)) AND
M.MEME_BIRTH_DT LIKE (CASE WHEN @DOB IS NULL THEN '%' ELSE @DOB END)

UNION

select S.SBSB_ID, LTRIM(RTRIM(M.MEME_FIRST_NAME)) + ' ' + 
LTRIM(RTRIM(M.MEME_LAST_NAME)) AS Names,
(CASE M.MEME_REL WHEN 'M' THEN 'Subscriber'
                             WHEN 'S' THEN 'Son'
                             WHEN 'D' THEN 'Daughter'
                             WHEN 'W' THEN 'Wife'
                             WHEN 'H' THEN 'Husband'
                             WHEN 'O' THEN 'Other'
                             ELSE M.MEME_REL END) AS Relation,
RTRIM(LTRIM(P.PRPR_NAME)) AS ProvName,
'Purged - ' + H.CLDH_STS AS Status
FROM CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M 
ON S.SBSB_CK = M.SBSB_CK
INNER JOIN CMC_CLDH_DEN_HIST H ON H.MEME_CK = M.MEME_CK
INNER JOIN CMC_PRPR_PROV P ON P.PRPR_ID = H.PRPR_ID
WHERE 
S.SBSB_ID LIKE (CASE @subscriberID 
WHEN '' THEN '%' ELSE @subscriberID END) AND
M.MEME_REL IN (@Relation) AND
UPPER(M.MEME_FIRST_NAME) LIKE '%' + 
UPPER(CASE @firstName WHEN '' THEN '%' ELSE @firstName END) + '%' AND
UPPER(M.MEME_LAST_NAME) LIKE '%' + 
UPPER(CASE @lastName WHEN '' THEN '%' ELSE @lastName END) + '%' AND
(H.CGCG_ID IN (@Category) OR H.CGCG_ID = '') AND

(CASE WHEN (@Tooth) = '' THEN H.CLDH_TOOTH_BEG
WHEN ISNUMERIC(@Tooth) = 0 THEN UPPER(@Tooth)
WHEN LEN(@Tooth) = 1 THEN '0' + @Tooth
ELSE @Tooth END) >= H.CLDH_TOOTH_BEG AND

(CASE WHEN (@Tooth) = '' THEN H.CLDH_TOOTH_BEG
WHEN ISNUMERIC(@Tooth) = 0 THEN UPPER(@Tooth)
WHEN LEN(@Tooth) = 1 THEN '0' + @Tooth
ELSE @Tooth END) <= H.CLDH_TOOTH_END AND

S.SBSB_CK IN (select SBSB_CK FROM CMC_MEME_MEMBER 
WHERE MEME_SSN LIKE (CASE @SSN WHEN '' THEN '%' ELSE @SSN END)) AND
M.MEME_BIRTH_DT LIKE (CASE WHEN @DOB IS NULL THEN '%' ELSE @DOB END)

I would rather have the query not executing blank fields with '%' but was not sure how to eliminate them conditionally from the query.

Answer 1

You'll definately hit some performance issues with what you have. The only other way to do this is to use IF / ELSE logic and test each @Parameter.

You check if the parameter is not null and if it isnt add it to the where clause. This eliminates the need for

@Paramer = table.FieldName OR @Parameter IS NULL

Answer 2

1. Make sure you've got appropriate indexes on the database to support your query. Get the database to dump out the access plan and figure out what tables are being accessed using a full table scan rather than an index. Try to add indexes or hints or whatever to get rid of the full table scans.

2. If you're using Oracle or some other database which supports the NVL function you can rewrite

.

@Parameter = table.Fieldname OR @Parameter IS NULL

as

NVL(@Parameter, table.Fieldname) = table.Fieldname

I've found this can improve performance dramatically as the second formulation is more likely to allow an index to be used while the first one often devolves into a full table scan.