MVSFORUMS.com

vijay · Beginner Joined: 09 May 2003 Posts: 131 Topics: 64

Hi,
I'm reading a table in an online program.

ID - Key

Status - (data part of the table)
timestamp - (data part of the table)

I need to show all records in the table (14 records per page) sorted in the following order status:timestamp:ID.

For paging logic I use

if
tbl-status|tbl-timestamp|tbl-id > w-status|w-timestamp|w-id

The logic works.But the response is slow because it is going thru a tablespace scan.

Any suggestions regarding the display of the table contents sorted by the
columns in the data part and not the key of the table.

VIjay

Manas Biswal · Posted: Fri Aug 26, 2005 3:57 pm Post subject:

vijay,

Its not the tablespace scan but actually the sort that is killing you. Basically, everytime this query is run (for each page), the whole table's data is sorted on status, timestamp and id and then the qualifying (greater than the last displayed record) records are fetched and returned.
If the table is small, then using your logic is OK. But if the table is big, then you can try other options. I am assuming that your online program is coded Pseudo -conversationally. The problem with pseudo-conversation is that useful facilities like scrollable cursors, GTTs etc. are rendered useless. Anyway, the only way that I can think off is - On the first invocation of your transaction, read the entire table using an order by clause and put it in a TSQ. After that, subsequent paging can retrieve data sequentially from the TSQ. The initial loading of the TSQ will take some time, but overall, the performance will improve.

HTH...Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

a_seshu · Posted: Mon Oct 24, 2005 1:24 pm Post subject:

Hi Vijay,

Why dont you think the option of creating an index on the table with the columns.

ID, Status, timestamp

preferrably an ascending index...

CREATE INDEX <INDEX NAME> ON <TABLENAME> (ID ASC, STATUS ASC, TIMESTAMP ASC)

This would work for you...
_________________
I dont think I would ever stop learning. - Seshu.

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Just to add to the last suggestion:
You should tell the optimizer, that you only intend to fetch the first forteen rows of the answer set by coding in the cursor definition "OPTIMIZE FOR 14 ROWS". With that the usage of the before mentioned index is likely, even when the data is not clustered.

regards
Christian