ADODB ACEODBC Query with NOT IN condition takes long

[Shreedhar]

Hi Gurus, I am using ADODB (ACEODBC driver) to Query Workbook/sheets, The worksheet does not have heavy volume data (~1500 rows) I use LIKE /NOT LIKE filters in the WHERE clause and it returns the result in a jiffy.

however, that's not the case with NOT IN condition, takes long time but return the result, could you please suggest ways to improve performance/ any suggestions would immensely help me.

As Always, Thanks for your time

Regards, Shreedhar

 

[Chihiro]

NOT IN by it's nature will take longer, it has to iterate over record set.
Instead of using NOT IN. Do a Left Join and filter the result on null or not null.

Edit: Normally I'd use NOT EXISTS(subquery) in t-sql. But not sure if you can use it in ODBC query.

 

[Shreedhar]

NOT IN by it's nature will take longer, it has to iterate over record set.
Instead of using NOT IN. Do a Left Join and filter the result on null or not null.

Edit: Normally I'd use NOT EXISTS(subquery) in t-sql. But not sure if you can use it in ODBC query.

Thanks will try not exists.

 

[Chihiro]

If that doesn't work, try the LEFT JOIN/IS NULL. It is semantic equivalent to NOT EXISTS.

Select l.*
FROM Table_Left as l
LEFT JOIN
    Table_Right as r
ON r.Column = l.Column
WHERE r.Column IS NULL

FYI - If you are using actual SQL server, not ODBC to Excel, 'NOT IN' will be faster than LEFT JOIN / IS NULL, but there is difference in how NULLs are handled.

 

[Shreedhar]

If that doesn't work, try the LEFT JOIN/IS NULL. It is semantic equivalent to NOT EXISTS.

Select l.*
FROM Table_Left as l
LEFT JOIN
    Table_Right as r
ON r.Column = l.Column
WHERE r.Column IS NULL

FYI - If you are using actual SQL server, not ODBC to Excel, 'NOT IN' will be faster than LEFT JOIN / IS NULL, but there is difference in how NULLs are handled.

Thanks Chihiro, Compared to NOT IN condition, LEFT JOIN with NULL Check is super fast.