Suggestion needed on search/filter page design



IBM's flagship relational database management system

Suggestion needed on search/filter page design

Postby BChat » Mon Jun 15, 2009 9:55 pm

Hi all,

We have a online master table search/filtering screen. As of now it has 18 inputs criteria & you can fill up 1 or many of them. The first filled up field determines the sort fields of the listing. To achieve this we have 18 x 2 SQLs in the program (one for UP one for DOWN for each sort order) and similarly some 20-22 index's in the table for index cover.

Now the required enhancement is a sort functionality on the selected 1 of the 6 displayed cols. But to achieve this following the same strategy we are ending up with 100s of SQLs & similar counts of indexes.

The typical SQL for below scenario is like::

FIELD01 - FIELD18 are the 18 search criteria possible
FIELD10 is the 1st search criteria entered
FIELD15 followed by REC_TIMESTAMP is the sort order selected. REC_TIMESTAMP is an unique id for a rec.

SELECT FIELD01, FIELD02, ... FIELDnn
FROM MY_TABLE
WHERE SYSTEM_ID = :WS-SYSTEM-ID
AND FIELD01 BETWEEN :WS-FIELD01-LOW AND :WS-FIELD01-HIGH
AND FIELD02 BETWEEN :WS-FIELD02-LOW AND :WS-FIELD02-HIGH
...
AND FIELD18 BETWEEN :WS-FIELD18-LOW AND :WS-FIELD18-HIGH
AND FIELD10 = :WS-FIELD10-LOW
AND (FIELD15 > :WS-FIELD15
OR (FIELD15 = :WS-FIELD15
AND REC_TIMESTAMP > :WS-LAST-REC-TIMESTAMP))
ORDER BY FIELD15, REC_TIMESTAMP
FOR FETCH ONLY OPTIMIZE FOR 15 ROWS
WITH UR;

For entered search criteria both LOW & HIGH will have the entered value. For others it will be LOW-VALUE & HIGH-VALUE respectively.


Please suggest any better approach? I am really skeptical of response of this approach & I am sure the next thing coming-up is sort across multiple cols

We are using DB2 V8 on CICS-COBOL. Tentative data volume is >= 50000, 100000 rec stress test. And being an online screen, response time is important; will need as much direct indexed access as possible.

Thanks,
Bchat
BChat
 
Posts: 19
Joined: Thu Jun 11, 2009 8:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Suggestion needed on search/filter page design

Postby dick scherrer » Wed Jun 17, 2009 5:25 am

Hello,

If someone is interested in response time, 50k to 100k rows should not be processed. . .

Suggest generating dynamic sql be considered.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Suggestion needed on search/filter page design

Postby BChat » Wed Jun 17, 2009 9:01 pm

dick scherrer wrote:Hello,

If someone is interested in response time, 50k to 100k rows should not be processed. . .

Suggest generating dynamic sql be considered.


Thanks for the suggestion. But even our present performance cannot be achieved with dynamic sql. The run-time bind kills. Thanks anyway.

BChat
BChat
 
Posts: 19
Joined: Thu Jun 11, 2009 8:20 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post