How to optimize below query

Question

SELECT TOP (10) A.NotificationID
,A.ID

FROM [TABLE-1] A(NOLOCK)
WHERE NOT EXISTS (
SELECT 1
FROM [TABLE-1] B(NOLOCK)
WHERE A.ID = B.ID
AND B.STATUS = 1
)
AND EXISTS (
SELECT 1
FROM [TABLE-1] B(NOLOCK)
WHERE A.ID = B.ID
AND B.STATUS = 0
)
AND NOT EXISTS (
SELECT 1
FROM [TABLE-2] C(NOLOCK)
WHERE A.ID = C.ID AND A.NotificationID=C.NotificationID
)
AND NOT EXISTS (
SELECT 1
FROM [TABLE-2] C(NOLOCK)
WHERE A.ID = C.ID
GROUP BY ID
HAVING COUNT(1) >= 1
)
AND A.IsService = 0
AND A.CreateDate > GETDATE()-1

Answer 1

The first important question is, or what I saw...

a lot of type conversion hints in the execution plan...

why are you defining such columns as

A.[ID] varchar NULL,

B.[ID] [bigint] NULL,

So every time you are doing a comparison A.ID=B.ID , it has to be converted from varchar to bigint...
same for the B.[STATUS] = varchar(200)

That may speed up your query in the first step...

Answer 2

Hi @sourav dutta ,

One SQL query optimization technique is to use Location instead of Own. where the query executes faster than the query that has it. where the query filters the records before creating the group, and the query filters the data in the group. Therefore, using WHERE instead of HAVING is a simple strategy for SQL query optimization.

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".