Results 1 to 3 of 3

Thread: ORDER BY issue.

  1. #1
    Join Date
    Nov 2003
    Location
    NJ USA
    Posts
    19

    Unanswered: 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.
    M. Jain

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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)

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •