471,896 Members | 2,143 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,896 software developers and data experts.

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 4449
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
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.