Win a copy of Serverless Applications with Node.js this week in the NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Ron McLeod
  • Tim Moores
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Vijitha Kumara

Possible to figure out time left for a SQL query?  RSS feed

 
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using JMS to handle asynchronous requests from front end. So, UI sends an Ajax request to a Spring boot controller which generates a message, the controller sends a message to the broker (Active MQ). My consumer spring boot app (which is a separate app), consumes the message, and based on the message, it runs a SQL query and uploads a file on the server. Since my producer and consumer are two different spring boot apps, is it possible for me to figure out how much time a SQL query could take? Basically, if I could show this on the UI, the user would know how long to wait.

OR any other alternative, if I want to figure out the time?

Please let me know if I can answer any more questions. Thanks!
 
Sheriff
Posts: 13392
221
Android Debian Eclipse IDE IntelliJ IDE Java Linux Mac Spring Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Telling users how long they have to wait is a double-edged sword. As it is, there could be a number of factors that are out of your control that determine how much time a SQL Query will actually take so giving an accurate wait time is iffy at best. If the time you give is too long, the user may decide that it's not worth waiting or get frustrated anyway. Either way, it's not a good user experience.

Since you're using an asynch call from the client side, you could just report "Still working on your request... will inform you when it's ready" and use a "toaster notification" or something like that to indicate when the results are ready. At that point, the user can go back to the screen and you can display the results of the query. This might require some caching of the results though and you might have to implement some timeout logic, say if the user does not view the results within 20 minutes of the notification then they'll have to run the query again. Or you could just find some way to cache the results on the client side through an AJAX callback.

These are just some ideas off the top of my head so if any of those sound like something you'd like to try, you'll have to do some experimentation yourself
 
Bartender
Posts: 20580
121
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, there are several problems there.

A JDBC SQL query operation is atomic. It hangs the thread making the request until results are returned. So the request thread itself cannot inform you how long it's going to take until the request has completed (at which point, "remaining time" is zero ). Even spawning a secondary thread (where allowed) wouldn't really help. You may be able to estimate how long a request will take to complete for a given query based on past performance, but, as they say on Wall Street "past performance is no guarantee of future results".

Beyond that, if the end user is making a web request, standard HTTP protocols don't permit live updates to a web page. The HTTP request won't return until the response is 100% complete, so here again, remaining time would always be zero.

These days it's common to use AJAX to display progress indicators, but that only works if the initial request was made to an external asynchronous "engine" thread that can be polled by AJAX. And at that point, again. JDBC is still a sticking point for progress determination.

Adding things like JMS and web callbacks can add to complexity, but the fundamental bottlenecks remain the same. So the only real answer is that it'll be ready when it's ready, and if the user's patience is strained, then try and make the database request as quick and as precise as possible.
 
Jack Tauson
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Actually, there are several problems there.

A JDBC SQL query operation is atomic. It hangs the thread making the request until results are returned. So the request thread itself cannot inform you how long it's going to take until the request has completed (at which point, "remaining time" is zero ). Even spawning a secondary thread (where allowed) wouldn't really help. You may be able to estimate how long a request will take to complete for a given query based on past performance, but, as they say on Wall Street "past performance is no guarantee of future results".

Beyond that, if the end user is making a web request, standard HTTP protocols don't permit live updates to a web page. The HTTP request won't return until the response is 100% complete, so here again, remaining time would always be zero.

These days it's common to use AJAX to display progress indicators, but that only works if the initial request was made to an external asynchronous "engine" thread that can be polled by AJAX. And at that point, again. JDBC is still a sticking point for progress determination.

Adding things like JMS and web callbacks can add to complexity, but the fundamental bottlenecks remain the same. So the only real answer is that it'll be ready when it's ready, and if the user's patience is strained, then try and make the database request as quick and as precise as possible.




Thanks for your input. User is happy to go away from the computer if a request is going to take an hour or so but in some requests it could take few minutes and that's where I am wondering what would be a good way to let the user know the status.

I believe, if I log the user out or maybe refresh the page, from the UI, I could check the place where user files are supposed to exist, if it exists, I can have them download it and if it doesn't exist, I could have them wait with a message saying, check back again etc. Does this sounds reasonable?
 
Jack Tauson
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Junilu Lacar wrote:Telling users how long they have to wait is a double-edged sword. As it is, there could be a number of factors that are out of your control that determine how much time a SQL Query will actually take so giving an accurate wait time is iffy at best. If the time you give is too long, the user may decide that it's not worth waiting or get frustrated anyway. Either way, it's not a good user experience.

