ORDER BY issue.

Hi All,

If I use ORDER BY in Union query then it take lot of time .

My query looks like.

Select x,y,z
FROM(

SELECT x,y,z
FROM tt,yy,zz

UNION
SELECT x,y,z
FROM tt1,yy1,zz1
) A
WHERE a.x > '03/03/2004'
order by x

Union query return morethan 200000 records.

It's take lot of time around 20 sec if I removerd it then takes 2 sec.
I can put middle(union) part of query in view but I can put ORDER BY in query but I have to use TOP n.

Can I put any index on column in view or else.

Please suggest me asap.one trick to use top n in a view is to use top 100 percent

create view v_crap
as
select top 100 percent *
from t1
order by c2

i know this doesnt solve your main question but it could allow you to create a view until you can tune correctly.|||First, I find it hard to believe that you can retreive 200,000 rows in 2 seconds...

Secons, Do you have an Index on x,y,z?

Third, Try this (make sure you have an index)

SELECT x,y,z
FROM (SELECT TOP 2 x,y,z
FROM tt,yy,zz
WHERE x > '03/03/2004'
ORDER BY x)
UNION
SELECT x,y,z
FROM (SELECT TOP 2 x,y,z
FROM tt1,yy1,zz1
WHERE x > '03/03/2004'
ORDER BY x) A
ORDER BY x