Any recommendations on what I can do to improve the performance of this query? Currently a similar n/search pulling the same columns/filters is twice as performant. This is for a duplicate po check for an entity. Timings: n/Search: 958ms n/Query: 1715ms

SELECT t.id internalid
    , t.tranid
    , t.entity
    , t.trandate
    , ts.fullName status
    , t.otherrefnum
    , t.ForeignTotal amount
    , tsa.addr1 shipaddr1
    , tsa.city shipcity
    , tsa.state shipstate
    , tsa.zip shipzip
FROM TRANSACTION t

LEFT JOIN TransactionShippingAddress tsa ON tsa.nkey = t.ShippingAddress
LEFT JOIN transactionstatus ts on ts.id = t.status AND ts.tranType = 'SalesOrd'

WHERE t.type = 'SalesOrd'
    AND t.entity = ?
    AND t.trandate >= ADD_MONTHS(SYSDATE, -60)
    AND t.otherrefnum = ?

Do you need LEFT join or can you do INNER join?

Inner would be possible on the status. If they pull the address from the account does it pull it into the transactionshippingaddress table, or would it only end up on the address book as the nkey? (Most of our addresses are one-time). Even removing the joins, and just a count was still showing the search being quicker.

Yeah that's tricky... NetSuite query performance is a bit of an enigma... we struggle with it constantly

Instead of using a LEFT JOIN with TransactionStatus, how about using BUILTIN.DF(t.status) instead?

BUILTIN.DF has to run post-processing once per row. A join in theory should be faster and less resource intensive

Ok, I thought you can remove the second left join.

If you're just interested in finding dupes you might want to look into GROUP BY ... HAVING COUNT(*) > 1 construct

Ultimately we do a dupe check, and then need to query the data to send out an email. I think I'm going to move it to a scheduled script after we confirm a dupe so the after submit doesn't have added wait time.