Since you're using an asynch call from the client side, you could just report "Still working on your request... will inform you when it's ready" and use a "toaster notification" or something like that to indicate when the results are ready. At that point, the user can go back to the screen and you can display the results of the query. This might require some caching of the results though and you might have to implement some timeout logic, say if the user does not view the results within 20 minutes of the notification then they'll have to run the query again. Or you could just find some way to cache the results on the client side through an AJAX callback.

These are just some ideas off the top of my head so if any of those sound like something you'd like to try, you'll have to do some experimentation yourself



Thanks. So, in order for me o figure out whether the task is finished or not so that based on that,  Icould show ""Still working on your request... will inform you when it's ready", from the UI should I keep checking at the location where files are supposed to be present? This might probably require me to log the user out of
HTML 5 storage sessionstorage maybe after few minutes and the nhave them log back in OR maybe I could just refresh the page, so that I could check via some way whether files exist at a particular location or not. Figuring out w/o refreshing the page I think might not be possible.
 
Tim Holloway
Bartender
Posts: 20580
121
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you've got a database request that can take an hour or so, you definitely have to do something different. If you tried to do that in a single HTTP request/response cycle, chances are that the user's browser would time out. Even if it didn't, the request would be tying up all sorts of network and server resources.

In a case like that, you need an "engine" to handle the request offline. Basically, your webapp spawns an engine thread in the servletContextListener startup method. DO NOT attempt to spawn a thread in a servlet. That's not only a violation of the J2EE standard, it's a good way to de-stabilize the webapp server! So the engine thread would have an associated synchronized queue object that defined requests - what kind of request it was, who it was for, and so forth, The user would invoke a servlet that constructed a request and placed it in the engine queue, returning a "working..." response to the user.

The engine would then take the request off the queue and run the request. By using a queue, multiple requests from multiple users can be stacked up if you like and it also handles the necessary cross-thread communication from the servlet to the engine.

When the engine has finished with the request, it marks the request as having completed. It may move it to some sort of completion queue or something.

Now it gets a little tricky. The original web request completed long ago, and a server cannot "push" a "completed" response. It can only respond to requests. So you have a servlet check for completion and return a yes/no response (this could be an AJAX control on a page or a full-page request). The check for completion may be done by the user manually or in response to a periodic client-side timer.

Now if it's a REALLY long request and/or very uncertain, you might have the server simply send an email or other asynchronous message to the user.

 
Jack Tauson
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:If you've got a database request that can take an hour or so, you definitely have to do something different. If you tried to do that in a single HTTP request/response cycle, chances are that the user's browser would time out.



Thanks for your inputs and yes,that's the reason I am using Java Message Service (JMS) and have two spring boot apps in place. First one contains the web app from where user could click on a download button and a message is sent to JMS queue. My another Springboot app consumes this and uploads the files to the server. Since I've this thing setup, I was thinking of doing something like this :

So, in order for me to figure out whether the task is finished or not so that based on that,I could show ""Still working on your request... will inform you when it's ready", from the UI should I keep checking at the location where files are supposed to be present? This might probably require me to log the user out of HTML 5 storage( sessionstorage maybe) after few minutes and then have them log back in OR maybe I could just refresh the page, so that I could check via some way whether files exist at a particular location or not. Figuring out w/o refreshing the page I think might not be possible.

So, does this sounds reasonable? Or would you recommend something else here?
 
Tim Holloway
Bartender
Posts: 20580
121
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You don't "log out" of "HTML storage". The HTTP protocol is not a time-sharing protocol. Instead it's a series of separate sessions logically connected by the HttpSession. The client is not connected to the server except for the length of time it takes to send an HTTP request down to the server and obtain a response from the server. Once the response (web page or AJAX response) has been returned, the client closes the network connection to the server and does not open it again until it makes another request to the server.

"Logging out" of a web application server, really just means that the HttpSession stored on the server is destroyed. Since the HttpSession contains the user's identity, that makes the user no longer identified to the server until they log back in again.

So the only way to check to see if an asynchronous process is complete when using HTTP is to periodically send requests to the web server and have the server return the completion status. You can do this manually (for example, by pressing the F5 key) or by putting in a metadata tag on the web page header that tells the browser to send another request after a set period of time, or by a timer for an AJAX component of the web page. It's up to you which option(s) you prefer.
 
Jack Tauson
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:You don't "log out" of "HTML storage". The HTTP protocol is not a time-sharing protocol. Instead it's a series of separate sessions logically connected by the HttpSession. The client is not connected to the server except for the length of time it takes to send an HTTP request down to the server and obtain a response from the server. Once the response (web page or AJAX response) has been returned, the client closes the network connection to the server and does not open it again until it makes another request to the server.

