1

I have a Spring based java webservice which is returning JSON response. The problem is that the JSON response takes long time because the SQL involved is querying huge chunk of data and it takes 4-5 minutes. I am wondering if it's possible to store the data in a flat file somewhere using Javascript so that next time if I have to use same data, I could use the file present on the server. Please let me know if anything like this is possible or not?

Added more clarifications based on the discussion in the comments and answers:

1) The size of data I have accoutered thus far is 22k which seems to be taking 4-5 minutes to respond via Java webservice.

2) I don't have any control over the SQL query or database side since I just got the SQL query to use in my web service.So I am trying to manage this at my end.

3) Eventually, user should be able to download the data. So, I am planning to display these 22k records in a table and they will download it.

Thanks everyone. Let me know if I could answer more questions.

  • 1
    Theoretically it would be possible, but highly impractical with dealing with all the caching logic and the such – TurtleKwitty Oct 15 '18 at 19:23
  • 1
    It sounds like you answered your own question. Why wouldn't that be possible? – Greg Burghardt Oct 15 '18 at 19:27
  • Personally, I would take a step back and look at what data is being retrieved and why. Is this for a report? You might even need to redesign the database. – Dan Wilson Oct 15 '18 at 19:28
  • @DanWilson: Redesigning the database should be your last option. There is nothing wrong with caching the output of a report. And if you need to analyze live data, there are solutions for that too, which do not involve redesigning something as foundational as a database. – Greg Burghardt Oct 15 '18 at 19:32
  • 2
    @GregBurghardt: unless the database was not “designed” in the first place. In many cases, denormalizing the data could make all the difference. Although, to be frank, in the current case, my first reflex would be to check for the missing indexes. – Arseni Mourzenko Oct 15 '18 at 19:40
4

1. Profiling

In order to be able to identify the bottleneck, you need to profile the service. You do that with an application called a profiler which samples the program and gives you the data about how long different parts took or how much memory was used, and what pieces of code were using it.

There are profiling tools for SQL too, allowing to spot the queries which take too long. If you have only one query, you don't need to use profiling, and, instead, should rely on the execution plan. The execution plan shows how a query was interpreted and how it will actually execute. Those details are precious when it comes to optimizing slow queries.

2. Indexes

There may be nothing wrong with your query. The execution plan may show that it's absolutely straightforward, and there is nothing you can do about the query itself.

This doesn't mean you cannot modify the database schema itself. A primary tool for that is an index. An index allows a database to quickly identify which rows match a given criteria. For instance, if you're searching a lot for the products which are available and are in a specific price range, creating an index covering the columns availability and price could make those queries much, much faster.

Indexes, however, come at a price. They make your insertions and updates slower. Don't put indexes everywhere: you'll end up with a terribly slow database.

3. Normalization, denormalization and OLAP cubes

Depending on the structure of your data, you may need to normalize or denormalize your data in order to reduce the number of joins or the number of reads. Many cases are good candidates for a star schema, which sometimes provides a tremendous optimization at a cost of data integrity.

Since you provided no information whatsoever about the actual query and the actual schema, it is impossible to tell which ones of the changes should you perform. In all cases, if you don't know them, learn them. They are useful.

4. Caching

If it is acceptable to have a slightly outdated data, use caching. Caching can be implemented at the level of the service itself, as well as at the client'. Doing it on server side means multiple clients will share the benefit of the same cache. Doing it on the client side means the client won't even have to perform the request in the first place. Usually, you'll be implementing cache on both sides to have the benefits of both worlds.

Your idea with a flat file goes towards the client caching technique. However, don't do that. Instead, rely on caching mechanisms of HTTP, which would be more reliable and probably more robust as well, and will be supported by most browsers, which means much less code for you.

5. Sharding

You told us that the database is “querying huge chunk of data,” but you haven't told what you mean by “huge.” If we're talking about the amount such as 10 TB, the bottleneck may be in the hardware (including the connection speed between the database and the NAS/SAN).

In this case, you may have to distribute the database on multiple machines with multiple NAS/SAN devices using a technique called sharding. It consists of telling that specific rows of the same table will be hosted in multiple members of the cluster based on a specific criteria (such as a value of a row). This allows to scale a database just by adding more machines, while keeping the performance at a high level.

Similarly, a slow computation can be distributed over multiple machines by using the map reduce technique (if the profiler shows you that the bottleneck comes from your code rather than the database query).

6. Progressive download and pagination

JimmyJames made a valuable suggestion in his comment, and I would like to expand on this and on the recent edit of the question.

  1. Flush data as soon as it is ready, without waiting for all the data to be returned from the database. Since you're talking about a few thousands of row, there is no need to buffer all the rows in memory. Instead, as soon as the cursor gets the first row, write it to the output and flush the output to the client.

    Obviously, this would require a bit more than calling the JSON serialization of the web framework. Either change the format, so that each row would fill one line (CSV is a perfectly fine choice for that), or flush the [ character on the first line, then JSON-serialize every row, append a comma, and then where there are no rows left, end by a ]. I would strongly advise to use the first approach rather than the second: first, generating JSON by hand is always error prone, and parsing it in a similar way on the client side won't be intuitive.

  2. Use either pagination or search, i.e. return only a subset of the rows.

    The cases where the users would actually be looking at twenty thousand rows are pretty rare. If they are exploring the data from most to least relevant, pagination would help. They will skim the first page, probably go look at the second or the third one. If they would be refining the table, send the fifteen most relevant rows, and wait for them to enter some search criteria.

  • Thanks. Your 4th point closely matches with what I am trying to accomplish. I believe, using HTTP caching mechanisms won't require flat file usage. And I believe it's okay to have silghtly outdated data. So, using caching mechanisms of HTTP for 22k records or maybe more for other queries, would be good way to handle the data? I mean would the browser be able to hold such a big amount of data so that I won't have to call the webservice again and again? Please correct me if I didn't understand something here. – John Oct 16 '18 at 13:26
  • 22k records of what size? Let's imagine there are 500 characters in a record on average. This leads to a payload of approximately 11 MB. Nothing exceptional here. Unless you're using a ten years old mobile phone... wait... no, maybe it would be able to handle it just fine. – Arseni Mourzenko Oct 16 '18 at 17:32
  • Thanks again. It looks like, according to you, using HTTP mechanisms sounds like an efficient approach based on my scenario. Could you tell me if there is an upper limit on the size that I should keep in mind? I mean upper size limit till where HTTP mechanisms could be used?P.S. Not sure how can I determine the size of the 22k records using the select count(*) query because I have access to read only database and I can't see tables. – John Oct 16 '18 at 18:52
  • Good question. It appears that it's very difficult to find any relevant (and recent) information on that. For Firefox, it's easy: 31.25 MB by default. But Chrome?... No idea. There is this discussion from 2012 which is much less optimistic than my previous comment. Not sure if things have changed yet, but I'm pretty sure that I overestimated caching capabilities. What about trying yourself and sharing your discoveries? – Arseni Mourzenko Oct 16 '18 at 19:56
  • For what it's worth, testing AJAX requests of different size reveals that they are cached up to 26214400 bytes, that is 25 MB in Chromium 69 with default settings on a desktop PC. – Arseni Mourzenko Oct 16 '18 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.