Alpha Video Training
Results 1 to 26 of 26

Thread: Question RE: LQO in Script

  1. #1
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Question RE: LQO in Script

    Currently my employees run the script below to find cancellations from our clients. In the Query Filter, the first 2 conditions are using LQO. The last condition does not. All fields referenced in the Filter are also indexed fields. I believe the last part of the Query goes thru all of the records because of the "OR" conditions. Unfortunately all of those "OR's" are necessary to run the Query correctly. What I am asking is there any other way I might be able to reference all of those conditions and have A5 use LQO for the entire Query Filter? If not, we will live with it but since there are approximately 82,000 records and growing daily, it would be great if we could cut the time it takes to run the query. It is especially slow over the network of course. I have commented out the Export portion while I have been working on this. Thanks for looking....

    dim tbl as P

    tstr1 = ui_get_text("Client Name","Please Enter Client")
    if tstr1=""then
    end
    end if
    user1=(tstr1)

    dstr = ui_get_date("Date Received","Please Enter Date In")
    if dstr=""then
    end
    end if
    user2=ctod(dstr)

    tbl=table.open("ameregl3")


    query.description = ""
    query.order = ""
    query.filter = "AMEREGL3->ATTORNEY="+quote(user1)+".AND.AMEREGL3->DATE_COMP={"+dtoc(user2)+"}.AND.(AMEREGL3->CODE=\"adne\".OR.ameregl3->code=\"cd\".OR.ameregl3->code=\"can\".OR.ameregl3->code=\"c2\".OR.ameregl3->code=\"cnc\".OR.ameregl3->code=\"pb2\".OR.ameregl3->code=\"cnp\".OR.ameregl3->code=\"cnc2\".OR.ameregl3->code=\"cc2\".OR.ameregl3->code=\"ia\".OR.ameregl3->code=\"isp\".OR.ameregl3->code=\"isn\".OR.ameregl3->code=\"ita\".OR.ameregl3->code=\"nan\".OR.ameregl3->code=\"npa\".OR.ameregl3->code=\"nun\".OR.ameregl3->code=\"nsa\".OR.ameregl3->code=\"nss\".OR.ameregl3->code=\"non\".OR.ameregl3->code=\"ncz\".OR.ameregl3->code=\"pba\".OR.ameregl3->code=\"pv\".OR.ameregl3->code=\"sdne\".OR.ameregl3->code=\"ssw\".OR.ameregl3->code=\"cmra\")"
    query.options = "I"
    tbl.query_create()
    'export.type = 4
    'export.names = .F.
    'export.file = "\\Docserver\inetpub\EXCEL PROGRAMS\CANCELLATIONS\Cancellations.xls"
    'export.options = ""
    'export.field_sep = ""
    'export.record_sep = ""
    'export.fields = 9
    'export.field1 = "ameregl3->attorney"
    'export.field2 = "ameregl3->Person"
    'export.field3 = "ameregl3->ref___"
    'export.field4 = "ameregl3->date_in"
    'export.field5 = "ameregl3->address"
    'export.field6 = "ameregl3->poe_address"
    'export.field7 = "ameregl3->code"
    'export.field8 = "ameregl3->job"
    'export.field9 = "ameregl3->comments"
    'tbl.export()

    tbl = table.close()

    END

  2. #2
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Question RE: LQO in Script

    You can try
    Code:
    query.filter = "ATTORNEY="+quote(user1)+" .AND. DATE_COMP={"+dtoc(user2)+"}.AND.(at(ut(CODE),\"ADNE-CD-CAN-C2-CNC-PB2-CNP-CNC2-CC2-IA-ISP-ISN-ITA-NAN-NPA-NUN-NSA-NSS-NON-NCZ-PBA-PV-SDNE-SSW-CMRA\")>0)"
    This won't use LQO either i think but may speed up the query by not doing multiple compares for the same field.

    Also in this case, the use of the table name within the filter string is not necessary as the filter is applied to the tbl pointer where you have already specified the table. It is not detrimental to include the name but is less typing and a bit easier to read.

    Edit: after seeing Stan's post I changed the upper() function to the ut() function to trim the field value.
    Last edited by Tim Kiebert; 01-12-2015 at 08:34 PM.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    The entire test of ameregl3->code = can be replaced.

    Build a list like (shortened sample version).

    codes_list = "adne,cd,can,c2,cnc" 'comma list of all codes
    codes_list = strtran(codes_list,",",crlf()) 'convert comma list to crlf list

    Then the filter is
    query.filter = "AMEREGL3->ATTORNEY="+quote(user1)+".AND.AMEREGL3->DATE_COMP={"+dtoc(user2)+"} .AND. is_one_of(alltrim(ameregl3->code),"+quote(codes_list)+")"

    If that doesn't speed it up we can try other methods.
    There can be only one.

  4. #4
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,661

    Default Re: Question RE: LQO in Script

    Hi Stan,
    Did ver 5 use crlf() lists?
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    I don't think it's a question of a particular version using lists, rather whether a specific function is supported in a specific version

    Is_one_of(), per the docs, is available in v5.
    Comma_to_crlf() says v6 or later so I suggested strtran() instead.

    All three "use lists".
    There can be only one.

  6. #6
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Gentlemen, Thanks for the replies. I've finally gotten into work and will try these. Will update you on progress. I've never had the occasion to need a "list" before, until, now. I will certainly read up on them now. I wasn't aware that you could actually call out a list. Thanks again.

    Scott

  7. #7
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    *****UPDATE*****
    Ok, so I tried both methods and still goes through all 82,000 records on the last part of the filter, although the script is much neater now.

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    You do have an index on the code field? I'm not clear how is_one_of() works with LQO since functions aren't mentioned in the docs other than smatch() and between().

    The .OR. shouldn't cause problem since it is an allowed operator for LQO.

    Collections are faster than using is_one_of(). Docs say v5 supports collections.

    Try this attack

    Code:
    codes_list= "adne;adne,cd;cd,can;can,c2;c2,cnc;cnc,pb2;pb2,cnp;cnp,cnc2;cnc2,cc2;cc2,ia;ia,sp;sp,isn;isn,ita;ita,nan;nan,npa;npa,nun;nun,nsa;nsa,nss;nss,non;non,ncz;ncz,pba;pbh,pv;pv,sdne;sdne,ssw;ssw,cmra;cmra"
    codes_list  = strtran(codes_list,",",crlf())
    
    dim global cds as U 'must be global so the query.filter expression will "see" it, in my experience
    cds.initialize("1;2","1","2",codes_list)
    tbl=table.open("ameregl3")
    query.description = ""
    query.order = ""
    query.filter = "ATTORNEY="+quote(user1)+".AND.DATE_COMP={"+dtoc(user2)+"} .AND. cds.exist(alltrim(CODE))"
    
    'rest of script
    Another thought. You have a pretty long list of codes you are including in the filter. Is the list of codes to be excluded shorter? Might be able to filter exclude a shorter list.
    Last edited by Stan Mathews; 01-13-2015 at 03:52 PM.
    There can be only one.

  9. #9
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Thanks for the suggestion Stan. Yes, "Code" is one of the indexes as are "Attorney" and "Date_comp". I'll give this a try and see what happens. I also noticed that ".OR." is included in LQO. I assumed the length of time is due to so many ".OR." conditions and the time involved is due to comparing all of them for each record. I was hoping that the "comparison" would only be for the records called out by the "Attorney" field and the "Date_comp" field and not all records.

  10. #10
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Hi Stan. Tried it and I still get the same thing. It's still going through all records. It could be "it is what it is". I thought maybe I could break up the query into 2 Queries, one for the "Attorney" and "Date_comp" and then query those records for the "Code" conditions, but I'm pretty sure it will be the same result and a lot more typing and needless code. Could be I may have to live with it the way it is.

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    I assumed the length of time is due to so many ".OR." conditions and the time involved is due to comparing all of them for each record.
    Agreed. The is_one_of() filter expression variation should have been faster then the multiple .or. filter, just won't use LQO. The collection version should be faster still.
    There can be only one.

  12. #12
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Thanks Stan for your time and trouble in helping me with this. The good thing I can take from this is I learned about Collections and some new functions. Always good to be able to add to your own knowledge base for the future. Unfortunately, being 60 years old I'll have to hope I can recall it in the future...lol.

  13. #13
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    Scratch this idea. We'd have to filter the index for only the codes used in the filter. Otherwise all records would fit the query.filter expression. Not a fan of filtered indexes. Brain freeze led to that.

    Since we're this deep, try creating an index with the expression

    attorney+cdate(date_comp)+code

    index tag name "composite"

    then

    query.filter = "key_exist(\"composite\",attorney+cdate(date_comp)+code)"

    Again, just looking for speed. LQO is not going to be possible with your list of codes to check.
    Last edited by Stan Mathews; 01-13-2015 at 04:33 PM.
    There can be only one.

  14. #14
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Ok. I'll give it a try. I never thought of creating a different type of index specific to this.

  15. #15
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    When I tried to create the index, after inserting the expression, it gives an error message "Expression does not evaluate to a logical value", and won't let me save it.

  16. #16
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Just read your next thread....... Oh well, I guess it is what it is. Thanks for all your help Stan. I don't use filtered indexes either. I remember reading in past threads that this caused some headaches. Thanks again though.

  17. #17
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    The index order expression doesn't need to evaluate to a logical value. Are you sure you didn't try to put that in the filter expression> If you want to try it you need to create a filter expression for this index. Probably easiest to use Tim's excellent suggestion

    index Name - composite
    index order expression - attorney+cdate(date_comp)+code
    index filter expression - at(ut(CODE),"ADNE-CD-CAN-C2-CNC-PB2-CNP-CNC2-CC2-IA-ISP-ISN-ITA-NAN-NPA-NUN-NSA-NSS-NON-NCZ-PBA-PV-SDNE-SSW-CMRA")>0)

    What was your experience with the speed variations between the suggested alternative filters?
    There can be only one.

  18. #18
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Btw, an "Exclude" list of codes would be much longer unfortunately............

  19. #19
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Yes....DUH!!!!! That's exactly what I did....... Best 2 out of 3....lol.

  20. #20
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    The index filter expression says it is invalid or incomplete the way it is......

  21. #21
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    I saw little to no difference in time with any of the filters so far. I'm working on my machine with a copied version of the db. I wonder of this is one of those instances where it would work across the network but not on a standalone.....hmmmm..... who knows......

  22. #22
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    Nope, should be much faster locally in all aspects of operation.
    There can be only one.

  23. #23
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Forgot the first right hand paranthesis, error message gone and results in a logical.

  24. #24
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Well....... still no "soap". Still goes through all records. I don't think I'll use that one only because I'm not a fan of filtered indexes. I think I'll leave it at your previous suggestion using a collection and leave it at that. That is unless you can pull more rabbits out of your hat Stan.....lol.

  25. #25
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Question RE: LQO in Script

    Well Stan, here's one for the books. I had one of my employees try it again, with the old script on the actual db, across the network, and its working fine. Taking only a few seconds. Now bear in mind, nothing has been changed on the original script as I have been working in a copied version to my stand alone machine. So....... I told them just use it and let me know if anything changes. This makes no sense to me as I tried it yesterday on the actual db and it took forever. I'm not going to question it, I'm just going to call it good and chalk it up to an A5 anomaly. Thanks again for all your help the last 2 days as it was invaluable to me.

    Scott

  26. #26
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Question RE: LQO in Script

    Would lead me to believe the index was corrupt or it was out of date?

    Also would be interesting to hear about the performance using the alternate filer expressions on the working database.
    There can be only one.

Similar Threads

  1. LQO
    By gaby_h in forum Alpha Five Version 9 - Desktop Applications
    Replies: 4
    Last Post: 10-28-2009, 12:52 PM
  2. LQO Limits
    By spain246 in forum Alpha Five Version 7
    Replies: 8
    Last Post: 03-28-2006, 12:30 PM
  3. LQO speed - Is this compatable?
    By Mattyau in forum Alpha Five Version 5
    Replies: 8
    Last Post: 11-15-2004, 04:53 AM
  4. LQO Not Working?
    By russ Boehle in forum Alpha Five Version 5
    Replies: 5
    Last Post: 02-08-2003, 04:36 PM
  5. LQO
    By Aamer Khan in forum Alpha Five Version 4
    Replies: 3
    Last Post: 04-13-2000, 05:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •