0

My query is very simple but seriously it takes a hell lot of time to execute. 

SELECT * 
FROM MASTER 
WHERE MASTER.ID NOT IN (SELECT SLAVE.ID 
                        FROM SLAVE 
                        WHERE SLAVE.ID IS NOT NULL)

"Query means that I need all the rows in MASTER sheet which are not present in the slave sheet and I do that by comparing ID of both."

Another option is to use NOT EXISTS instead of NOT IN.

Master has 2000 rows and Slave has 800 rows. It takes like 5-7 minutes in VBA to compute this. Can anyone point out to some better methods or inbuilt functions that I can use please. Thanks.

Community
  • 1
  • 1
Meesha
  • 801
  • 1
  • 9
  • 27

2 Answers2

0

Not in queries tend to be slow. Alternatives are:

where not exists 
(subquery goes here)

or something like this

where yourfield in 
(select yourfield 
from somewhere
except 
select yourfield
from somewhere else)

The two approaches are logically different so you have to know what you are attempting to accomplish before you write your code. I know, we are all supposed to do that anyway.

The second approach is RDBMS specific. Some engines use the word "except". Others use the word "minus". Some might not support this approach at all.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
0

I would look into optimizing your query. Most of the time, a sub-query is slower than a join (Join vs. sub-query) and in my experience, adding several hundred elements to an IN clause causes performance issues.

If you're looking for all the ID's in Master that are not in Slave, I would use

SELECT MASTER.* 
FROM MASTER
LEFT JOIN SLAVE ON MASTER.ID=SLAVE.ID
WHERE SLAVE.ID IS NULL

Also, I would look into your indexes of the tables. If your ID column is unique and is not already indexed, I would go ahead and make that an index.

Community
  • 1
  • 1
Michael
  • 2,158
  • 1
  • 21
  • 26
  • HI, I get an error "Syntax error in from clause" . It that because I need to put ON after Left Join? – Meesha Mar 26 '15 at 13:30
  • Yes, you are correct. I updated the query and tested it on MySQL to make sure it works. Depending on your RDBMS, you might need to tweak it just a little bit. – Michael Mar 26 '15 at 16:18