subquery performance mystery

[lev]

CREATE TABLE [dbo].[LOG]
(
[TYPE] [smallint] NULL ,
[TIME_STAMP] [datetime],
[ID] [varchar] (44)
)

ID is non-unique. I want to select all IDs where the last entry for
that ID is of type 11.

Below is the query that I used. Notice that the subquery used is a
nested (not correlated) subquery meaning that it doesn't use results
of outer query. This subquery should only be executed once. However,
on large number of rows (3 million), this query never returns.

I have also attempted to run subquery separately. That takes 1 minute.
Then I put the results in temp table and joined that temp table with
the main query. That takes about 2 minutes.

Unfortunately, that solution is unacceptable to us since we have to
support both MSSQL and Oracle with the same queries, and the syntax
for temp tables or table variables is different in Oracle.

Mysterious.

Here's the query:

-- main query
select ID
from log
where ID in
(
-- subquery
select id from log l1
where time_stamp =
(
select max(time_stamp)
from log l2
where l2.id = l1.id
and l2.type = 11
)
)

[Gert-Jan Strik]

Does this run in Oracle?

SELECT Log.ID
FROM Log
INNER JOIN (
SELECT ID,MAX(Time_Stamp) AS MaxTimeStamp
FROM Log
GROUP BY ID
) AS X
ON X.ID = Log.ID
AND X.MaxTimeStamp = Log.Time_Stamp
WHERE Log.Type=11

Hope this helps,
Gert-Jan
lev wrote:

CREATE TABLE [dbo].[LOG]
(
[TYPE] [smallint] NULL ,
[TIME_STAMP] [datetime],
[ID] [varchar] (44)
)

ID is non-unique. I want to select all IDs where the last entry for
that ID is of type 11.

Below is the query that I used. Notice that the subquery used is a
nested (not correlated) subquery meaning that it doesn't use results
of outer query. This subquery should only be executed once. However,
on large number of rows (3 million), this query never returns.

I have also attempted to run subquery separately. That takes 1 minute.
Then I put the results in temp table and joined that temp table with
the main query. That takes about 2 minutes.

Unfortunately, that solution is unacceptable to us since we have to
support both MSSQL and Oracle with the same queries, and the syntax
for temp tables or table variables is different in Oracle.

Mysterious.

Here's the query:

-- main query
select ID
from log
where ID in
(
-- subquery
select id from log l1
where time_stamp =
(
select max(time_stamp)
from log l2
where l2.id = l1.id
and l2.type = 11
)
)

[lev]

Gert-Jan,

Thanks a million, that helps a lot. A similar query also involving
"derived tables" works on oracle - I haven't had a chance to try
yours. Thanks again.

Lev.
Gert-Jan Strik <so***@toomuchspamalready.nl> wrote in message news:<3F**************@toomuchspamalready.nl>...

Does this run in Oracle?

....