ODBC Query So Slow - Why So?

[GRWi]

Hi All
I have an ODBC linked table to a topspeed database() that I want to get data from.
When I create a query where I enter an actual number(e.g.>=12345) in the criteria of ORDERID column, the query gives me the results immediately.
If I look up a different small table for the minimum orderid and enter that in as:
>=[OrderIDMinimum]![MinOfORDERID] and then run this it takes about 25 minutes to run.

The OrderIDMinimum query runs immediately as well.

Even if I reference a number in a field in a form it runs at the slow speed.

I don't understand why it should give the results immediately the first way then take so long the second way.

Why would this be and how do I get the query to speed up

TIA

 

[Banana]

What is the full SQL of the query?

Also, I usually encourage anyone working with ODBC sources via Access to download and read Jet/ODBC whitepaper from support.microsoft.com.

 

[GRWi]

SELECT workings.*
FROM workings, OrderIDMinimum
WHERE (((workings.ORDERID)>=[OrderIDMinimum]![MinOfORDERID]));

 

[Banana]

Is OrderIDMinimum a table? If so, is it linked or local table?

If you execute the same query in the backend, do you get the expected result? It appears to be performing a cross-join using inequality operator which just sounds very slow to me.

What is your ultimate goal with this query? To get minimum number of orders allowed or find the smallest OrderID?

 

[GRWi]

OrderIDMinimum is a query on a separate table to identify the start of the orders I need from the workings table. It runs locally and is immediate when run on its own.
I ultimately want to create a table that will only show the workings of orders >= orders from a specific date. (I am testing on a select query to test prior to a maketable query)

This query works immediately:
SELECT workings.*, workings.ORDERID
FROM workings
WHERE (((workings.ORDERID)>=12345));
the query in the previous post takes 25minutes. I don't understand why there should be such a time difference, if I do:

SELECT workings.*, workings.ORDERID
FROM workings
WHERE (((workings.ORDERID)>=DLookUp("MinOfORDERID","OrdersIDMinimum","MinOfORDERID")));

it runs immediately.

You don't have the URL of the white paper handy, I've tried searching for it and the one you mention is not there.

 

[Banana]

My suspicion is that because the query you posted in previous isn't doing what you may be wanting to do.

For example, if we had two tables;

Dogs
1 Rover
2 Fido
3 Spike
4 Killer

Cats
1 Miffy
2 Fluffy
3 Garfield

If we did a query:
SELECT * FROM Dogs WHERE ID >= 1,

We get only those result-
1 Rover
2 Fido
3 Spike
4 Killer

But if we did this:
SELECT Dogs.* FROM Dogs, Cat WHERE Dogs.ID >= CatID

The result would be:
(Shown) (compared to those cats)
1 Rover 1 Miffy
2 Fido 1 Miffy
2 Fido 2 Fluffy
3 Spike 1 Miffy
3 Spike 2 Fluffy
3 Spike 3 Garfield
4 Killer 1 Miffy
4 Killer 2 Fluffy
4 Killer 3 Garfield

Not quite same results. This is because the slow query is saying "Get me all IDs for dogs where their IDs are greater than cat's ID", not same thing as saying "Get me the all orders after that date.

Did that make sense?

Whitepaper: http://support.microsoft.com/kb/128385[/url

 

[GRWi]

Yes I understand, I'll redesign the query method. Thanks for the link