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