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 to stop an sql process

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

    how to stop an sql process

    I have an app where the tables are in the 30-100 million records. The users like to click the column sort to sort found records. But given the record size it takes too long and the user clicks other sort links, then closes the grid, and then attempts to re-open the grid. But the SQL process from the original sort attempt is still running and the grid will not re-open. Is there any way to ABORT a sort or other sql process if the component is closed?
    Steve Wood
    See my profile on IADN


    #2
    Re: how to stop an sql process

    Hi Steve,

    >> Is there any way to ABORT a sort or other sql process if the component is closed?
    I don't think that is possible.
    For those numbers, using Stored Procedures with proper parameters to limit the amount of records being load by the grid, and not to allow blank parameters that would bring a lot of records.
    Edhy Rijo
    Progytech
    (Computer Consultants)
    The makers of CardTracking.Net
    www.progytech.com

    Comment


      #3
      Re: how to stop an sql process

      Do you have indexes set up for each of the sort options? That would make it much faster.
      Pat Bremkamp
      MindKicks Consulting

      Comment


        #4
        Re: how to stop an sql process

        Only thing I know of is the back-button, seems to log folks right out of a tabbed UI, lol So along that lines if the process was going on in a div container that you could close...
        NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

        Comment


          #5
          Re: how to stop an sql process

          Hi Steve

          Not sure you can without completely breaking the connection with the sql server instance and/or having administrative privileges.
          Glen Schild



          My Blog

          Comment


            #6
            Re: how to stop an sql process

            Edhy, SP is the way I want to go but need to figure out how. There are Alpha-only methods that get part way there but break down with the large record set; the soln has to be complete including sorting, individual column sort, filter/search, etc.

            Also, even leaving the tabbed ui or closing the rowser does not stop the sql process once started. User's get impatient and close the browser then try to go back. Eventually I want to estimate the time, tell the user it will take x seconds for the results so they will make an informed decision.
            Steve Wood
            See my profile on IADN

            Comment


              #7
              Re: how to stop an sql process

              Hi Steve,
              >> Edhy, SP is the way I want to go but need to figure out how
              I can help you out with the stored procedure, but our friend Doron Farber has done an excellent job creating searchable grids and UX with MS stored procedures, take a look at this article in his blog Optimize your sql query for best search result, you can email him directly and I am sure he will be able to point you to some of his videos using his technique, I will also let him know about this thread.
              I am on my way out and will be back around 5pm EST, feel free to call me or email me, you have my contact information.
              Edhy Rijo
              Progytech
              (Computer Consultants)
              The makers of CardTracking.Net
              www.progytech.com

              Comment


                #8
                Re: how to stop an sql process

                A web session is non-persistent and stateless even that connection is lost you can't simple cancel the proces at the DB engine, it's mostly done with a sql management tool.

                A first step is that you partiton the tables with very large record set. call dynamic sorts with view by a stored proc. If your DB size is about 30G then allow the DB to reserve at least 30% ram that saves writing to disk.
                If you want clean or kill threads at the db server the only that it can be done is by query the information_schema, you need the instance_id, processnumber and threat that is running to kill it.

                There is some risk that you might lose connections with other connected if your not exactly sure what process_id is bound to that session at the client side. My preference for such large databases is that you connect to by a named userid and not a general user like root <you should always provoke user root in production> so the user name is available and in the db user schema with grant options by role or what ever you like.

                By that you can check what is active by userid at the db site. here id an example howto so it can be done in Alpha the result is pushed trough curl to the WAS.
                http://www.psce.com/blog/2012/04/04/...ries-in-mysql/


                Enjoy! AND HAPPY NEW YEAR

                Comment


                  #9
                  Re: how to stop an sql process

                  Hello Steve,

                  Check this Stored Procedure I did that can handle large scale of database. Alpha Five is using dynamic SQL to perform the search via the grid probably. In the article Optimize your sql query for best search result I created a Stored Procedure that will enable the user to search in hundreds of millions records.
                  But the database optimization needs to be there as well.


                  Regards,

                  Doron
                  The Farber Consulting Group, Inc.

                  Main Web Site: http://www.dFarber.com
                  MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
                  Convert Ms Access to Web
                  Custom Software Development
                  Alpha Five Development
                  No Interest Loans Application

                  Comment


                    #10
                    Re: how to stop an sql process

                    Thank you. Using Stored Procedures is something missing from my bag of tricks. This particular customer has several interesting challenges that I cannot do effectively directly from Alpha. Like I need to search the table using SOUNDEX which I do not see as an Alpha search option. Most of the queries are against tables with tens or hundreds of millions of records.
                    Steve Wood
                    See my profile on IADN

                    Comment


                      #11
                      Re: how to stop an sql process

                      Steve,

                      What type of info do you need to search for while Soundex has limits by example no digits, alternative is Full text search can you alter the DB?

                      try this as alternative for soundex

                      SELECT
                      p.title,
                      p.sku,
                      COALESCE(p.price, g.price) AS price,
                      g.name
                      FROM products AS p
                      LEFT JOIN productgroups AS g
                      ON p.group_id = g.id


                      SELECT
                      p.id,
                      p.title,
                      GROUP_CONCAT(t.tag) AS tags
                      FROM products AS p
                      LEFT JOIN products_tags AS pt
                      ON pt.product_id = p.id
                      LEFT JOIN tags AS t
                      ON t.id = pt.tag_id
                      GROUP BY p.id
                      ORDER BY p.title
                      Last edited by bea2701; 01-06-2015, 03:00 AM.

                      Comment


                        #12
                        Re: how to stop an sql process

                        Hi Steve,

                        In the stored procedure named: Optimize your sql query for best search result there are multiple search capability and passing parameters provides you different result for the specific search you need. This type of search is fully optimized and search by the specific fields you need. It is not dynamic sql which can reduce performance in large scale of database.

                        Regards,

                        Doron
                        The Farber Consulting Group, Inc.

                        Main Web Site: http://www.dFarber.com
                        MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
                        Convert Ms Access to Web
                        MS SQL Remote DBA
                        Alpha Five Development
                        No Interest Loans Application

                        Comment


                          #13
                          Re: how to stop an sql process

                          Steve how many records are they needing to sort at a time?
                          You may want to first build a view that limits how many to something like 10000. That way when they click sort it will only be sorting the first 10000 records found using that sort, instead of it sorting an entire table of millions of records.
                          If the original table has some indexes set up for the common sort types you will find that it will return very quickly.
                          You could also display a message while the sort is happening with a gif or something to entertain them.
                          I have found that message boxes slow down the double clickers...
                          Just a though....
                          Chad Brown

                          Comment


                            #14
                            Re: how to stop an sql process

                            I've already ruled out a View with a predefined Limit. Say I wanted to return the first 100 records where LastName='Wood'. If I use a View that limited the records to the first 10000; what if there are not 100 Wood's in the first 10000, but there are in the first 20000? I NEED the first 100 Woods even if I have to search the entire table to get them.

                            Instead I am looking at the exact sql automatically produced by Alpha and determining if that is the most efficient sql query. I am also looking at a 3rd party indexing utility made for large data sets.
                            Steve Wood
                            See my profile on IADN

                            Comment


                              #15
                              Re: how to stop an sql process

                              HI Steve,
                              The reason to use parameters is to limit the amount of records to bring to the grid, I don't quite understand why in the previous example of records where LastName='Wood' you have to bring thousands of records to choose those with LastName='Wood'.
                              I understand that the logic of this application may require you to show all these records, but I just find it a bit odd. For example, I have a .Net application in which I have to import over 600,000 records a week, but I don't even show these records in a grid because the end user will not really look at each one, just a set of them to do something else, so all my process are done at the MS-SQL server and then show summary of the process to the user.
                              Edhy Rijo
                              Progytech
                              (Computer Consultants)
                              The makers of CardTracking.Net
                              www.progytech.com

                              Comment

                              Working...
                              X