Precompute the query time? Is it a possibility?

One of my SQL query is taking 6-7 minutes to return 25,000 records. I don’t have any control over the SQL query since someone gave it to me so I can’t optimize it. This happens when a user clicks on a table and then a Ajax call is fired up.

  1. Is there a way to pre-compute this step and let the user know that this call is going to take time. I mean, I want this to happen when a user clicks on some row of a table and as soon as the Ajax call is fired up?

  2. Next time a user visits the same page, would it be possible to store the records on the server somewhere so that user won’t have to wait same amount of time and can access the Ajax result as soon as it’s fired up?

Please share your thoughts if anyone has done this before or know how to best approach this issue.

Thanks

Slow queries are often a sign of missing database indexes …

1 Like

That’s true. It’s a SELECT * query and I am pretty sure it’s missing indexs and many more things.

Then

  1. not that I’m aware of. And I doubt that it’s possible.
  2. employ any persistence layer (database, other database, filesystem, …) that you’re comfortable with.

Thanks for your response. Could you elaborate on #2. For example, if I want to use filesystem. I mean any reference to any online doc would be helpful. I haven’t done this before.

for storing something in the filesystem you need a programming language of your choice that is installed on the server. You’ll find its filesystem functions it the documentation for the language.

One should never use SELECT * because it forces the database server to access another table to get the column names for proper selection. Plus, unless you really need every single column, it’s a waste of bandwidth and CPU.

Perhaps re-indexing the table might help. Or, you could post the query here and see if any of us could optimize it, for you.

V/r,

^ _ ^

I see. Thanks

I have the SQL query like this which I could run in SQL Developer which is connected to Oracle database :

select * from table(MyDatabaseSchema.get_employee_custom_routine_vw (
'username', 
'\path1\Path2:MyProject.TestDatabase\'));

I have read-only access to this schema so in SQL developer, I can’t see what tables are there when I click on the plus symbol next to Tables. Is there a way to view some details about the SQL behind get_employee_custom_routine_vw() Or any other way to check details about it? Thanks

Okay, say for example, I am using server side programming language (PHP) for dealing with this huge JSON response. So, do I need my AJAX call return something other than JSON?

Also, is it possible to use HTML5 storage to achieve same purpose?
Thanks

I think something could be put together. It would be some work which may have something to do with why “loading” gifs are so common.

  • code to put the results into localStorage / IndexedDB
  • code to determine if the cached results existed
  • code to determine if the table had changed
  • code to use either the cached results or run the query

AFAIK, saving results on a user’s computer requires them giving permission to the script to do so which may or may not put off some users if they don’t understand the what and why.

I’ve worked, a bit, with localStorage, and I’ve never had a browser ask me if it’s okay to store information in localStorage. Which browser(s) ask for permission?

V/r,

^ _ ^

I use several browsers so I’m not sure where I’ve seen it. But now that you mention it and I think about it, I think it was under a setting. eg. “ask when … default, always”.

Odd. I just looked in FireFox settings (v52.9), and under “Advanced”, I found: Tell me when a website asks to store data for offline use.

But even with that on, when putting things like customer contact information into localStorage, even with this box checked (which it is, by default), I am not prompted to give or deny permission. :roll_eyes:

V/r,

^ _ ^

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.