Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Location
    India
    Posts
    6

    Unanswered: Alternate to Top or Rownum

    Hi all,


    I have an application which uses Sybase ASA 8.0.2.3601. It has a table with about 5 million rows with the following structure.


    ID INTEGER NOT NULL,
    NAME VARCHAR(100),
    UPDATEIME NUMERIC(19,0),
    ...
    OWNERNAME VARCHAR(25) NOT NULL,
    PRIMARY KEY ("ID","OWNERNAME")).


    Also, it has two index namely.
    CREATE INDEX Event0_ndx ON Event("ID")
    CREATE INDEX Event1_ndx ON Event( "OWNERNAME")


    The client application can request the data from any range (say 1000, 1050) and optionally sorted by any column in the table.


    I tried using the top command to work around this (similair to in MSSQL, where we can use sort and top in inner query). But, the problem here is that SYBASE doesnot support ORDER BY in inner queries.


    As a result, if the client requests the data from a range with come sorting order, I have to do very inefficient resultset processing to show the data.


    It would be really helpful if any one could help me improve the performance.


    Thanks & Regards,
    J.Sujith Regan.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Probably the easiest method would be to:

    SET ROWCOUNT x

    SELECT INTO #tempTable
    FROM innerTable

    SET ROWCOUNT 0

    Then join the temp table to the outer table.
    Thanks,

    Matt

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    6

    Question

    Thanks MattR for your reply. I have a few queries.

    1. Will creating temporary tables affect the performance, espicially if the client request is in the order of 10/min (operation on a 5 million DB).

    2. Should the temporary tables be deleted explicitly or will the database handle it?

    3. I did some search and learnt that Sybase has number(*), which is said to be equivalent to rownum. Can that be used? Kindly advice on this.

    Thanks a lot for your help.
    Last edited by jsregan; 02-12-03 at 01:17.

Posting Permissions

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