Optimize the performance of an API that returns 200K rows as Json

Viewed 709

Using asp.net Web API c# I'm developing an API that returns between 100,000 - 200,000 rows/records from a table using SQL Server 2017 to the client (Android application).

Client will need to fetch this data once or maximum twice a day based on some condition and store it on the local db SQLite.

The API returns the data in Json format as below

{

   "MyData": [
        {
            "ID": 1,
            "BillRefNo": 10001995,
            "Barcode": 1000189500001,
            "BillRefCID": 915,
            "IsDuplicate": 0,
            "IsDeclined": 0,
            "IsAuditRequest": 0,
            "IsRevRequest": 0,
            "NoOfProducts": 0,
            "IsCompleted": 0
        }

}

Now when I hit the API using postman, It takes sometimes from 4-7 minutes to retrieve the result. I think it's important to mention that when I debug the API it returns the result for example in 3 minutes and in postman itself it takes 1-2 minutes to display this huge data.

The size of the response is 28.4MB.

Now I want to optimize this , especially the response time more than the storage. Although it's important too.

My biggest concern is when different/multiple clients will hit this API. I believe the time will increase (I'm not sure). And I don't know if the server where the API is deployed will be affected or not.

Now I did some research on how to optimize this. But I need an expert opinion on which one is the most suitable and most efficient and will likely improve it.

  1. Compressing the data in API before returning it to client. But I'm not sure how long it will take for the client to decompress the data (Extra step) and then store this decompressed data in local db.

  2. Convert JSON to CSV in API before returning it to client. (I'm not sure if this is doable or not).

  3. From SQL Server, Export the table as CSV file(Not sure if this is doable ) and then read this file from the client side.I'm not sure how fast the reading process from client side will be.

  4. Changing the JSON key names. For example from BillRefNo change it to 1. By this I will reduce the number of text returned and it might improve the performance (Not sure)

I have been thinking about this for weeks. Any idea , suggestion , thoughts would be highly appriciated!

0 Answers
Related