StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL speed SSRS optional fields
text
Body
copied!<p>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</p> <pre><code>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) </code></pre> <p>I would rather have the query not executing blank fields with '%' but was not sure how to eliminate them conditionally from the query.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload