CODEWITHSUNDEEP
ms-access
Vibhu
Vibhu

Reputation: 13

Optimize NOT IN query in Access SQL

I need to optimize the SQL Query

select * from tblProdutcs 
where ProductCode Not in 
(SELECT ProductCode From tblPrice 
WHERE DateExtracted=#15-Sep-2013# 
order by ProductCode) 
order by ProductCode

This is taking far too long (about 45-60 seconds currently.

Upvotes: 1

Views: 58

Answers (1)

Ismail Sahin
Ismail Sahin

Reputation: 2710

  • Remove order by ProductCode in the inner query because the outher query does ordering as well

  • Be sure that both of your tables have index insert an id field which is primary key, unique and autoincerement (if neccessary) to both tables

  • Also try this query. it may increase speed as well, but I don't know why. It should be asked if there is a better performance effect between these two ways

    SELECT pro.* FROM tblProducts pro LEFT JOIN tblPrice pri ON pro.ProductCode = pri.ProductCode WHERE pri.ProductCode IS NULL AND pri.DateExtracted=#15-Sep-2013# ORDER BY pro.ProductCode

Upvotes: 2

Related Questions