CODEWITHSUNDEEP
sqlsql-serverperformance
OKaradayi
OKaradayi

Reputation: 3

How to get better performance this sql query?

How to get better performance this sql query using cross apply? 

SELECT  DISTINCT TOP 10  acc.account_no
      FROM account (NOLOCK) acc
      CROSS APPLY(
       SELECT COUNT(1) AS piece
         FROM account (NOLOCK) acc2
        WHERE acc.account_no= acc2. account_no
          AND status= 'P'
      ) AS X

      CROSS APPLY(
       SELECT COUNT(1) AS piece2
         FROM account  (NOLOCK) acc2
        WHERE acc.account_no = acc2. account_no 
      ) AS Y
    WHERE X.piece= Y.piece2

Upvotes: 0

Views: 73

Answers (2)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

Seems like you need the account numbers where all the statuses are of value 'P'. Not sure about your TOP 10 without ORDER BY...

SELECT DISTINCT TOP 10 account_no
FROM  Account a (NOLOCK)
WHERE NOT EXISTS(
  SELECT 1 FROM account a1 (NOLOCK) WHERE a1.status <> 'P' AND a.Account_No = a1.Account_No)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Use Group By and Having 

SELECT account_no
FROM   Account
GROUP  BY account_no
HAVING Count(CASE
               WHEN status = 'P' THEN 1
             END) = Count(1)

Upvotes: 2

Related Questions