2
\$\begingroup\$

I'm trying to fetch records from table PaymentToken if its AccountNumber matched but AccountToken wasn't:

select DISTINCT DTR_PT.Id, DTR_PT.AccountNumber, DTR_PT.CardNumber, DTR_PT.ExpirationDate, DTR_PT.CardHolderName, DTR_PT.AccountToken, DTR_PT.CreatedDate, DTR_PT.Active
from Dev_NeoGateway_DTR.dbo.PaymentToken as DTR_PT 
JOIN Dev_NeoGateway_DTA.dbo.DTA_Customer as DTA_C on DTA_C.Id = DTR_PT.AccountNumber
WHERE DTR_PT.AccountToken NOT IN (select DTA_PI.CardToken FROM  Dev_NeoGateway_DTA.dbo.DTA_PaymentInstrument as DTA_PI )

Because of the WHERE clause, this query takes a lot of time as it queries all the table again and again.

\$\endgroup\$
1
\$\begingroup\$

Linting & Naming:

Your query is overflowing the box to the right. That's not good because it means I (and you) have to scroll to see it completely. Additionally while you use aliases, the table aliases are unnecessarily shortened.

After renaming them a little and reformatting the query looks as follows:

SELECT DISTINCT 
    PaymentToken.Id
    , PaymentToken.AccountNumber
    , PaymentToken.CardNumber
    , PaymentToken.ExpirationDate
    , PaymentToken.CardHolderName
    , PaymentToken.AccountToken
    , PaymentToken.CreatedDate
    , PaymentToken.Active
FROM Dev_NeoGateway_DTR.dbo.PaymentToken AS PaymentToken 
JOIN Dev_NeoGateway_DTA.dbo.DTA_Customer AS Customer
    ON Customer.Id = PaymentToken.AccountNumber
WHERE PaymentToken.AccountToken NOT IN (
    SELECT PaymentInstrument.CardToken 
    FROM Dev_NeoGateway_DTA.dbo.DTA_PaymentInstrument AS PaymentInstrument 
)

Optimizing the query

From what I can see, the Customer table is not used in your query. Joining it only slows down your query.
Other than that, the query is very straightforward. If you could restrict the subselect's result set, you could achieve better performance.

Other than that, the only thing I'd expect to improve perfomance are indices on the involved columns

\$\endgroup\$
0
\$\begingroup\$

I disagree with @Vogel612 that the join is redundant. It is by default an INNER JOIN, therefore, it is required to filter out the PaymentToken rows that do not have a matching Customer ID. It is redundant only if there is already a foreign key relationship that guarantees all PaymentTokens will have a matching Customer ID.

So it looks like you need to add some indexes, or rewrite to the query to something equivalent that uses more sargable conditions. Without more information about your schema and available indexes, it's hard to suggest improvements. Also, please post the execution plan next time.

If the PaymentToken table have an index on the AccountToken and the AccountNumber, it should avoid a cluster index look up in order to filter out the rows. If you expect most of the AccountToken to be valid and the query is only to find a few invalid tokens, having indexes on the PaymentToken.AccountToken and PaymentInstrument.CardToken should allow it to do a quick anti merge join to return only invalid tokens.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.