Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 43

Thread: how to stop an sql process

  1. #1
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default 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
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  2. #2
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default 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

  3. #3
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,629

    Default 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

  4. #4
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    2,117

    Default 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...

  5. #5
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,520

    Default 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


  6. #6
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default 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
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  7. #7
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default 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

  8. #8
    "Certified" Alphaholic
    Real Name
    eric
    Join Date
    Mar 2009
    Location
    Amsterdam
    Posts
    1,284

    Default 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

  9. #9
    Member
    Real Name
    Doron
    Join Date
    Dec 2011
    Location
    NJ, USA
    Posts
    174

    Post 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

  10. #10
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default 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
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  11. #11
    "Certified" Alphaholic
    Real Name
    eric
    Join Date
    Mar 2009
    Location
    Amsterdam
    Posts
    1,284

    Default 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 at 03:00 AM.

  12. #12
    Member
    Real Name
    Doron
    Join Date
    Dec 2011
    Location
    NJ, USA
    Posts
    174

    Post 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

  13. #13
    "Certified" Alphaholic chadbrown's Avatar
    Real Name
    Chad Brown
    Join Date
    Aug 2007
    Location
    Aurora, Ontario, Canada
    Posts
    1,408

    Default 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

  14. #14
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default 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
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  15. #15
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default 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

  16. #16
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default Re: how to stop an sql process

    Edhy,

    My client is pretty rigid, wants what he wants. I am likely going to use the stored procedure and optimisation methods described in this thread. The full picture is the table is around 300 million records and if the user searches for LastName=wood and gets 500K records, I am to pop a msg saying "Dude narrow your search" and keep doing that until the record count is under 100.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  17. #17
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default Re: how to stop an sql process

    Hi Steve,

    Quote Originally Posted by Steve Wood View Post
    The full picture is the table is around 300 million records and if the user searches for LastName=wood and gets 500K records, I am to pop a msg saying "Dude narrow your search" and keep doing that until the record count is under 100.
    That is not what I meant, sorry if I gave you the wrong impression.
    All I am trying to do is understand your clients requirements and have a clear picture why anybody would like to see a grid with 500K or 1M records instead of an Excel or CSV file.

    My clients will always ask many things but I tend to ask the reasons why so I can then suggest the best approach based on facts and limitations.
    Edhy Rijo
    Progytech
    (Computer Consultants)
    The makers of CardTracking.Net
    www.progytech.com

  18. #18
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default Re: how to stop an sql process

    For sure the grid would only show the first 100 max. I would only want to return the first 100 (or so) in the query. I just meant a simple server side view with a limit is not going to work because the limit would not take the filter in to account. I know how to handle most clients needs but this guy is a bit more insistent (by which I mean he strives to give his users what they need).
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  19. #19
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: how to stop an sql process

    Quote Originally Posted by Steve Wood View Post
    Edhy,

    My client is pretty rigid, wants what he wants. I am likely going to use the stored procedure and optimisation methods described in this thread. The full picture is the table is around 300 million records and if the user searches for LastName=wood and gets 500K records, I am to pop a msg saying "Dude narrow your search" and keep doing that until the record count is under 100.
    this statement is different from the post number 1. may be they are the same to you since you are involved, deal with this everyday and I am not.
    is it possible then to have a dialog and ask the user to search and use the search terms to count the record yield and pop up the message till the list is down to earth from stratosphere then proceed to the grid to display the result?
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  20. #20
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default Re: how to stop an sql process

    Thanks Gandhi,

    The discrepancy is that the first post talks about website users and the most recent talks about my client.

    I agree that I should just do a count based on their search and return a number only, tell them to refine search, etc. I showed my client a product by Omnidex (thanks Al) where returning results (counts, soundex, etc) from large tables is instant.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  21. #21
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: how to stop an sql process

    the omnidex demo does exactly what I said.
    you have ton of experience, won't you be able to do something like that with a dialog and onChange events to display the results the same way they did?
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  22. #22
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default Re: how to stop an sql process

    Yes I could duplicate but it would take many minutes to return those counts instead of instant as with the Omni add-on. I have not determined price and how to use with Alpha. I know I would have to use ODBC rather than AlphaDAO.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  23. #23
    "Certified" Alphaholic
    Real Name
    eric
    Join Date
    Mar 2009
    Location
    Amsterdam
    Posts
    1,284

    Default Re: how to stop an sql process

    Quote Originally Posted by Steve Wood View Post
    Edhy,

    My client is pretty rigid, wants what he wants. I am likely going to use the stored procedure and optimisation methods described in this thread. The full picture is the table is around 300 million records and if the user searches for LastName=wood and gets 500K records, I am to pop a msg saying "Dude narrow your search" and keep doing that until the record count is under 100.
    Set the soundex of of your popup message limit to 5 ! that's smart developing ""

  24. #24
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: how to stop an sql process

    http://www.percona.com/blog/2006/06/...-large-tables/
    take a look at this article, may be there is something to consider.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  25. #25
    "Certified" Alphaholic chadbrown's Avatar
    Real Name
    Chad Brown
    Join Date
    Aug 2007
    Location
    Aurora, Ontario, Canada
    Posts
    1,408

    Default Re: how to stop an sql process

    Removed was incorrect.
    Last edited by chadbrown; 01-08-2015 at 10:11 AM.
    Chad Brown

  26. #26
    "Certified" Alphaholic chadbrown's Avatar
    Real Name
    Chad Brown
    Join Date
    Aug 2007
    Location
    Aurora, Ontario, Canada
    Posts
    1,408

    Default Re: how to stop an sql process

    Steve what about assigning a limit using a variable that the user can set?
    Use a session variable set on login to say 10000 but have a spot to increase it using a textbox that on change sets a new limit.
    Chad Brown

  27. #27
    Member
    Real Name
    Doron
    Join Date
    Dec 2011
    Location
    NJ, USA
    Posts
    174

    Post Optimize your sql query for best search result

    Hi Steve,

    Regarding the search here is some sample code how to limit the max records say to 100. Then you display a red line message to limit the search. There is no problem when you place a search criteria like: LastName='Wood' since the first 100 records will be displayed. I have done this kind of work with dot net as well 5 years with TB of databases and the standard is to show 100 records every search no matter what.

    Code:
    IF @Address IS NULL AND @City IS NULL AND @State IS NULL AND @Zip IS NULL
                BEGIN
                    INSERT INTO SearchResults
                    SELECT TOP 100
                        UserID = @UserID , 
                        Cu.ID,
                        'Customer' AS SearchType
                    FROM
                        Customer Cu
                    WHERE
                        (Cu.FirstName LIKE @FirstName + '%' OR @FirstName IS NULL) AND
                        (Cu.LastName LIKE  @LastName  + '%' OR @LastName IS NULL)  
                         
                    SELECT @@ROWCOUNT AS TotalRecs
                    RETURN     
          END
    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
    MS SQL Remote DBA
    Last edited by DoronF; 01-08-2015 at 10:52 AM.

  28. #28
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,172

    Default Re: how to stop an sql process

    If you are experiencing very slow queries in SQL Server using expressions such as LIKE ('contains' and 'starts with' searches), there is a known performance issue in SQL Server if the fields are not unicode (not nvarchar() or nchar()). SQL Server will create a calculated field value for each record in the table using CONVERT_IMPLICIT to use in the query expression, which can be very slow.

    Alpha Anywhere added an option that will dramatically improve speed, particularly with large tables. This was covered in the "Whats new for Alpha Anyhwere" documentation.

    http://news.alphasoftware.com/v12pre...seNotesV12.htm

    Just search for "AlphaDAO - SQL Server - Slow queries "

  29. #29
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,838

    Default Re: how to stop an sql process

    I will review that document. I happen to be using MySQL and still don't understand why SELECT lastname FROM mytable WHERE lastname LIKE 'wood%' (plus a limit of say 1000), on a 300 million row table takes seconds directly on the sql engine (via Navicat) and 20+ minutes coming from Alpha.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  30. #30
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,629

    Default Re: how to stop an sql process

    Steve, I think Alpha has more to do than Navicat, for example they may be doing a full table scan to get a count of records.

    So, I'd try this: create a temporary table ( or I've been using memory engine tables lately for situations like this where I need the table for only a short while) and create a stored procedure that truncates the memory table, then inserts the 1000 records from your 300 million table. Now, use that table for the grid. As far as Alpha is concerned, the table only has at most 1000 records that are already sorted and don't need filtering, so it should be fast.
    Pat Bremkamp
    MindKicks Consulting

Similar Threads

  1. a5 Process Name
    By omagarc in forum Alpha Five Version 10 - Desktop Applications
    Replies: 9
    Last Post: 10-10-2012, 03:27 PM
  2. Stop Developing, Stop Fiddling, just get it OUT THERE!
    By Ted Giles in forum General Questions
    Replies: 13
    Last Post: 04-07-2012, 04:25 PM
  3. getting process is being used by abother process
    By gkeramidas in forum Alpha Five Version 4
    Replies: 1
    Last Post: 11-17-2005, 02:32 AM
  4. Stop The Tab Stop Insanity
    By Roy Irby in forum Alpha Five Version 5
    Replies: 7
    Last Post: 04-23-2003, 11:56 AM
  5. DB in use by other Process
    By Debbie Keller in forum Alpha Five Version 4
    Replies: 2
    Last Post: 07-18-2001, 03:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •