no response / very slow when use "NOT IN" 1

[bzeng]

Hi,
I am supporting a web based online management system with
DB backend: MS Access2000
IIS5.0/ASP3.0

The major problem is that the advanced search page doesn't respond when the query contains &quot;<field1) NOT IN (select <field2> from...)&quot;. It seems the query runs very slow or experience some problem at the backend. So I tried to diagnose the problem by run some testing query against the Access DB directly.

I had a very weird problem by using a very simple query in the MS Access database (2000).

select * from contact where contactid not in (select contactid from contactprofile)

it takes almost 20-30 secs to return. I don't have much data in these two tables. (less than 5000 records for each table) I also found quite a few other queries are also quite slow by using &quot;NOT IN&quot;. The advanced search page doesn't respond only when the query triggers the &quot;NOT IN&quot; sql. I have never seen such a problem before. I even import the data into MSSQL Server, but it works fine.

Is that something to do with MS Access DB or something is wrong with my data? (I have tried another PC, same problem)


Thanks in advance.


Brian

 

[lespaul]

I have found that in general using a NOT IN clause makes the queries run slower. I think it has to do with the fact that it has to check each record in the main query with each result in the sub query.

My solution: I try to aviod using NOT IN in my queries!! I know that doesn't help you much, but maybe someone will have a better answer!

Leslie

 

[Golom]

You might try

   Select Distinct Contact.* 
   From Contact INNER JOIN ContactProfile
        ON Contact.ContactID <> ContactProfile.ContactID

It should be able to use indexes where the &quot;NOT IN&quot; version probably can't.

 

[bzeng]

Hi,
Thanks for all the replies!

I have tried the sql

Select Distinct Contact.*
From Contact INNER JOIN ContactProfile
ON Contact.ContactID <> ContactProfile.ContactID

directly on the database, it doesn't respond at all. (Maybe it takes even longer..) I have checked that the contactid in both tables are set to Indexed=Yes. The strange thing is that in my complicated sql statement sometimes it works fine with a few &quot;NOT IN&quot; querying different tables(some of the tables have the same amount of data), but most of the time it doesn't work. I wonder if there is something wrong with my data or indexing or is this a known Microsoft problem???

I doubt that the file is corrupted as it works fine when there is no &quot;NOT IN&quot;. I even created a brand new Access file and transfered everything across. But I still had no luck.


Is there some kind of limitation on MS Access when the SQL Statement gets really complicated? Well, actually, even I tried to run some simple NOT IN sql, it took ages. Haven't mention the speed over the ASP pages... well of course it ends up with IIS time out.

Again, all the script respond instantly in SQL Server with the same data...


thanks again.

 

[Golom]

Sigh ... worth a try I guess.

Now that I think about it ... I vaguely recall that access can use indexes on =, <=, >= comparisons but not on <>. So its probably doing much the same as your NOT IN is doing.

SQL Server being much smarter probably can use indexes for that comparison.

Just out of curiosity (not that I think that it's the cure) can you try

Select Distinct Contact.* 
   From Contact INNER JOIN ContactProfile
        ON NOT (Contact.ContactID = ContactProfile.ContactID)

 

[SonOfEmidec1100]

Try this (should 'fly') make sure ContactID is indexed in both tables

SELECT * FROM Contact LEFT JOIN ContactProfile
ON Contact.ContactID = ContactProfile.ContactID WHERE ContactProfile.ContactID is NULL

 

[bzeng]

Bingo!

The following script respond in 1 sec.

SELECT * FROM Contact LEFT JOIN ContactProfile
ON Contact.ContactID = ContactProfile.ContactID WHERE ContactProfile.ContactID is NULL

I have to go to the next steps --> fix the ASP page
which were not responding. The sql on the ASP page is totally different but this is a milestone. Will let you guys know soon.


thanks very much!

 

[bzeng]

Hi,
Thanks very much again for the script

SELECT * FROM Contact LEFT JOIN ContactProfile
ON Contact.ContactID = ContactProfile.ContactID WHERE ContactProfile.ContactID is NULL

It fixed all the problems I had in the system. Just out of curiosity what could have been the problem for the &quot;NOT IN&quot; query. Is that a known problem with MS Access 2000? (Maybe I should upgrade MS Access to XP or 2003)

I was stuck up all night for the problem last night and didn't go to sleep until 7am in the morning! Again I really appreciate all of you who replied to me.

Shall I just leave the problem to Microsoft or should I check my db so that I can use a simple query like &quot;NOT IN&quot;. Again if it is not recommended, I will give up using it. But I wonder...



Brian

 

[bzeng]

Hi, I want to exit the FOR loop when the rs0.EOF is true, but I got an error message.

Here is the code
--------------------------------
...
do while not rs0.EOF
%>
<tr align=center>
<%for i=1 to 4
if isNull(rs0(&quot;studentid&quot) then
bgcolor=&quot;white&quot;
notinclass=true
else
bgcolor=&quot;#FF9966&quot;
end if%>
<td width=25%><table border=1 bgcolor=&quot;<%=bgcolor%>&quot; bordercolor=&quot;gray&quot; width=&quot;100&quot;><tr><td> </td></tr></table><br><%=rs0(&quot;sname&quot%></td>
<% if rs0.EOF then
Exit For
else
rs0.movenext
end if
next%>
</tr>
<%
loop

...
--------------------------------------------

the error message is

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/HS/app/Admin/ClassStudent.asp

It seems that something to do with rs0.moveNext. the way to find out is to move &quot;rs0.movenext&quot; out of the for loop (nothing else is changed) and then the code works. (But the following testing code does not give me the results I want)

--------------------------------

do while not rs0.EOF
%>

<tr align=center>

<%for i=1 to 4
if isNull(rs0(&quot;studentid&quot) then
bgcolor=&quot;white&quot;
notinclass=true
else
bgcolor=&quot;#FF9966&quot;
end if%>

<td width=25%><table border=1 bgcolor=&quot;<%=bgcolor%>&quot; bordercolor=&quot;gray&quot; width=&quot;100&quot;><tr><td> </td></tr></table><br><%=rs0(&quot;sname&quot%></td>
<% if rs0.EOF then
Exit For
else
end if
next%>
</tr>
<%
rs0.movenext

loop

--------------------------------------------


I wonder what I have done wrong...


thanks in advance


Brian

 

[cmmrfrds]

You will probably need to rearrange your logic. When you do a movenext in the floor loop and the EOF condition is true, it is going back to the beginning of the for loop and referencing the record even though it is EOF. Best to check (immediately) for EOF after the movenext or some other change in the logic flow.

I see you found a good solution on the first problem of using the NOT IN which requires reading the entire table in any database not just Access. A NOT EXISTS will jump out on the first record so it will not read the entire table. If you are forced to use that type of logic the EXISTS is preferable to the IN for efficiency.

 

[SonOfEmidec1100]

Im not sure this will achieve what you want but maybe just replacing the EXIT FOR with EXIT DO will help.