Resolved Slow browse display on open query statement

[matchew]

Good Day,
I am having a trouble with displaying the results of my open query statement to the browse.
Legend
Program Name: memberName.w
Open Query: OPEN QUERY {&browse-name} FOR EACH MemberInf
WHERE MemberInf.lNAME + " " + MemInf.FName BEGINS SELF:SCREEN-VALUE
AND memInfo.memStatus NE MemAppCode.

Problem: First Search of name in the program memberName.w the display of the open query Result to the browse is very slow it makes the window not responding it takes like 10-15 secs to wait.
Second Search everything is OK.

I tried to use index but it makes it even slower I don't know what to do can anyone provide some help?
and I think there is nothing wrong with my search query for the Fill-In.

 

[oli]

WHERE MemberInf.lNAME + " " + MemInf.FName BEGINS SELF:SCREEN-VALUE

This is not a good idea.
I believe that the compiler won't find any appropriate index to use (and boost) your query. But just for curiosity, what is the definition of the index you thought would help?

You should either code 2 distinct conditions (one one "lName" and the other on "fName") or consider a view or (in last instance) denormalization of your table to have a field that concatenates "lName" and "fName".

 

[matchew]

The Index that I used was the "name"
contains the ff.
I just want to search for their full names
fname
lname
memstatus

ow yes I use that kind of method instead of creating new field for full name for the reason that if a user want to type first the firstname or the middlename that will be a 3 additional field to the DB

 

[Cringer]

How many records are there? You may be better caching the records to a temp-table when the screen loads (with a field that contains "MemberInf.lNAME +" "+ MemInf.FName" and then use the temp table for your query.

 

[matchew]

How many records are there? You may be better caching the records to a temp-table when the screen loads (with a field that contains "MemberInf.lNAME +" "+ MemInf.FName" and then use the temp table for your query.

for this table I think its over 30,000 records
I tried caching them in main block but I think my users won't like it because it take like 15-30 secs to cached them bu I think I will Try your suggestion but any other suggestion?

 

[Cringer]

Other than actually having the concatenated field stored on the table, no. What you are doing will never be efficient because Progress will not be able to select a decent index, and in fact I think it will result in a table scan for each time the query is opened.

 

[matchew]

Ok thank cringer I will take note of your advices

 

[Osborne]

Another problem is possibly this line:

memInfo.memStatus NE MemAppCode

Many years ago I read a statement that said the not equal criteria is not efficient at all because the only way it can be checked is to read every record in the table first. With modern Progress I don't know if this still applies, but if it does then this would add to the slowness. If it is easy to implement, you could test this by creating temp tables for the codes required and have your query do this instead:

 OPEN QUERY {&browse-name} FOR EACH ttMemAppCodes,
    EACH MemberInf WHERE MemberInf.lNAME + " " + MemInf.FName BEGINS SELF:SCREEN-VALUE
    AND memInfo.memStatus = ttMemAppCodes.MemAppCode.

 

[matchew]

I am using that statement for the static query of my browse but I think I will try this statement instead

 OPEN QUERY {&browse-name} FOR EACH ttMemAppCodes,
    EACH MemberInf WHERE MemberInf.lNAME + " " + MemInf.FName BEGINS SELF:SCREEN-VALUE
    AND NOT memInfo.memStatus = ttMemAppCodes.MemAppCode.

 

[matchew]

that is my problem when using a index so it makes it even slower
I tried to use an index again but progress still doing a whole-index scan

 

[Rob Fitzpatrick]

I'm not following your query. Should "MemInf.FName" and "memInfo.memStatus" be "MemberInf.FName" and "MemberInf.memStatus"? Or are there other buffers involved here that are not shown in your code?

I believe the compiler has no option here but to do a whole-index scan as your WHERE clause contains an expression on field values of the buffer you are querying.

 

[Cringer]

Using a static query or Open Query will make no difference at all to your performance. The problem is your query. The concatenation in the left hand side of the query forces a full table scan. There's nothing you can do about it, unless you store the concatenated value in the db or cache the values to temp-table beforehand. You will also want to change the last line to

AND memInfo.memStatus NE ttMemAppCodes.MemAppCode.

 

[LarryD]

Another possible option would be to add an index for MemInf.FName and an index for MemInf.lNAME if you don't have them already, then do something like the following:

def var lv-fname as character no-undo.
def var lv-lname as character no-undo.
assign
    lv-fname = entry(1,SELF:SCREEN-VALUE," ")
   lv-lname = entry(2,SELF:SCREEN-VALUE," ").
OPEN QUERY {&browse-name} FOR EACH ttMemAppCodes,
                    EACH MemberInf WHERE MemberInf.lNAME = lv-lname
                               AND MemInf.FName = lv-fname
                               AND memInfo.memStatus NE ttMemAppCodes.MemAppCode.

 

[matchew]

Thanks Guys I already Solved my problem about this query few days ago and I forgot to update my thread thanks a Lot to you guys
and I have a similar solution as LarryD posted

Thank You Very Much ^_^