"Logging out" of a web application server, really just means that the HttpSession stored on the server is destroyed. Since the HttpSession contains the user's identity, that makes the user no longer identified to the server until they log back in again.

So the only way to check to see if an asynchronous process is complete when using HTTP is to periodically send requests to the web server and have the server return the completion status. You can do this manually (for example, by pressing the F5 key) or by putting in a metadata tag on the web page header that tells the browser to send another request after a set period of time, or by a timer for an AJAX component of the web page. It's up to you which option(s) you prefer.



Thanks for clarifications.

I see. So, I do have a table in the database which has a REQUEST_STATUS column. This column keeps track of the status, whether things are COMPLETED, INCOMPLETE, ERRONEOUS etc. I believe, I would need a web service to check the status of this column and from the UI, I could keep checking it again and again and as soon as I see COMPLETE, I could provide the files for download to the user. I believe this approach would fit with the timer for an Ajax component of the web page that you mentioned.

But one issue I might run with the above approach as far as checking the status of the column is concerned is as follows:

Q 1:In order to check the status of the query for download , whether it's COMPLETE, INCOMPLETE, ERRONEOUS I need a unique REQUEST_IDENTIFIER (a unique ID) and based on this ID only I can check the status of the request. The problem is that when a UI sends an Ajax request to a Spring boot controller, it's a POST request which calls a stored procedure. The stored procedure has one OUT parameter which gives me the unique REQUEST_IDENTIFIER. But since POST request doesn't send anything back to the UI (unlike GET request), I would not be able to know the REQUEST_IDENTIFIER which is needed in the UI in order to check the status of the query. Right now I am only making use of the returned REQUEST_IDENTIFIER at the Java level for sending it to the Broker (ActiveMQ) so that my consumer could grab it and do its stuff. Do you think I should not be able to proceed with this approach?


Q 2:That metadata approach you mentioned, is it going to use the Ajax call back as well? I am not much clear on this. Thanks

 
Saloon Keeper
Posts: 2476
317
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Jack Tauson wrote:Q 1:In order to check the status of the query for download , whether it's COMPLETE, INCOMPLETE, ERRONEOUS I need a unique REQUEST_IDENTIFIER (a unique ID) and based on this ID only I can check the status of the request. The problem is that when a UI sends an Ajax request to a Spring boot controller, it's a POST request which calls a stored procedure. The stored procedure has one OUT parameter which gives me the unique REQUEST_IDENTIFIER. But since POST request doesn't send anything back to the UI (unlike GET request), I would not be able to know the REQUEST_IDENTIFIER which is needed in the UI in order to check the status of the query. Right now I am only making use of the returned REQUEST_IDENTIFIER at the Java level for sending it to the Broker (ActiveMQ) so that my consumer could grab it and do its stuff. Do you think I should not be able to proceed with this approach?


The HTTP response should return a 202 Accepted status code and provide a link where the client can check the status of the request, and possibly other information such as an estimate of when the request might be completed, or a hint of how often the status check should be performed.
 
Jack Tauson
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ron McLeod wrote:

Jack Tauson wrote:Q 1:In order to check the status of the query for download , whether it's COMPLETE, INCOMPLETE, ERRONEOUS I need a unique REQUEST_IDENTIFIER (a unique ID) and based on this ID only I can check the status of the request. The problem is that when a UI sends an Ajax request to a Spring boot controller, it's a POST request which calls a stored procedure. The stored procedure has one OUT parameter which gives me the unique REQUEST_IDENTIFIER. But since POST request doesn't send anything back to the UI (unlike GET request), I would not be able to know the REQUEST_IDENTIFIER which is needed in the UI in order to check the status of the query. Right now I am only making use of the returned REQUEST_IDENTIFIER at the Java level for sending it to the Broker (ActiveMQ) so that my consumer could grab it and do its stuff. Do you think I should not be able to proceed with this approach?


The HTTP response should return a 202 Accepted status code and provide a link where the client can check the status of the request, and possibly other information such as an estimate of when the request might be completed, or a hint of how often the status check should be performed.



Thanks!

1) When you say that it should return 202 code, you mean ,in my web service,I should hard code this code and return this as a JSON response, right?

2) Even after returning this code, this would only be visible in the browser and not to the user unless I decide to do checks based on the status in the UI,right?

3)Is it okay if I return REQUEST_IDENTIFIER as well (based on what I described in my previous post) in my request considering the fact that it's a POST request?
 
Can you really tell me that we aren't dealing with suspicious baked goods? And then there is this tiny ad:
global solutions you can do at home or in your backyard
https://www.kickstarter.com/projects/paulwheaton/better-world-boo
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!