May 24, 2010 at 5:18 am
Hi,
I have 2 tables both of them have large number of rows.
I need to select the recent updated row from any of the two tables. I have written below query. how can i optimize it.
Please help.
[Code]
select * from (
select top 1 * from (
select * from
(
select top 1 * from A order by CurrentTime desc
)A1
union
select * from
(
select top 1 * from B order by CurrentTime desc
)A2
) C order by CurrentTime desc
) temp
union
.
.
.
[/Code]
May 24, 2010 at 5:43 am
You could use the following code:
SELECT TOP 1 *
FROM
(
SELECT TOP 1 * FROM A ORDER BY CurrentTime DESC
UNION ALL
SELECT TOP 1 * FROM B ORDER BY CurrentTime DESC
) C ORDER BY CurrentTime DESC
But the best performance gain would be if you have an index on CurrentTime on each of the tables involved.
Side note: both tables have to have the same number and order of columns if you really want to return all fields. You should consider replacing SELECT * with a defined list of columns.
May 24, 2010 at 6:00 am
Hi and Thanks,
So this is the only way from which i can get the result (by union) ?
I should use UNION ALL in place of UNION.
May 24, 2010 at 6:18 am
descentflower (5/24/2010)
Hi and Thanks,So this is the only way from which i can get the result (by union) ?
I should use UNION ALL in place of UNION.
It's definitely not the only way to get the result. You could store the top 1 from each table in a temp table and get the results from there (probably slower than the UNION approach).
Or you could re-evaluate your database design for the root cause of having two (or more?) tables with identical structure. But there is not enough informaion available to give any advice towards one table, maybe with horizontal partitioning...
The UNION ALL will give you both results even if they are identical. UNION will remove duplicates.
Check the actual execution plan and see if you have an index seek on both tables. If so, it's probably the best you can get.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply