title: Optimize the performance of an API that returns 200K rows as Json
body: <p>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).</p>
  <p>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.</p>
  <p>The API returns the data in Json format as below</p>
  <pre><code>{
  
     &quot;MyData&quot;: [
          {
              &quot;ID&quot;: 1,
              &quot;BillRefNo&quot;: 10001995,
              &quot;Barcode&quot;: 1000189500001,
              &quot;BillRefCID&quot;: 915,
              &quot;IsDuplicate&quot;: 0,
              &quot;IsDeclined&quot;: 0,
              &quot;IsAuditRequest&quot;: 0,
              &quot;IsRevRequest&quot;: 0,
              &quot;NoOfProducts&quot;: 0,
              &quot;IsCompleted&quot;: 0
          }
  
  }
  </code></pre>
  <p>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.</p>
  <p>The size of the response is 28.4MB.</p>
  <p>Now I want to optimize this , especially the response time more than the storage. Although it's important too.</p>
  <p>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.</p>
  <p>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.</p>
  <ol>
  <li><p>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.</p>
  </li>
  <li><p>Convert JSON to CSV in API before returning it to client. (I'm not
  sure if     this is doable or not).</p>
  </li>
  <li><p>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.</p>
  </li>
  <li><p>Changing the JSON key names. For example from     <code>BillRefNo</code> change
  it to <code>1</code>. By this I will reduce
  the number of text returned and it might improve the
  performance (Not sure)</p>
  </li>
  </ol>
  <p>I have been thinking about this for weeks. Any idea , suggestion , thoughts would be highly appriciated!</p>