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

Export takes way too long

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

    Export takes way too long

    Try as I may in v5 and v6, when I create a saved export operation and run it from a button that filters for the current record only, the darn thing insists on seemingly building an index and then running through every single record in the table or set as if it was a mystery where the current record was. For a small table this is not a big deal. But when the table has half a million records in it, it is downright unacceptable (takes way too long).

    3 questions:

    1. Other than the way outlined below, does anyone know a way around this?

    2. Can anyone tell me why it is desirable to run through every record in the table?

    3. Is there some problem that I do not see in using the following code to get on with the operation?

    Thanks,

    Ray

    *************************
    dim global recnumber as n
    recnumber=5 'will come from form in production

    table.reset("main.set")
    a_tbl = table.current()
    query.order = ""
    query.filter = "recno()="+str(recnumber)
    query.options = "I"
    a_tbl.query_create()
    export.type = 0
    export.names = .T.
    export.file = Export_filename
    export.options = "QLT"
    export.field_sep = ","
    export.record_sep = ""CR""LF""
    export.fields = 3
    export.field1 = "mailmain-"last"
    export.field2 = "mailmain-"first"
    export.field3 = "mini_app-"cob_email"

    a_tbl.export()


    #2
    RE: Export takes way too long

    Ray,

    Your script seems to be exporting from a set, instead of the table you mention.

    Your script is using an obsolete query option.

    I'm not sure whether either of these is significant, but they struck me as odd.

    The key here is to define an index that uniquely identifies each record you need to export, and then define the query filter so that lightning query optimization can occur.

    It's also possible to build a custom export script using xbasic. Getting the output formatted correctly is tedious but it seems to me that such a script could grab the contents of the current record, slam them into a series of variables, format a concatenated string in the desired way, and then write it to a text file pretty quickly.

    -- tom

    Comment


      #3
      RE: Export takes way too long

      I'm new here, but my thought would be if you want to export the current record, have the button mark the record, export amrked records, then unmark that record. Isn't there a built-in index on marked/unmarked records? If not, I guess this doesn't help.

      Bill Belanger

      Comment


        #4
        RE: Export takes way too long

        Ray,

        recno() is not a real index, so I don't think it will use LQO. Instead, either create a recno() index, or use a unique key to an existing index, and then create a query filter that looks like this

        "between(index_expression,"+keyvalue+","+keyvalue+")"

        The index expression should exactly match an index (and to help Alpha a bit further, I make sure the case and everything else, e.g. quotes, are identical). You could set a range, but Alpha considers that an antiquated method.

        Regards,

        Ira
        Regards,

        Ira J. Perlow
        Computer Systems Design


        CSDA A5 Products
        New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
        CSDA Barcode Functions

        CSDA Code Utility
        CSDA Screen Capture


        Comment


          #5
          RE: Export takes way too long

          Okay, what I was trying to do but have failed is to instantly export a single record using a saved export so that my client could modify the export at will without having to deal with an xBasic version of the export with a filter that does give the instant result (without waiting to run through half a million records). As I said, I still cannot find not way to do this. Ira, any version using between() that I have tried does not work with a saved export.

          In version 4 I am pretty sure that the following code on a form button did the job:

          tbl=table.current()
          current_record = alltrim(str(recno(),10,0))
          :Export.run("MT Export","BETWEEN(RECNO(),"+ current_record+ "," + current_record+")")

          But if it did, it no longer does.

          Modifying an xBasic version of the saved export with the following filter:

          "BETWEEN(ID,"+ v_ID_value+ "," + v_ID_value+")"

          works, where v_ID_value is a global variable from a form (and the value is from an indexed numerical field). So does the filter:

          "recno()="+str(recnumber)

          works where recnumber is a global variable from a form.

          My original question was: Is there any good reason not to use "recno()="+str(recnumber) and I am still not sure it matters since they both of the above work. I understand that record numbers could change if someone deleted and packed the table involved, but that�s the only very remote reason I can see to prefer the other option.

          In any case, a better question may be: Is there a way to get an instant export using the a saved export operation, and if not, why not?

          Ray Lyons

          Comment


            #6
            RE: Export takes way too long

            Lord have mercy, how I wish someone from Alpha had saved me the many hours I have spent on this!

            There is a way and it is pretty darned simple. By using the "Run Genie" to run the saved export, putting in a recno() filter and checking the High Level Xbasic for record 5 I get:

            query.filter = "recno()=5"
            query.order = "recno()"

            export.run("Point Export",query.filter,query.order)

            Given that Alpha approves of that, I can put the following on a form button and it works (i.e., runs instantly)!

            dim recnumber as n
            recnumber = recno()
            query.filter = "recno()="+recnumber
            query.order = "recno()"
            export.run("Point Export",query.filter,query.order)

            Interestingly, there is not even a need to change recnumber into a string, which I do not quite understand, but hey, what works, works.

            Ray

            Comment


              #7
              RE: Export takes way too long

              Spoke too soon.

              Only works on a table based export, not on an export based on a one-to-one set.

              Doesn't there have to be a way when a such set is used? I still think it used to work, but now I am not nearly so sure.

              Ray

              Comment


                #8
                RE: Export takes way too long

                Hello Ray,

                ""Okay, what I was trying to do but have failed is to instantly export a single record using a saved export so that my client could modify the export at will without having to deal with an xBasic version of the export ""

                If this really turns out to be an issue (speed of query), then you can 'cheat' :-) Create a dummy table/set and do a quick copy of the current record then do the export from this dummy table which will only contain one record. Now your client can modify the export operation.

                Good luck,
                Jim

                Comment


                  #9
                  RE: Export takes way too long

                  Thanks Jim, I thought about this method but so far have gone with seeing if they can handle copy the RAW Xbasic code for their saved export and pasting it into a ready made script that has instructions to replace the ".T." filter with a between() one that gets the job done when run in a script.

                  What I do not understand is why the between() or even "recno()="+var_recnumber filter can't be effective (get an instant export) when run in a saved operation. The same exact code does when put into a script. Go figure, and if you figure it out, please let me know!

                  Ray

                  Comment


                    #10
                    RE: Export takes way too long

                    In case anyone is interested, in lieu of Alpha fixing how exporting the current record only runs, Selwyn has given me the final step in my work-around. It now all works, though it still does not address the problem of unsophisticated users just being able to use a saved export run by an action script filtering for the current record only (which does work but which runs through every single record in the primary table).

                    Anyway, let me make it clear that I am exporting from a set. There are 2 possibilities: The set has only one-to-many relationships or it has at least 1 one-to-many relationship. Which you are dealing with matters in regard to whether Selwyn�s contribution to my work-around is necessary.

                    My work-around involves creating a saved export, showing the Xbasic code and copying the raw code into a new script (or into the clipboard if you want to paste it into an existing script). The code would look something like this:

                    a_tbl = table.current()
                    query.order = ""
                    query.filter = ".T."
                    query.options = "I"
                    a_tbl.query_create()
                    export.type = 0
                    export.names = .T.
                    export.file = "c:\test.txt"
                    export.options = "QLT"
                    export.field_sep = ","
                    export.record_sep = ""CR""LF""
                    export.fields = 3
                    export.field1 = "mailmain-"last_name"
                    export.field2 = "mailmain-"first_name"
                    export.field3 = "mini_app-"cob_email"
                    a_tbl.export()

                    Depending on how a button that is going to run this script gets the current record [using a Between() function is one option] the query.filter line needs to be modified. Let�s assume our button running the script has the following method to get the current record:

                    dim global record_number as n
                    record_number = current_record_number()

                    Then our query filter line from the raw code (as in the above) would have to be changed to:

                    query.filter = "recno() = " + record_number

                    This would get an instant result on a primary table with a ton of records (just as we might expect without going through all this!) and is all that needs to be done if there are no one-to-many relationships in the set. In the end, that was my problem, as my set had a one-to-many relationship and even though I was not exporting any of those child records, any primary record that had multiple records in the one-to-many child would result in there being more than one line of identical data in the exported text file. That would be a problem because the people who were getting this file could not, except for the field names, deal with more than one line of data.

                    My initial way of dealing with this was to create a set with just the same one-to-one tables and relationships and run my export from it. But Selwyn made it easier by telling me that I could get where I wanted to go by simply adding the following line above or below the [export.file = "c:\test.txt"] line:

                    export.fetch_parent = .t.

                    Much simpler, though none of this would be necessary if Alpha would just change the way exporting a the current record from a set works. Maybe someday.

                    Ray Lyons

                    Comment


                      #11
                      RE: Export takes way too long

                      What file format are you exporting to?
                      Gabe

                      Comment


                        #12
                        RE: Export takes way too long

                        Gabe,

                        As in my last reply:

                        export.file = "c:\test.txt"
                        export.options = "QLT"
                        export.field_sep = ","
                        export.record_sep = ""CR""LF""

                        i.e., comma delimited with double quotes around character fields. But you get the same problem with any type of export file, as far as I have tested.

                        And, yes, there surely are Xbasic ways to take that text file and strip out the extraneous lines. But now that is not necessary and in any case it would still leave the fundamental problem of a saved export running through a half million records to find the current one (!). Unless of course you do something like what I outlined in my last reply.

                        Ray

                        Comment


                          #13
                          RE: Export takes way too long

                          Ray;
                          I won't lie to you, I didn't read but your first post since the thread is too long to read considering the answer is not very complicated. So this might have already been suggested:
                          1-Create a saved export operation instead of resorting to dot varibles as you did.
                          2-xbasic (OnPush event of a button):
                          current_record = alltrim(str(recno(),10,0))
                          export.run_silent("Operation_Name", "BETWEEN(RECNO(),"+ current_record+ "," + current_record+")")
                          That's it.
                          If you are exporting to Excel, it would be even a lot easier.
                          Gabe

                          Comment


                            #14
                            RE: Export takes way too long

                            Gabe,

                            The problem with your suggestion is that it won't work. Indeed, that basically defines the problem, running a saved export filtered any which way you want will still run through a primary table with 500,000+ records. The only saving grace is that it gets the desired result. But it unnecessarily takes way too long.

                            Use a global variable set on a form button that runs the modified version of the raw code the saved export generates gets a result instantly. Add Selwyn's one liner and you even get the desired result in sets with one-to-many relationships.

                            It might not be very complicated, but it is way too complicated when what is needed is simply running a filterd saved import that novice users can create through a genie and easily modify thereafter. Asking them to do more than that is asking for trouble and an unhappy client.

                            Sorry about the long thread. It just took a lot of suggestions that didn't work to finally get to the bottom of the problem.

                            Ray

                            Comment


                              #15
                              RE: Export takes way too long

                              A message from Selwyn:

                              "this is resolved now. see build 2041
                              which will be available shortly"

                              You gotta love this man and this company and this software!

                              Ray Lyons

                              Comment

                              Working...
                              X