Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

How can I speed this up?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can I speed this up?

    How can I speed this up?
    Code:
    'Set up instance variables
    
    DIM i as n
    for i = 1 to 25
    	
    	'Open the table that you want to update...
    	dim tbl as p 
    	tbl = table.open("directory",FILE_RW_SHARED)
    	'Select a random record to check out
    	query.filter = "random_records(1,\"(Confirmedid = 1  )  .and. (Checkout = .f.  )\")"
    	query.order = ""
    	query.options = ""
    	
    	DIM itbl as p 
    	itbl = tbl.query_create()
    	DIM recs as n 
    	recs = itbl.records_get()   'get the number of records found by the query
    	if recs = 0 then 
    		ui_msg_box("Error","Record to update not found. Could not find record that matches filter expression: '" + query.filter+"'.",UI_STOP_SYMBOL)
    		tbl.close()
    		end 
    	else if recs > 1 then 
    		ui_msg_box("Error","More than one record was found that matches the filter expression. No records were updated.",UI_STOP_SYMBOL)
    		tbl.close()
    		end 
    	end if
    	
    	if tbl.is_record_locked() = .t. then 
    		ui_msg_box("Error","Cannot edit record because it is locked by another user, or in another session.",UI_STOP_SYMBOL)
    		tbl.close()
    		end 
    	end if 
    	
    	tbl.change_begin()
    	tbl.CHECKOUT = .T.
    	tbl.CHECKOUTDATE = date()
    	tbl.CHECKOUTBY = User_Name()
    	tbl.change_end(.t.)
    next
    	
    tbl.close()
    Thanks
    _______________________________
    Steven McLean
    i3 Home Inspections
    [email protected]

  • #2
    Re: How can I speed this up?

    Steven,

    The issue I see is that you are opening the table 25 times inside the for loop and closing the table only once outside the for loop. Need to move your open statement to before the for loop begins. Hope this helps.
    Jeff Ryder

    Comment


    • #3
      Re: How can I speed this up?

      I'm not sure why you're doing what you're doing. But,

      The main reason this takes so long is because it has to query the table 25 times.

      Instead, I'd maybe look for a way to query once, then find 25 random records within that query.

      Or, not query at all, and find 25 records using a randomizing routine.

      Edit:
      Just saw Jeff's response. How many records are in that table?
      -Steve
      sigpic

      Comment


      • #4
        Re: How can I speed this up?

        Hi Steve, what I an trying to do is select 25 random records and set a logical field (flag) to True

        Steve
        _______________________________
        Steven McLean
        i3 Home Inspections
        [email protected]

        Comment


        • #5
          Re: How can I speed this up?

          Steven,

          It did not even sink in that that the query was running 25 times also. Saw the first issue and stopped. I think Steve is on the right track for you. Set up your query using ramdom_records like in your example but do it once and request 25 records. Then loop through the resultant queried records and do your update.
          Jeff Ryder

          Comment


          • #6
            Re: How can I speed this up?

            I read your question..
            I read the first line of your script..
            And didn't read anything beyond that..

            Any time you use a For..Next loop, it will be SLOW.

            Regardless of what the rest of the script does, if you could use any alternative (and most of the time there is one) to For Next..by all means use it.

            Later..
            Quick read of the script:
            You opened the table 25 times, and closed it once!
            There is 25 instances of the table hanging out there.
            Open the table, once, before the For next loop.

            Again..
            You queried the table 25 times!
            I am in the middle of a lot of business transactions right now and I might be reading this way too quick, BUT couple important notes:
            If your intention is to get a 25 random records from the first table, you may not. Random is not random. It follows a specific "random" sequence. And if your intention is to have 25 random and unique records, you may not. You might stumble on the same record more than once.

            Secondly, If the second table has one record, it will be overwritten 25 times..You might as well just write it once with the last loop.

            I hope I am not confusing things with this quick response, but I think I got it right.
            Last edited by G Gabriel; 08-06-2009, 04:04 PM.

            Comment


            • #7
              Re: How can I speed this up?

              It won't make much difference when you are working with 1-25 records but you should be aware that wrapping change or enter code within batch_begin() batch_end() will also speed things up.

              Code:
              tbl.batch_begin()
                      tbl.change_begin()
              	tbl.CHECKOUT = .T.
              	tbl.CHECKOUTDATE = date()
              	tbl.CHECKOUTBY = User_Name()
              	tbl.change_end(.t.)
              tbl.batch_end()
              There can be only one.

              Comment


              • #8
                Re: How can I speed this up?

                You say what you want to do is:
                "... select 25 random records and set a logical field (flag) to True..", why not:
                open the table, select 25 random records then update them. If concerned about some records being locked, select 30 or whatever records and count the successful updates and stop when 25 are updated.

                Below is code written but not debugged or checked for syntax so I hope it at least conveys what I would do:

                'Open the table that you want to update...
                dim tbl as p
                tbl = table.open("directory",FILE_RW_SHARED)
                'Select a random record to check out
                query.filter = "random_records(25,\"(Confirmedid = 1 ) .and. (Checkout = .f. )\")"
                query.order = ""
                query.options = ""

                DIM itbl as p
                itbl = tbl.query_create()
                DIM recs as n
                recs = itbl.records_get() 'get the number of records found by the query
                if recs < 25 then
                ui_msg_box("Error","Insufficient records to reset, no changes made",UI_STOP_SYMBOL)
                tbl.close()
                end
                end if

                tbl.fetch_first()
                tbl.batch_begin()
                while .not. tbl.fetch_eof()
                if tbl.is_record_locked() = .t. then
                ui_msg_box("Error","Cannot edit record because it is locked by another user, or in another session.",UI_STOP_SYMBOL)
                else
                tbl.change_begin()
                tbl.CHECKOUT = .T.
                tbl.CHECKOUTDATE = date()
                tbl.CHECKOUTBY = User_Name()
                tbl.change_end(.t.)
                end if
                tbl.fetch_next()
                end while
                tbl.batch_end()
                tbl.close()

                Comment


                • #9
                  Re: How can I speed this up?

                  Gary, Thanks this is a huge improvement.

                  G. Gabriel, you expressed concern as to the potential for the same record to be selected more than once. The "checked out" records are further processed and marked "confirmed" The random selection excludes the "confirmed" records. Theoretically this should ensure the random integrity of the selection.

                  Stan, the batch begin / end made a substantial difference on 100/200 records. I could not find this in the help file. Is there any documentation on this?

                  Thanks for the help!
                  _______________________________
                  Steven McLean
                  i3 Home Inspections
                  [email protected]

                  Comment


                  • #10
                    Re: How can I speed this up?

                    <tbl>.batch_begin()
                    There can be only one.

                    Comment


                    • #11
                      Re: How can I speed this up?

                      Stan, if I search the help I can find it, but not the index? I wonder what else is not in the index.

                      Is there any good "Language Reference" text available.
                      _______________________________
                      Steven McLean
                      i3 Home Inspections
                      [email protected]

                      Comment


                      • #12
                        Re: How can I speed this up?

                        You can find it in the index with <tbl>.batch_begin() or plain .batch_begin(), <tbl> being the commonly used designation for a table pointer variable.

                        Check the Contents section for the language references.
                        There can be only one.

                        Comment


                        • #13
                          Re: How can I speed this up?

                          Steven (and others I imagine),

                          When I am searching for functions or methods I almost always use the help file index and usually can find whatever in a matter of seconds or at most minutes...I do not always find ALL the various functions that can be used but usually enough to do what I need to.

                          When searching for a function try this way....

                          first with the name (even if you do not know if such a function exists--try various names then).

                          Then do the same but have a period in front of it....like Stan mentioned.
                          Then try an asterisk in front
                          Then try a5.name
                          Then try a5_name

                          Then go to the various listings of functions for the field types...such as type in character and then scroll down to functions...and start looking through them....actually goes quite fast as these listings are very organized.

                          There are others also but most times if the above do not find what you are looking for using the index after a few minutes, move onto the Contents or here on the messageboard.

                          The messageboard has a different method for searching but is just a variation of the index search really.

                          FWIW really---the above is what I have developed, in part, to find whatever more easily...should be of use----but then have said it many times and still users do not seem to pick up on it. I swear, if I could teach users how to search the help/messageboard and a few other basics, I think the learning curve for them would be drastically reduced.
                          Mike
                          __________________________________________
                          It is only when we forget all our learning that we begin to know.
                          It's not what you look at that matters, it's what you see.
                          Henry David Thoreau
                          __________________________________________



                          Comment

                          Working...
                          X