Retrieve huge amount of data in Web API

Question

I have a web api which allows the user to retrieve data real time and by batch. The problem however is that I have a data which has over 30 million of records which causes a bottleneck in a by batch request. I use paging in my get method which returns a default of 10 records per api request but still the 10 records take time in retrieving because of the bulk pull of data.

Here is the sample of my get method :

 public async Task<IHttpActionResult> Get(int pageno = 1, int pagesize = 10)
    {
        int skip = (pageno - 1) * pagesize;
        int total = db.webapi_customer_charges.Count();
        var cc = await db.webapi_customer_charges
            .OrderBy(c => c.hospital_number)
            .Skip(skip)
            .Take(pagesize)
            .ToListAsync();

        return Ok(new Paging<webapi_customer_charges>(cc, pageno, pagesize, total));
    }

Is there a way or workaround or like best practice when it comes to retrieval of huge amount of data ? Thank you.

is column hospital_number a primary key column or is it indexed? — Jehof, Mar 10 '16 at 07:25
Neither. The column hospital number is just used for filtering. — Qwerty, Mar 10 '16 at 07:34
Why not using [ODATA in WEB API](http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api) ? — sepehr, Mar 10 '16 at 16:36

score 2 · Accepted Answer · edited May 23 '17 at 12:31

Not sure how you can do it with EF, since I assume it retrieves the whole set before you can skip or take, but I think it would be faster if you called a stored proc on Sql Server side and just pass in the min and max row numbers. That way you would only get the amount of data you need from the server on each call.

Here is a link to how you can call a stored proc with EF. If you don't find a better solution give it try it should be quite simple to make a select and use the ROW_NUMBER() in Sql to filter based on your input params.

Retrieve huge amount of data in Web API

1 Answers1