How to avoid looping?

  • Thread starter Thread starter timeOday
  • Start date Start date
T

timeOday

I have the following query which is invoked in a loop for each value of
account_id; otherwise the query is identical each time.


"select * from updates where account_id=33 and update_time >= 343837
limit 1"

That is, the first update for the accounts at or after the specified time.

All the account ids can be retrieved with the query:

"select account_id from accounts"

The looping seems wrong. Surely there's a way to do it with a single
SQL query, but I can't put my finger on it.
 
You need a subquery. Here's an example that works in the Northwind
sample database:

SELECT *
FROM Orders AS A
WHERE OrderDate = (
SELECT MIN(OrderDate)
FROM Orders AS B
WHERE (B.CustomerID = A.CustomerID)
AND (B.OrderDate >= #1998/04/30#)
)
;

CustomerID corresponds to account_id, and OrderDate to update_time.
 
John said:
You need a subquery. Here's an example that works in the Northwind
sample database:

SELECT *
FROM Orders AS A
WHERE OrderDate = (
SELECT MIN(OrderDate)
FROM Orders AS B
WHERE (B.CustomerID = A.CustomerID)
AND (B.OrderDate >= #1998/04/30#)
)
;
Click to expand...

Thank you very much John.

I'm having an efficiency problem doing it that way.
It seems to consider each and every order in turn, retaining only those
which have the OrderDate as computed by the subquery - even though only
1 order for each CustomerID can meet that condition. The query is
correct, but slow.

In contrast, the "looping" approach:

(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
1) AND (B.update_time >= 631) limit 1)
UNION
(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
2) AND (B.update_time >= 631) limit 1)
UNION
(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
3) AND (B.update_time >= 631) limit 1)
UNION
....

runs efficiently, but the query is huge and repetitive. Surely there's
a better way?
 
Perhaps the following will work for you. I'm not sure I completely
understand your query, so I may be off-base.

SELECT U.*
FROM Updates as U INNER JOIN
(SELECT Account_ID, Min(Update_Time) as FirstTime
FROM Updates as U2
WHERE U2.Update_Time >= 343837) as U3
ON U.Account_Id = U3.AccountID
AND U.UpDate_Time = U3.FirstTime

You will not be able to update records identified using this query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thank you very much John.

I'm having an efficiency problem doing it that way.
It seems to consider each and every order in turn, retaining only those
which have the OrderDate as computed by the subquery - even though only
1 order for each CustomerID can meet that condition. The query is
correct, but slow.

In contrast, the "looping" approach:

(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
1) AND (B.update_time >= 631) limit 1)
UNION
(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
2) AND (B.update_time >= 631) limit 1)
UNION
(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
3) AND (B.update_time >= 631) limit 1)
UNION
...

runs efficiently, but the query is huge and repetitive. Surely there's
a better way?
Click to expand...

This is a classic problem-that-is-solved-by-a-subquery, and I don't
know any other way of doing it in SQL.

I don't rate your 'looping' UNION query as a solution, because the SQL
depends on the number of records in the table and most if not all
implementations of SQL have a pretty low limit on the number of UNIONs
per query.

Have you indexed the update_time and account_ID fields?
 
(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
1) AND (B.update_time >= 631) limit 1)
UNION
(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
2) AND (B.update_time >= 631) limit 1)
UNION
(SELECT update_time, account_id FROM updates AS B WHERE (B.account_id =
3) AND (B.update_time >= 631) limit 1)
UNION
...
Click to expand...

I forgot to say, none of my SQL books seems to mention a LIMIT
keyword. Even if it exists in your dialect, how can it work
consistently without an ORDER BY clause?
 
Be sure to try John Spencer's suggestion: it's still a subquery, but
looks as if it might be faster than mine.
 
Back
Top