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 Does A SQL Inner Join Bring Performance To A Crawl?

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

    #16
    WOW!!!!!!!!!! that makes quite a difference.
    Just to be fair, I use mysql. I'm very inexperienced with lists, but seem to do well with sql.
    I created a list that uses the query shown below,
    The samsaraugdrivers has 96 rows, the samsaradutylogs table as almost 232000 rows.
    The time to load the data, and the responsiveness while scrolling through the data is sluggish at best without using virtualization.
    Using virtualization, I get my data relatively quickly and am quite happy with the speed scrolling through the data.
    I opted against pagination and turned off the Get count of Records option because I wanted to avoid having the query performed twice.

    Now I need to learn how to create a search option and selectively display fields. Being able to show more data by clicking on a cell would be even better.

    I know it's late to say in short, but try the virtualization setting. It really makes a difference.


    2021-08-26_14-19-51.png
    Code:
    SELECT DL.seq,DL.logdate,DL.samid,SD.idnum,SD.drivername,DL.dutystatus,DL.statusbeg,
    DL.statusfin,DL.latitude,DL.longitude,DL.samvehid,DL.codriver
    FROM samsaradutylogs DL
    LEFT JOIN samsaraugdrivers SD ON DL.samid = SD.samid
    Gregg
    https://paiza.io is a great site to test and share sql code

    Comment


      #17
      Have you tried the view yet? That would take the join out of Alpha completely. (And you need to index the foreign key.)

      If you would like, I could hop on a teamviewer session with you to take a look.

      If you want to do that, private message me and I'll give you my direct email/phone.

      Comment


        #18
        So three things:
        1) If you go to the query definition in Alpha and click on Native SQL you can see if there's something weird about the query being run in SQL Server.
        2) the demo video in the app is using a where clause and the demo in SSMS isn't. Is the query still lightning fast in SSMS when you filter by the criteria you're showing in the Alpha video?
        3) I've found that LEFT OUTER JOIN can perform a lot faster than INNER JOIN at times, so if it will produce the same records, you could try that.

        Comment


          #19
          Originally posted by lgrupido View Post
          Have you tried the view yet? That would take the join out of Alpha completely. (And you need to index the foreign key.)

          If you would like, I could hop on a teamviewer session with you to take a look.

          If you want to do that, private message me and I'll give you my direct email/phone.
          Sorry, no Larry I haven't had time yet, been putting out fires at my real day job
          Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

          Comment


            #20
            Originally posted by christappan View Post
            So three things:
            1) If you go to the query definition in Alpha and click on Native SQL you can see if there's something weird about the query being run in SQL Server.
            2) the demo video in the app is using a where clause and the demo in SSMS isn't. Is the query still lightning fast in SSMS when you filter by the criteria you're showing in the Alpha video?
            3) I've found that LEFT OUTER JOIN can perform a lot faster than INNER JOIN at times, so if it will produce the same records, you could try that.
            1) I don't see anything different than what the query syntax shows in my first post
            2) That's true, I never thought about it but you're right, the list filter actions has to introduce a where clause. But, as this shows, it makes no difference to the speed that SSMS returns the result ... https://www.screencast.com/t/AK96IGFyi
            3) I just tried a FULL OUTER JOIN instead but it is still fast in SSMS and slow in Alpha. I'm still left with why it is okay in SSMS but not in Alpha.
            Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

            Comment


              #21
              Originally posted by lgrupido View Post
              Have you tried the view yet? That would take the join out of Alpha completely. (And you need to index the foreign key.)
              I have tried the view this morning, and this works well. Returns searches in about 2-3 seconds.

              I noticed something interesting though, when I tried to change the list data source from my current sql query to the new view, it complains that it can't change the source because my source query is too complex. It points to the CASE statement as the culprit.
              Maybe that was the whole issue to start with, though it runs just fine in the list until I tried to add the join.

              SQL Soutce.jpg

              I just deleted the custom query then chose the view instead, but I wonder why it complains about the CASE statement and if that was causing the issues.


              I'm going to keep playing around with this since it definitely seems to be the way to go in terms of performance.
              Thanks to all for all the great ideas.


              Last edited by iRadiate; 08-28-2021, 09:47 AM.
              Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

              Comment


                #22
                One more quick question, this is the first time I have used a view. My original table is called 'repairs' and the linked table is called 'machines'

                I called the view 'vw_repairs'

                Does anyone have any preferences on naming convention for views?

                Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

                Comment


                  #23
                  I usually put the word view in the name to be able to easily identify views while performing queries. It's obviously a personal choice,
                  and helps me when it's time to make changes or track down problem code.
                  Gregg
                  https://paiza.io is a great site to test and share sql code

                  Comment


                    #24
                    Your case statement should be in your view already, so you don't need it any longer in Alpha. However, clicking portable SQL will often help when trying to add SQL functions to select statements. (A view is still often a better choice though.)

                    vw_repairs is a fine name so long as you always want machines linked to your repairs view. Technically, if you ever need a view just over the repairs table ( like maybe you have a bunch of calculations you want to do ) that would be better for a view called just vw_repairs. So you could call it vw_repairs_machines instead.

                    you can also call it v_repairs or view_repairs or repairs_vw - really anything. Just so long as you pick something and try to stick with it so you are not looking all over trying to find the view you want and figure out what you were thinking when you wrote it!

                    Sometimes you might want to join together three or more tables, so then calling the view vw_table1_table2_table3 etc gets a bit crazy. So instead you might name the view by how it is used, like vw_repairs_for_billing

                    Comment


                      #25
                      Originally posted by lgrupido View Post
                      Your case statement should be in your view already, so you don't need it any longer in Alpha. However, clicking portable SQL will often help when trying to add SQL functions to select statements. (A view is still often a better choice though.)

                      vw_repairs is a fine name so long as you always want machines linked to your repairs view. Technically, if you ever need a view just over the repairs table ( like maybe you have a bunch of calculations you want to do ) that would be better for a view called just vw_repairs. So you could call it vw_repairs_machines instead.

                      you can also call it v_repairs or view_repairs or repairs_vw - really anything. Just so long as you pick something and try to stick with it so you are not looking all over trying to find the view you want and figure out what you were thinking when you wrote it!

                      Sometimes you might want to join together three or more tables, so then calling the view vw_table1_table2_table3 etc gets a bit crazy. So instead you might name the view by how it is used, like vw_repairs_for_billing
                      Yes, the case statement is in the view. It was just complaining when I tried to switch over from the original custom query to using the view, that's all.

                      I think I like vw_repairs_machines better so I know it is linked with the machines table.

                      Once again, thanks for all the help .. much appreciated! And, I learned something about views
                      Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

                      Comment


                        #26


                        Hello There,

                        In order for you see how the query looks like I will use In MS SQL management studio the Execution Plan option found here:


                        MS-SQL-Execution-Plan.jpg

                        Adding index on the foreign key is MUST but does not necessarily resolve the problem all the time since you may need to include additional field in that index.

                        The CASE WHEN with the LIKE could be a major problem and slow down performance.

                        This is how an Execution Plan looks like after a query is running. Hoover on top of each item show where the problem is.

                        Also for a List View un-check the this property: Get count of Records in query.
                        Otherwise that query will run twice to figure out the number of total records the data set created.


                        MS-SQL-Actulal-Execution-Plan.jpg

                        Regards,

                        Doron
                        The Farber Consulting Group, Inc.

                        Main Web Site: http://www.dFarber.com

                        Avis Car Rental Software
                        MS SQL Blog: http://www.dfarber.com/computer-consulting-blog/

                        Comment


                          #27
                          Same query with JOIN but using the view instead of SQL query in the list builder ...

                          <2 seconds ... https://www.screencast.com/t/PUd3fYflR3


                          That's a heck of a lot better than >15 seconds for whatever Alpha is doing with the query.
                          Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

                          Comment


                            #28
                            Originally posted by DoronF View Post
                            The CASE WHEN with the LIKE could be a major problem and slow down performance.
                            This is ONLY the case when Alpha handles the query in the list builder. SSMS has absolutely no issue running the query with the CASE WHEN and LIKE clause very quickly, as demonstrated by the video links I posted.

                            Originally posted by DoronF View Post
                            Also for a List View un-check the this property: Get count of Records in query.
                            Otherwise that query will run twice to figure out the number of total records the data set created.
                            I do not return the record count.
                            Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

                            Comment


                              #29
                              SMMS Query Execution Plan (with the ORDER BY clause)

                              Query Execution Plan (with ORDER BY).jpg


                              SMMS Query Execution Plan (without the ORDER BY clause)

                              Query Execution Plan (without ORDER BY).jpg
                              Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

                              Comment


                                #30
                                Hi There,

                                Where ever you see and Index scan it isn't what you want to see in a query plan. You need to try to turn scan into Seek. which means retrieving the data by just using the index tree.
                                Make sure you have indexes for each Join. Include some of the fields in the Order within the index of the FK and see.
                                In addition you don't have any WHERE Clause , that may cause the Server to scan the entire clustered index to return all rows.

                                Regards,

                                Doron
                                The Farber Consulting Group, Inc.

                                Main Web Site: http://www.dFarber.com

                                Avis Car Rental Software
                                MS SQL Blog: http://www.dfarber.com/computer-consulting-blog/







                                Comment

                                Working...
                                X