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

Why sluggish set performance via Active Link?

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

    Why sluggish set performance via Active Link?

    My form has an embedded browse. It is bound to a set, total five tables. One top level, four are level two. All links are 1:1 and all linking fields are numeric. Top table is seeded with 27 rows and an aggregate column count across all tables likely less than 40.

    Against local Alpha dbf tables, default browse view of set opens and fully and correctly populates in under one second.

    Exported all tables to remote MySQL DB. Did all required set up (as far as I know) for Active Link. Default browse view of set now very sluggish, taking upwards of six seconds to fully populate with 27 rows of primary table with selected columns from child tables.

    Having built my own SQL select and running it directly at MySQL command line, all rows are correctly returned within 0.25 seconds.

    Executing the same select within Alpha's SQL genie also correctly returns all rows within roughly 0.25 seconds.

    Is there a performance problem with Active Link with as few as five tables? Is there a way to see the SQL generated by Alpha during the set query operation (to verify it is correct)? What's going on here, and how do I achieve the performance I need?
    Last edited by fsi; 05-27-2008, 10:11 PM.

    #2
    Re: Why sluggish set performance via Active Link?

    I suspect the fact that active links don't have index definitions the same way DBF files do may have something to do with it.

    Have a look at Views in mySQL, and try to use mapped tables for 1 on 1 links wherever possible. There are much faster ways to retrieve data from parent and child tables in SQL - and keeping 5 or more active link tables open seems wasteful, especially if you haven't set up primary keys properly. (I have however noticed some odd behaviour with interpreting primary keys with the AlphaDAO Oracle driver before)

    Comment


      #3
      Re: Why sluggish set performance via Active Link?

      If active links do not (or can not) implement a suitable fashion of indexing, I suspect you have hit close to the mark. I haven't found a lot of technical detail on the internals of active links, so I appreciate your comment.

      As to mapped tables.... I'll play with that. Seems quite a good idea on its face. As to my five tables, I can say they were designed with the greatest degree of normalization in mind. So I'll take slight issue with your opinion that my five tables open are excessive. (but those are not meant as fightin' words!) I had already considered "degrading" the schema to a less normalized structure, but I found the idea rather disturbing from a DB design perspective. As stated, the links were 1:1 on numerical keys... couldn't be much simpler.

      The very first thing I'll do is examine Views in mySQL. If that will permit for a greater portion of server-side processing, I'm all for it. Or maybe it's a combination of Views and mapped tables. (ignorant at this point) On to experimentation!

      Comment


        #4
        Re: Why sluggish set performance via Active Link?

        Originally posted by fsi View Post
        As to my five tables, I can say they were designed with the greatest degree of normalization in mind. So I'll take slight issue with your opinion that my five tables open are excessive.
        I just meant that with active link tables in mind, and their nature of keeping open & looking out for changes in each linked table.

        Comment


          #5
          Re: Why sluggish set performance via Active Link?

          Originally posted by fsi View Post
          The very first thing I'll do is examine Views in mySQL. If that will permit for a greater portion of server-side processing, I'm all for it. Or maybe it's a combination of Views and mapped tables. (ignorant at this point) On to experimentation!
          For displaying data from lots of different tables, especially if many of the fields are read only (e.g. you're just linking to a table to grab the full name of a customer or contact), views really boost performance. they also get around some quirks in the query builders, and allow you to put far more complex queries against the database than you normally could in the Alpha query builders.

          I also use SQL_LOOKUP() quite a bit because it's faster than having to link all these tables just to grab a name field etc.

          Comment


            #6
            Re: Why sluggish set performance via Active Link?

            Originally posted by fsi View Post
            My form has an embedded browse. It is bound to a set, total five tables. One top level, four are level two. All links are 1:1 and all linking fields are numeric. Top table is seeded with 27 rows and an aggregate column count across all tables likely less than 40.

            Against local Alpha dbf tables, default browse view of set opens and fully and correctly populates in under one second.

            Exported all tables to remote MySQL DB. Did all required set up (as far as I know) for Active Link. Default browse view of set now very sluggish, taking upwards of six seconds to fully populate with 27 rows of primary table with selected columns from child tables.

            Having built my own SQL select and running it directly at MySQL command line, all rows are correctly returned within 0.25 seconds.

            Executing the same select within Alpha's SQL genie also correctly returns all rows within roughly 0.25 seconds.

            Is there a performance problem with Active Link with as few as five tables? Is there a way to see the SQL generated by Alpha during the set query operation (to verify it is correct)? What's going on here, and how do I achieve the performance I need?
            what is your set definition?

            how many tables and how are they linked?

            Comment


              #7
              Re: Why sluggish set performance via Active Link?

              Originally posted by fsi View Post
              My form has an embedded browse. It is bound to a set, total five tables. One top level, four are level two. All links are 1:1 and all linking fields are numeric. Top table is seeded with 27 rows and an aggregate column count across all tables likely less than 40.
              Only just spotted that they are all 1;1 links... you'd be much better off with a mapped table, or, if the browse is readonly, specifying the query that links these tables in a readonly active link definition (or better still in a view on your mySQL database, so the processing is done server-side).

              Comment


                #8
                Re: Why sluggish set performance via Active Link?

                Originally posted by NoeticCC View Post
                Only just spotted that they are all 1;1 links... you'd be much better off with a mapped table, or, if the browse is readonly, specifying the query that links these tables in a readonly active link definition (or better still in a view on your mySQL database, so the processing is done server-side).

                i don't think that mapped tables will make any difference, but Andrea's other point is exactly correct.

                if all of the links are one to one links, (assuming that these conditions are also true)

                a) all of the tables in the set are from the same sql database (likely)
                b) the set is meant for displaying data (not updating it)

                then you would be much better off doing the 'joining' on the server.

                you can either create an active link table with a custom sql select statement that joins all of the tables (the custom sql query builder will guide you through the whole process of making a 5 table join statement)

                or,

                create a view in your database, and based your active-link table on the view.



                we are working on a technique to automatically optimize sets with lots of one-to-one links so that the joins are done on the server (rather than on on the client). we hope to be able to release this as an inline patch in a few months.

                Comment


                  #9
                  Re: Why sluggish set performance via Active Link?

                  I was hoping you'd chime in here, Selwyn.

                  To my great surprise, Andrea's mapped table approach did make a difference. A truly dramatic difference. The form opens and the embedded browse populates fully (via mapped tables) within approx 1 second! This is 20% of the time required - 6 seconds - for standard set operation. Set operation is accompanied by incredible drive grinding on my local machine. Mapped is much more drive-friendly. Despite the improvement, I still did not feel this was the "right" approach; ie: too much work on client side, including data duplication (which always feels contrary to the "Zen" of proper DB access). Further, this improvement to one second for under 30 rows - if it's linear - would be disaster as row count moves into 3, 4, and 5 digits.

                  Yes, all links are truly 1:1; all tables in set are same SQL DB; and the set is meant for display only (no update).

                  Standard set operation seems to degrade rapidly once three tables are in play (even though just a simple parent with two level2 children and numerical keys and 1:1 links). That surprised me. I had read an earlier post where someone was linking 30+ tables or so, and that definitely seemed a performance killer. Wasn't expecting it in just 3-5 tables, though.

                  I did finally implement a View server-side.

                  But now I'm going to experiment with your active link approach with custom SQL select. Since I've already got a proper SQL select, I'll see where it leads.

                  Many thanks to you, Andrea.
                  Last edited by fsi; 05-28-2008, 06:36 PM.

                  Comment

                  Working...
                  X