By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

subquery performance mystery

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.