CODEWITHSUNDEEP
sqlsql-servert-sqlquery-optimization
Ryan Gillooly
Ryan Gillooly

Reputation: 315

How to optimise SQL Query

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)

Upvotes: 1

Views: 77

Answers (1)

arserbin3
arserbin3

Reputation: 6158

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
    )

Upvotes: 1

Related Questions