Browse Widget Population

[KMoody]

I'm populating a dynamic temp-table for a browse widget.

The user can request:

• the table,
• the where condition, and
• which fields to show.

However, sometimes users want to see an entire table. In one example, such a request results in 137,656 entries in the browse widget temp-table and a 67 second wait time.

Here is the main section of my temp-table population:

DEFINE VARIABLE ttxx AS HANDLE.
DEFINE VARIABLE hbuf AS HANDLE NO-UNDO. /* Default buffer object */

CREATE TEMP-TABLE ttxx.
ttxx:CREATE-LIKE ("{&File}").
ttxx:ADD-NEW-FIELD("UID","ROWID").
ttxx:TEMP-TABLE-PREPARE("ttxx").
hbuf = ttxx:DEFAULT-BUFFER-HANDLE.

FOR EACH {&File} WHERE {&Where} NO-LOCK:
  hbuf:BUFFER-CREATE ().
  hbuf:BUFFER-COPY(buffer {&File}:HANDLE).
  hbuf:BUFFER-FIELD("UID"):BUFFER-VALUE() = ROWID({&File}).
END.

Is there any way to make this faster, or is the slow performance unavoidable with such large tables?

(By the way, I'd like to thank everyone who's helped me out recently. ProgressTalk is a really valuable resource.)

 

[TheMadDBA]

0) How long does it take to just read the entire base table?

1) Do you really need to copy to a temp-table and not use the base table? (Appserver boundary maybe?) There is always going to be some amount of overhead copying the data to a temp-table.

2) Look at the -T, -Bt and -tmpbize parameters for the client sessions (and appservers if you are using them). Make sure the -T is set to a local drive (network shares are bad). Make sure -Bt * -tmpbsize is large enough to hold the entire temp-table in memory.

3) When you CREATE-LIKE you inherit all of the indexes from the base table, which adds overhead to creating the temp-tables. Make sure you need all of them and specify the ones you need.

 

[KMoody]

0) It takes about 30 seconds to read the table.

1) I'm not sure. It might be possible to query the base table itself. The trick is getting the existing code to cooperate.

2) Raising the -tmpbsize from 4 to 8 did help performance slightly.

3) I'll try that and see if it improves performance.

Thanks!

 

[TheMadDBA]

So your best possible time will be somewhere around 30 seconds. If you change to reading the table itself you can avoid pre-reading all of the records. Good Luck

 

[RealHeavyDude]

On a side note: You should include the buffer-release () in the loop because otherwise you might wind up missing the last record. Dynamic buffers don't have a similar scope than static ones. This even more true for temp-tables - they don't have a transaction scope too.


Heavy Regards, RealHeavyDude.

 

[KMoody]

So I guess I can't make it any faster than 30 seconds? Darn.

Well, I figured out how to query the table itself, and that seems to help. Unfortunately, the browse also needs to let the user sort by column and/or search for records beginning with a string.

This is how I'm trying to solve this problem:

DEFINE INPUT PARAMETER queryString AS CHAR.

DEFINE VARIABLE hPopupQuery  AS HANDLE  NO-UNDO.
DEFINE VARIABLE hPopupBuffer  AS HANDLE  NO-UNDO.
DEFINE VARIABLE hPopupBuffer2  AS HANDLE  NO-UNDO.

CREATE BUFFER hPopupBuffer FOR TABLE tableName BUFFER-NAME "b1Alias".
CREATE BUFFER hPopupBuffer2 FOR TABLE tableName BUFFER-NAME "b2Alias".

When the user clicks on a browser column:

DEF VAR s as CHAR.
/*The "where" statement in queryString actually varies based on the user's input */
DEF VAR queryString as char init "FOR EACH b1Alias USE-INDEX part-num WHERE b1Alias.PART-NUM BEGINS '4' NO-LOCK".
s = queryString + ",EACH b2Alias NO-LOCK where ROWID(b2Alias) = ROWID(b1Alias) AND STRING(b2Alias." + hSortColumn:NAME + ") BEGINS """ + searchString + """ BY b2Alias." + hSortColumn:NAME.
hPopupQuery:QUERY-PREPARE(s).

However, this query is pretty slow. Is there a better way to do a "join" on the same table?

 

[RealHeavyDude]

Is there any particular reason for the use-index in the query string? I am asking because it is bad practise to use it.

Why do you even join? You will have the same record in both buffers as you explicitely define an equality match on their rowids no matter what other criteria you have in the query. If the other criteria don't match you event won't get a result as there won't be any child record satisfying your query. This, to be honest, doesn't make sense to me whatsoever.

Heavy Regards, RealHeavyDude.

 

[KMoody]

The "use-index" phrase was in the old code. (Not a great reason, right? )

I should probably clarify: I want to do a join without needing to explicitly check row ids if that's possible - unless that would require a behind-the-scenes row id check anyway.