12 Replies Latest reply on Jul 14, 2011 1:32 AM by pauland

    How to cancel/resend HTTP Request (due to slow SQL query)

    brandon_05mn Level 1

      Hi everyone- I have a flex application that first requires a login. The login queries my database to check if it is a valid username/password. 95% of the time the query to check if it is a valid username/password takes almost no time (less than a second), and the user is logged in very very quickly. But on occasion, for some reason, either the query takes a long time, or something goes on where the valid/invalid result isn't returned for about 20-40 seconds. If I hit the submit button again, it will resend the SQL request, and (usually) work fine very quickly. It is a simple query to find the username/password, so I think something else might be going on why it it taking so long.

       

      Two questions:

      1. Any idea why this might be happening only a portion of the time, and most times it works just fine? Any suggestions or fix for this?

      2. One idea to fix this that I came up with, I could just cancel and resend the request. Anyone know how I can cancel and resend the most recent httprequest? Should I have a check to see if I make a query, and the result isn't returned within 2 seconds, then cancel the current request and resend it? This doesn't fix the real issue, but it does provide a workaround. If I was a user logging in, and a login page took 30 seconds 5% of the time I log in, I would get bored and leave, or think it isn't working.

       

      Note: I have may queries on the website, that the same issue is happening, but the user login query is the most visible and most important. I would like to find a solution to avoid other issues too.

       

      Please help! Thanks!!

       

      As a side note, if you want to see the website/application that I am talking about, it is www.yahtzo.com

      The website is fully functional, just trying to fix a few bugs. If you have time, play a few games!

      Thanks!

        • 1. Re: How to cancel/resend HTTP Request (due to slow SQL query)
          Benoitcn Level 1

          seem no way to cancel a http request

          • 2. Re: How to cancel/resend HTTP Request (due to slow SQL query)
            Claudiu Ursica Level 4

            What are you useing a HTTP Service? or URLLoader?

             

            C

            • 3. Re: How to cancel/resend HTTP Request (due to slow SQL query)
              brandon_05mn Level 1

              Looks like http request- here is a code snippet-

               

              public function mysqlQuery(sql:String,fid:String):void {

              var http:HTTPService = new HTTPService;

              var parm:Object = new Object;

              parm.fas_sql = sql;

              parm.private_key = private_key;

              parm.fas_db = mysql_db;

              http.url = mysql_url+"?irand="+Math.random();

               

              http.showBusyCursor = true;

              http.request = sql;

              http.addEventListener(ResultEvent.RESULT, mysqlResult);

              http.addEventListener(FaultEvent.FAULT, mysqlFault);

              http.method = "POST";

               

              sqlToken = http.send(parm);

              sqlToken.param = fid;

              }

              • 4. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                Claudiu Ursica Level 4

                HTTPServcie has a requestTimeout property, I don't remember exactly if there's a default. Set that to a custom value and if you don't get any response for that amount of time the HTTPService will fire the FaultEvent. Inspect the event you might get some additional info to know that it was a timeout.

                 

                C

                • 5. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                  pauland Level 4

                  Set a flag when you send the request to validate the login:  logonInProgress = true;

                   

                  Whenever the user clicks the submit button, check the flag before making another logon request: if (!logonInProgress) validateLogon();

                   

                  Paul

                   

                  [edit: When you get a response or the request times out, clear the flag..  ]

                  1 person found this helpful
                  • 6. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                    brandon_05mn Level 1

                    I was able to set requestTimeout. Now my query can timeout after X number of seconds. The error is caught in the error function (error is returned as 'Request timed out. So I should be able to detect which query was sent, and can re-call that query. FYI- the default value for requestTimeout is -1. Anything at zero or less than zero means that there is no time limit. 

                     

                    Thanks for the help!

                    www.yahtzo.com

                    • 7. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                      pauland Level 4

                      Looking again at your original post, I'd suggest a few things.

                       

                      If your timings are long to get a response from the server, measure how long they take inside your code (save the time when the request was made, take the time when you get a response and then subtract one from the other) then you can know for certain if it's a flex issue or server issue.

                       

                      If logins are sometimes slow, indicate to the user that login is in progress.

                       

                      Make sure that multiple login attempts cannot happen while one is in progress (as per my earlier suggestion and/or hiding the login button). Hitting submit repeatedly should not result in multiple logon requests being sent to the server.

                       

                      Since http requests cannot be cancelled never adopt a strategy of repeating a request without waiting for a timeout - asking a slow server multiple times for information will only make it slower.

                       

                      Glad it's working now.

                      • 8. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                        brandon_05mn Level 1

                        Hi everyone-

                        Thanks to your help/suggestions, I was mostly able to solve the issue.

                         

                        I was able to set HTTP request timeout, and the query would terminate with a  "Request timed out" error message (and the case of a successful SQL return was not hit). When the request timed out error occurs, it figures out which was the query that timed out, and I resend the request. It works great - for select calls.

                        But I also have a feature on my website where a user can 'sign in as a guest', and when that happens, a dummy row is inserted in my user table. I also set up the timeout/resend query for this action. But I have found, that this causes DUPLICATE ROWS to be inserted!

                         

                        If the user clicks the 'sign in as guest' button, the guest row will be inserted, if the request times out, it will hit the error logic and resend the request. Upon the SQL being resubmitted, if the request didn't time out, the guest user is granted access to the rest of the website. But I have found that two (or more than two depending on how many times the query was resent) guest rows were inserted into my table!

                         

                        So the HTTP request timeout feature cancels the result from being returned if it hits the timeout duration, but it does not cancel the actual sending of data to the table!

                         

                        Does anyone have some good solution or idea for what I can do to change my logic so multiple rows are not inserted?

                         

                        Thank you!!!

                        • 9. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                          pauland Level 4

                          Sounds like your time out period is too short (or your SQL insertion is taking too long, depending on which way you look at it).

                           

                          You need to measure how long the database queries/inserts take.

                           

                          You can compensate for the problem by checking before you try and login a person (ie insert a new row) that they aren't already logged in. If they are already logged in, just return an OK status.

                          • 10. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                            pauland Level 4

                            I think that your timeout period should reflect the longest period you are prepared to allow a transaction to take. Ideally, all transactions should take place within that time. If your query/update exceeds that timeout period (or a little less) you should be rolling back the transaction to avoid duplicate entries, or you need a more sophisticated approach to inserts and updates.

                             

                            If merely logging in a user is taking a significant time, I'd be asking why the database transactions are so slow.

                            • 11. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                              Claudiu Ursica Level 4

                              There is a cancel method on HTTPService invoke that upon timeout also. IN addition you can check in the db if the row has not been already inserted by mistake upon re-submision. If the row is already in the db perform only an update with the relevant fields if any.

                               

                              C

                              • 12. Re: How to cancel/resend HTTP Request (due to slow SQL query)
                                pauland Level 4

                                I'd also add that the user could already be logged in on the original login attempt - this could be because he's logged in on another device, or has not logged out previously, or there has been a failure leaving the users login record in place. If there is a login record already in place, you can't assume that it's OK to log the user in again because it could be that there is an active session for that user elsewhere. You also need to be able to remove entries that are only there because the user has forgotten/failed to logout. These would need some association of the user to the http session and perhaps a timeout field to detect inactivity.

                                 

                                There can be quite a lot to do when logging somebody in!