How to handle amount of data in Web API?

user19928919 0
Viewed 84

I am new to web api and I have some problem with my web api. I have created an web api with sql server, but i did not used entity framwork, it is kind of old way to connect sql server database to web api.

The problem for now is my sql server database have more than ten thousand records and when i run my web api in google chrome and it showed out of memory in google chrome. I do not know why it will out of memory, those records are less than 200mb, it should not out of memory. I have though about my codes are wrong in somewhere, does anyone know how to solve it?

web api codes with data table:

public SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString);

        [HttpGet]
        public IHttpActionResult GetAll()
        {
            List<webapi> web = new List<webapi>();
            SqlCommand cmd = new SqlCommand("SELECT UserID, Name, Address, Mobile, Birthday From tbluser", connection);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            sda.Fill(table);
            foreach(DataRow read in table.Rows)
            {
                web.Add(new webapi
                {
                    UserId = Convert.ToString(read[0]),
                    Name = Convert.ToString(read[1]),
                    Address = Convert.ToString(read[2]),
                    Mobile = (read[3] != DBNull.Value) ? Convert.ToInt32(read[3]) : 0,
                    Birthday = (read[4] != DBNull.Value) ? Convert.ToDateTime(read[4]) : (DateTime?)null
                });
            }
            return Ok(web);
        }

web api codes with data reader:

public SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString);

[HttpGet]
        public IHttpActionResult GetAll()
        {
            List<webapi> web = new List<webapi>();
            SqlCommand cmd = new SqlCommand("SELECT UserID, Name, Address, Mobile, Birthday From tbluser", connection);
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                web.Add(new webapi()
                {
                    UserId = sdr.GetString(0),
                    Name = sdr.GetString(1),
                    Address = sdr.GetString(2),
                    Mobile = (sdr.GetValue(3) != DBNull.Value) ? Convert.ToInt32(sdr.GetValue(3)) : 0,
                    birthday = (sdr.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(sdr.GetValue(4)) : (DateTime?)null
                });
            }
            return Ok(web);
        }

I also made two version of get method which used data table and data reader to get data.

Class:

public class webapi
    {

        public string UserId { get; set; }

        public string Name { get; set; }

        public string Address { get; set; }

        public int Mobile { get; set; }

        public DateTime? Birthday { get; set; }
    }
webapi c#
2 Answers

I do not know why it will out of memory, those records are less than 200mb, it should not out of memory.

You don't pay this price only once. Especially locally. Here's what is happening:

Your database loads the data from disk to memory. This likely is streamed to the C# server. Either way the data needs to be serialized, send via sockets, read on the C# server side and deserialized into data. Since C# memory is managed, the data stays in memory until the garbage collector clears it. And we don't know when and how it will happen. It is likely that at least each separate webapi object will stay in memory for some unspecified time. Also the C# runtime rarely releases the memory back to OS. Another thing is that the database typically keep objects in a compact way, while C# class layout is less compact to allow better performance. It is likely that on C# side they weight a lot more than 200mb.

Next you send all those objects to the browser. This means data serialization, which by default is Json. So you take all those 200mb+ and now you turn that into Json string. The json serialization is really inefficient compared to binary database serialization formats. This can be many, many times more, depending on the data patterns. For example every Json object comes with field names.

You then send that data over the wire and read it on the browser side. Since Json is not streamable format, it has to be read entirely before it can be deserialized. It means that the browser has to keep buffering the data in memory, meaning you pay the full price at least on the browser side (possibly on the server's side as well, not sure). And then you pay for deserialization in js, and then you pay for generating and reneding html.

By rough calculations, if everything run locally, we get:

  1. say 250mb after loading in C#
  2. say 750mb after serializing into json
  3. 250mb after deserialization in browser
  4. at least 250mb after rendering (probably many times more if fancy html + styling)

All in all you get 1.5 Gb (1.25 Gb on the client side), probably much more.

does anyone know how to solve it?

You could fix your serialization and deserialization to minimize all the prices you pay.

But the proper solution is to send data in small chunks. You really don't want a user to go through thousands of records, that is not user friendly. And risky (e.g. denial of service attack).

You really should implement pagination. Meaning your endpoint should accept "page" parameter and then you should send chunks of data in [page*page_size, (page+1)*page_size] range.

freakish0

In your case, I think It is better to use OFFSET to fetch a huge amount of data into chunks from the database.

So try to use LIMIT OFFSET instead of selecting all rows, that could solve your issue.

Ali Zedan0
Related