var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Alternate to Top or Rownum
I have an application which uses Sybase ASA 220.127.116.1101. It has a table with about 5 million rows with the following structure.
ID INTEGER NOT NULL,
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,
Probably the easiest method would be to:
SET ROWCOUNT x
SELECT INTO #tempTable
SET ROWCOUNT 0
Then join the temp table to the outer table.
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 00:17.