speed up buffer to browse?

J

jmac12

Member
I've got the following code (open edge 10.2b):

Code: 
chrQuery = "FOR EACH " + chrMyTable.
                                       
do with frame {&frame-name}:

    create query hQuery.
    hQuery:set-buffers(hTempTable).
    hQuery:query-prepare(chrQuery).
    hQuery:query-open().
    
    hQuery:get-first().
    brDetail:query = hQuery.
    
    brDetail:add-columns-from(hTempTable).

end.

trouble is this seems to take ages compared just doing a temp table and a for each is there a faster way of doing it? the reason im doing this im trying to create generic code. so you dont have to hard code in what u are doing for each tableX. any ideas would be great I was doing in the following post: http://www.progresstalk.com/showthread.php?117503-Dynamic-temp-table-to-a-static-temptable but that seems to have gone dead
 
seems to me that you should change your query, to include WHERE field=<expression>
if you don't specify filter, and the table is big, it will take long time to accumulate the data
 
Try
Code: 
chrQuery = "FOR EACH " + chrMyTable + " NO-LOCK".
                                       
do with frame {&frame-name}:

    create query hQuery.
    hQuery:set-buffers(hTempTable).
    hQuery:FORWARD-ONLY = true.
    hQuery:Cache = 1.
    hQuery:query-prepare(chrQuery).
    hQuery:query-open().
    
    hQuery:get-first().
    brDetail:query = hQuery.
    
    brDetail:add-columns-from(hTempTable).

end.
 
hi maximMonin,

im getting the following error when im trying that code:
browse requires the Query to be DEFINEd SCROLLING (3330).

hi mosfin

yes i could add where stuff.. but not doing that quite yet my main question is y is it slower than just doing a normal for each grades no-lock then display i browse. i presuming its the building of the columns that is doing it. was wondering if theres a better way.
 
Maybe I'm wrong, but from the code excerpt it seems that you invoke the method ADD-COLUMNS-FROM for each iteration of the query. If that's the case then it might very well explain a performance issue as this operation is slow ...

Otherwise I would have a look on the INDEX-INFORMATION attribute of the query object. It will tell you whether you hit the index you expect it to hit.


Heavy Regards, RealHeavyDude.
 
Just an addition:

You don't need to open the query to add columns from the buffers associated with it to a browse widget. As soon as the query object has its buffers set you can add the columns to the browse widget.


Heavy Regards, RealHeavyDude.
 
remove hquery:cache = 1.

And it takes ordinary 1-2 ms to get first table record for db server even if table consists 1 mln+ records.
make sure that
for each Tablename:
display tablename.
leave.
end.
works fast enough.
or change dynamic query to
chrQuery = "FOR FIRST " + chrMyTable + " NO-LOCK".
But I think it do not change response time.
 
Cheers RHD,

Moving when I added the columns did the trick. I add them before I do any query on the data. So i have browse with no data but columns then just run the query and bobs ur
Uncle for some reason i thought you had to add the columns each time to get the data to be displayed but I don’t :D. So now works at the same speed. Strange how you can come back to something and realise that code was totally rubbish :P
code so far for other ppl looking is as follows:

Code: 
    /*brDetail = browse*/
    define variable hTempTable          as handle no-undo.
    define variable hQuery           AS HANDLE no-undo.
    define variable chrMyTable as character initial "grades" no-undo.
 
    create buffer hTempTable for table chrMyTable.

    CREATE QUERY hQuery.
    hQuery:SET-BUFFERS(hTempTable).
    brDetail:query = hQuery.
    brDetail:ADD-COLUMNS-FROM(chrMyTable). 
   /*above code needs to only be done once so you can set the columns*/
 
    /*[FONT=Calibri][SIZE=3]Dynamic query */[/SIZE][/FONT]
    chrQuery = "FOR EACH " + chrMyTable + " NO-LOCK".
    hQuery:query-prepare(chrQuery).
    hQuery:query-open().
    hQuery:get-first().
 
You must log in or register to reply here.
Back
Top