Not In Operator for Sub query too slow

[UnderGround]

Hi ,
I have a simple query but there are too many records in the database
around 90630 that is making the query to time out. The sql for the
query is..


INSERT INTO TblDbSinksBasinOrder ( JobNumber, OrderID, DateRecAdded )
SELECT tblSinkBasin.JobNumber, tblSinkBasin.ID, Date() AS DateRecAdded
FROM tblSinkBasin
WHERE (tblSinkBasin.ID Not In (select OrderID from TblDbSinksBasinOrder
where orderID is not null));


I just want to add records form the tblSinkBasin table into
TblDbSinksBasinOrder table but i only want to add records that are not
already in TblDbSinksBasinOrder. The TblSinkBasin table get new records
every day.

The only way that i could think of doing it that might work was to add
a dateRecordWasAdded field in the tblSinkBasin table so there is a date
for every record. now when i run my query run a sun query to get the
max date that is in tblDBSinksBasinOrder table and then get all the
recods from TblSinksBasin which are greater than that date.
I would like to avoid this as i do not want to add a new field in
TblSinkBasin.
Any advice??
Thanks in advance

 

[Albert D. Kallal]

FROM tblSinkBasin

WHERE (tblSinkBasin.ID Not In (select OrderID from TblDbSinksBasinOrder
where orderID is not null));

Why do you restrict the test all non null? That is a big list!!!

Try restrcing the test list to ONLY one record...

Try this:

WHERE (tblSinkBasin.ID Not In (select OrderID from TblDbSinksBasinOrder
where orderID = tblSinkBasin.id));

However, you can also just set a unique index on tblDBSinksBasinrder.OrderID
field.

IF you set a unique index on orderid, then any append query will simply
ignore (fail) to insert the same order id again. That is the easy way to do
this....

However, give both of the above ideas a try and see which one runs faster.
if the orderid field is indexed, then performance should be rather fast...

 

[Guest]

Access/Jet is optimised for joins:

INSERT INTO TblDbSinksBasinOrder ( JobNumber, OrderID, DateRecAdded )
SELECT tblSinkBasin.JobNumber, tblSinkBasin.ID, Date() AS DateRecAdded
FROM tblSinkBasin left join TblDbSinksBasinOrder on
tblSinkBasin.ID = TblDbSinksBasinOrder.OrderID
where TblDbSinksBasinOrder.OrderID is null

(david)

 

[Guest]

IF you set a unique index on orderid, then any append query will simply

ignore (fail) to insert the same order id again. That is the easy way to do
this....

But it does it by adding all the records, indexing, then deleting the
ones which conflict. That can take a while, and it also (used to?)
cause database bloating.

(david)

 

[Albert D. Kallal]

But it does it by adding all the records, indexing, then deleting the

ones which conflict. That can take a while, and it also (used to?)
cause database bloating.

I did not think it does the above...the key violations are skipped during
insert.

As a side note, however, I also agree, and also have used your other
suggestion of using a join to test for missing records (as you show in your
other post). I know for sure that one is fast..and that is how I done it for
years....

 

[Guest]

Can't be 'skipped' during insert, because they are reported
as errors. And index is created, because it is index values that
are compared. Does index value creation imply that record
has been assigned a location in target database?

In any case, I stopped using that method because of severe
bloating problems on an A2K database with a large daily import.

Dunno if it ever got fixed, because I've never tried it since -
it was much faster to add 50 records than it was to add 1000
records and fail on 950.

Interesting note on the join method: it's not always fast. I had
a big text database where I had to join and look for nulls, and
I never did find a way to make it fast. Jet is quite good at
finding one record or matching sets in a table: but perhaps it
does repeated scans to find nulls.

(david)

 

[UnderGround]

Hi,
Thanks alot for you responses.

Albert i cant restrict my records to one record. I want to be able to
say get all records from table A which are not in table B and insert
them into table B. I have added indexes to all the fields that are
going to be joined on. i tried your way of creating a unique index but
it did not make a difference. I still get the timeout.

David i tried your way and it worked now the query runs and gives me
the data, But now i have another problem. When i run the query from
access interface it runs without any problem but when i call the same
query from code using the docmd command it gives me a timeout. I also
tried by running the sql for the query using currentDB but did not make
a difference. Then i also tried the way i had thought originally. I
added a date field in the tblSinkBasin table which contains the date
the record was added to that table. Now in my query i run a sub query
to get the max date in tblSinkBasinOrder table and select and insert
only those records from tblSinkBasin that have date greater than the
Max date. I have the same problem with this way also. when i run it
with the access interface it runs in seconds but when i run it thought
code using docmd or currentDB it gives me the timeout error. i tried to
increase the timeout of the query by opening it in design mode and
going into properties and changeing the odbc timeout and saving the
query but it does not work (i think the query does not even run till
the new timeout) and give me an error.
Any ideas.
Thanks

 

[Guest]

You have misunderstood Alberts suggestion. His example
showed that he was restricting the Not In clause to only the
matching rows. That is, for each record in the query, he
restricted the subclause to that one record. Try it and see.

Unless that method fixes the problem, I suggest you repost
as "Timeout using CurrentDB.Execute". Post the SQL
again, and ask why you get a timeout by one method
but not the other.

(david)

 

[UnderGround]

Thanks alot. I see what Albert meant and tried it still the same
result. The query runs in a flash in access but times out when used in
code. I will post the sql again under Timeout using CurrentDB.Execute.