How to optimise SQL Query

Question

This query takes 3 seconds to execute, but if you remove the "convert" clause, it is instantly done, and vice versa. Can someone help ?

SELECT DISTINCT 
   N.ID, Groups.IP_Access
FROM         
   Name AS N 
INNER JOIN
   Activity AS A ON N.ID = A.ID 
INNER JOIN
   Groups ON N.ID = Groups.ID
WHERE     
   ((A.ACTIVITY_TYPE = 'LICENCE') AND 
    (A.PRODUCT_CODE = 'IP') AND 
    (A.THRU_DATE IS NULL) AND 
    (A.SOURCE_CODE = 'ICAS') AND 
    (N.MEMBER_TYPE IN ('MM', 'PM','ST', 'SC', 'SE', 'CA', 'CM', 'AC', 'IN', 'BC', 'NM', 'IS', 'RN', 'WEB','PS', 'PP', 'NP')) AND 
    (N.STATUS IN ('A', 'P')) AND 
    (N.ID <> 1) OR 
    CONVERT(bit, ISNULL(Groups.IP_access, 0)) = 1)

What type is `Groups.IP_access`? You might be able to get rid of the `Convert` call entirely — ebo, May 08 '14 at 15:07
I removed the `Convert` and changed to `Groups.IP_access > 0` but it ran the same length of time — Ryan Gillooly, May 08 '14 at 15:09
Why don't you try replacing CONVERT(bit, ISNULL(Groups.IP_access, 0)) = 1) with ISNULL(Groups.IP_access, 0) <> 0 — Malganis, May 08 '14 at 15:51

score 1 · Accepted Answer · edited May 23 '17 at 10:26

This is just a hunch, because it's hard to tell what sort of data you're hoping to pull, but you may be missing some parenthesis around the OR clause. See https://stackoverflow.com/a/1241158/1445356 for more info.

Your query currently has this effective WHERE clause:

WHERE
(
    A.ACTIVITY_TYPE = 'LICENCE'
    AND A.PRODUCT_CODE = 'IP'
    AND A.THRU_DATE IS NULL
    AND A.SOURCE_CODE = 'ICAS'
    AND N.MEMBER_TYPE IN ('MM','PM','ST','SC','SE','CA','CM','AC','IN','BC','NM','IS','RN','WEB','PS','PP','NP')
    AND N.STATUS IN ('A','P')
    AND N.ID <> 1
)
OR Groups.IP_access = 1

is that correct logic? or were you wanting to do this:

WHERE
    A.ACTIVITY_TYPE = 'LICENCE'
    AND A.PRODUCT_CODE = 'IP'
    AND A.THRU_DATE IS NULL
    AND A.SOURCE_CODE = 'ICAS'
    AND N.MEMBER_TYPE IN ('MM','PM','ST','SC','SE','CA','CM','AC','IN','BC','NM','IS','RN','WEB','PS','PP','NP')
    AND N.STATUS IN ('A','P')
    AND (
        N.ID <> 1
        OR Groups.IP_access = 1
    )

How to optimise SQL Query

1 Answers1