Alpha Video Training
Results 1 to 9 of 9

Thread: Fun with queries

  1. #1
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Fun with queries

    Is this inefficient? I am specifically wondering about repeatedly creating and dropping a query in a while loop.

    The idea here is that mytable provides family IDs. myRelatedTable is searched for a combination of a family ID (dynamic) and an event id (static). If the ID combination is found in the related table, some action takes place, such as copying data from mytable to myrelatedtable.

    I was wondering if there is an optimized way of doing this, such as creating a query that holds all familyID's that belong to one event, and then using that one query to look up family IDs. I don't know how much overhead creating and dropping queries like this cause, and being somewhat new to the A5 way of doing things, I may be writing pretty inefficient code.

    Code:
    veventid = 1
    mytable.fetch_first()
    while .not. mytable.fetch_eof()
       vFamilyID = mytable.familyid
       myquery=myRelatedTable.query_create("","EventID = "+vEventID .AND. "FamilyID = "+vFamilyID)
       vFound = (myquery.records_get()>0)
       myquery.drop()
       IF vFound
          'do something
       END IF
       mytable.fetch_next()
    end while

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

    Default Re: Fun with queries

    Code:
    veventid = 1
    mytable.fetch_first()
    while .not. mytable.fetch_eof()
       vlink = mytable.familyid+mytable.familyid
       IF mytable.familyid = veventid
          'do something with the link you have captured above on that record
       END IF
       mytable.fetch_next()
    end while
    Robin

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

  3. #3
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Re: Fun with queries

    Thank you for the suggestion. My explanation may have been a bit obscure -- what I need to do is this:

    Scan through mytable
    for each mytable row, find a row in RelatedTable that has a foreign key match to mytable primary key as well as another column match to a variable (this is the relatedtable query part)
    if a match is found in RelatedTable
    copy data from mytable to RelatedTable
    end if
    end for
    end scan

    It's the query part that I'm not sure I'm doing the most optimized way. I'm used to using self optimizing SQL queries for this kind of stuff, and it is hard to know how Xbasic queries are optimized, if at all.

    Luckily the amount of data I'm dealing with is not huge, so even unoptimized queries work fast enough. This is more of an academic interest at this point, which may well have practical implications in the future if I need to deal with hundreds of thousands of records in this manner. Shudder the thought...

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

    Default Re: Fun with queries

    I still don't think you need a query. You can fetch thru both tables: begin with the primary and then fetch the related table until a match if any is foundl; then fetch the next primary table and repeat the search in the related table. When (if) a match is found then run the copy you want while both tables are on the related matching records. I will attempt an example...

    Edit: I thought I had an example script but it was for entering records not changing matching records. Have you tried a Post operation for this task?
    Last edited by MoGrace; 03-09-2012 at 11:39 PM.
    Robin

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

  5. #5
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Re: Fun with queries

    Hmmm... Interesting idea. I wonder if scanning through the related table for each record in the primary table is faster than building a query each time. It seems a bit brute force to me, but then again I don't know how A5 is optimized internally, and what are the efficiencies/inefficiencies of table query. Maybe there is a fair amount of overhead in building and dropping the query. Would be nice to know a bit more about the internals. I was excited about the debugger profiler feature which would tell me a lot about the execution speeds of different statements, until I realized that the profiler only works for desktop applications. I suppose I could build a dummy desktop app just to attach the profiler to these types of operations, assuming that the internal engine is the same for desktop and web server operations. Seems like a lot of extra work, though...

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

    Default Re: Fun with queries

    Did you look into the idea of using a post operation to achieve your goal?
    Robin

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

  7. #7
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Re: Fun with queries

    Post operation?

  8. #8
    Member
    Real Name
    Robert Earl Allin
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    243

    Default Re: Fun with queries

    Just wondering.... Is this a MySQL database?

    If you are updating the Myrelatedtable with info from mytable, couldn't you do it with an update statement?

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

    Default Re: Fun with queries

    Quote Originally Posted by nlights View Post
    Post operation?
    Yes, if you are looking to match a family id with an event id then that is the filter you need to define in the post definition to find the records you want to update.
    Robin

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

Similar Threads

  1. Fun with Pictures and logos ?
    By Newton Thomas in forum Alpha Five Version 5
    Replies: 5
    Last Post: 11-23-2004, 03:24 PM
  2. Thumbnail fun
    By Blake in forum Alpha Five Version 5
    Replies: 10
    Last Post: 04-17-2004, 04:46 PM
  3. Just for fun - Code Size
    By CALocklin in forum Code Archive
    Replies: 2
    Last Post: 02-15-2004, 05:39 AM
  4. fun with arrays
    By Cheryl Lemire in forum Alpha Five Version 5
    Replies: 23
    Last Post: 10-14-2003, 08:29 AM
  5. It's time for some fun.
    By Stephen Williams in forum Alpha Five Version 4
    Replies: 43
    Last Post: 04-13-2001, 10:01 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
  •