469,291 Members | 1,777 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

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
)
)
Jul 20 '05 #1
2 4337
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
)
)

Jul 20 '05 #2
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?

....
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by imani_technology_spam | last post: by
115 posts views Thread by Mark Shelor | last post: by
10 posts views Thread by Daniel Billingsley | last post: by
22 posts views Thread by Kevin Murphy | last post: by
4 posts views Thread by sql_server_user | last post: by
5 posts views Thread by steven.fafel | last post: by
3 posts views Thread by laurentc via AccessMonster.com | last post: by
3 posts views Thread by bharadwajrv | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.