--- In firebird-support@yahoogroups.com, "Terry Child" <tmchild@g...>
wrote:

>
> Hi All
>
> I have the following table:
>
> CREATE TABLE "USER_LOCATION"
> (
> "ID" NUMERIC(18,0) NOT NULL,
> "USERNAME" VARCHAR(64) NOT NULL,
> "FIXTIME" TIMESTAMP NOT NULL,
> "LATITUDE" NUMERIC(9,0) NOT NULL,
> "LONGITUDE" NUMERIC(9,0) NOT NULL,
> PRIMARY KEY ("ID")
> );
>
> Each entry is the location of a user at a give point in time (from

their GPS).

>
> The ID field is derived from a generator and I'd like to use the

following query to get the latest position of each user.

>
> SELECT * FROM user_location WHERE id IN (SELECT max(id) FROM

user_location group by username)

>
> The subquery works fast enough on its own but the combined query is

slow (~6000 entries spread over 10 users).

>
> Am I right in assuming that I should do this in code as separate

queries instead of trying to combine everything into a single query?

>
> All suggestions gratefully received.
>
> Regards
>
> Terry Child
>

Hello Terry,

Just a couple of comments.

An index should be available on username, fixtime.
I would also advise you to use max(fixtime) instead of max(id)

SELECT * FROM user_location WHERE id IN (SELECT max(FixTime) FROM
user_location group by username)

They probably mean the same thing in your implementation, but assuming
ID is given by a generator, it may cause an issue later.

But I would attack this differently

Add a table to hold the current location for each user.

CREATE TABLE USER_CURRENT_LOCATION
(
USER_LOCATION_ID NUMERIC(18,0) NOT NULL,
USERNAME VARCHAR(64) NOT NULL
);

Create a stored procedure to record their current location

CREATE PROCEDURE SP_SET_USER_LOCATION
(
USERNAME VARCHAR(64) NOT NULL,
FIXTIME TIMESTAMP NOT NULL,
LATITUDE NUMERIC(9,0) NOT NULL,
LONGITUDE NUMERIC(9,0) NOT NULL
)
RETURNS
(
ID NUMERIC(18,0) NOT NULL
)
AS
BEGIN
ID = GEN_ID(GEN_USER_LOCATION_ID, 1);

INSERT INTO USER_LOCATION (....) VALUES (....);

UPDATE USER_CURRENT_LOCATION
SET USER_LOCATION_ID = :ID
WHERE USERNAME = :USERNAME;
END
^

Then to get the current location, it is a simple join.

Adam