Click here to Skip to main content
15,891,839 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am building a sample mvc application. I am trying to export search results to excel. I stored my search results in a session and export that session list to excel. It was working fine when I was playing around with small database that has only 2000 rows. Now I have around 300K rows in my database. Searching the results and exporting to excel still working fine. But my application slowed down drastically.

When I comment out below code my application runs fine again, as soon as I uncomment it will slowed down.

C#
//Session["SearchResults"] = aPPET.ToList<APPET>();


What would be the best way to do it when there is large database?

I read post about closedxml, but not really sure how to implement it. Do I need to create store procedure if I go that route? Or is there any better way.

Please suggests.

Below is my controller codes:

What I have tried:

C#
public class APPET1Controller : Controller
{
   private APContext db = new APContext();

// GET: APPET1
public ActionResult Index(string search, string cagecode, string sortBy, 
                          int? page, string docNumber, string remark, string status)
{
    APPETViewModel viewModel = new APPETViewModel();

    var aPPET1 = db.APPET1.Include(t =>T.APPETMedia)
                                .Include(t => t.Status)
                                .Include(t => t.APPETCCode)
                                .Include(t => t.APPETDType);
             ............some more codes..............

    DateTime searchDate;
    if (!String.IsNullOrEmpty(search))
    {
        bool isDateSearch = DateTime.TryParse(search, out searchDate);
        if (isDateSearch)
        {
            aPPET1 = aPPET1.Where(s => s.Date_Received == searchDate);
        }

        else
        {
            aPPET1 = aPPET1.Where(t.Doc_Number.Contains(search)
                    || t.Status.Status1.Contains(search)
                    || t.Remark.Contains(search)
                    || t.CCode.Contains(search));
         ..............some more codes..............
           viewModel.Search = search;
        }

    }
  var stats = db.Status.Select(s => s.Status1);
    viewModel.Statuses = new SelectList(stats);
    {

      if (!String.IsNullOrEmpty(status))
      {
        aPPET1 = aPPET1.Where(t => t.Status.Status1.Contains(status));
      }
    }
    if (!String.IsNullOrEmpty(docNumber))
    {   
        aPPET1 = aPPET1.Where(t => t.Doc_Number.Contains(docNumber));
    }
    if (!String.IsNullOrEmpty(remark))
    {
        aPPET1 = aPPET1.Where(t => t.Remark.Contains(remark));
    }
 ............some more codes.........

    Session["SearchResults"] = aPPET.ToList<APPET>();
    return View(viewModel);

}

public ActionResult ExportToExcel()
        {
var AppetList= (List<APPET1>)Session["SearchResults"];
            var result = from r in AppetList
                         select new
                         {
                             DocNumber = r.DocNum,
                             Status = r.Status.Status1,
                             Remark = r.Remark
                       -----some more codes------

                         };
            GridView gv = new GridView();
            gv.DataSource = result.ToList();
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=TaciList.xls");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();

            return RedirectToAction("Index");
Posted
Updated 24-May-18 4:50am
v2
Comments
F-ES Sitecore 24-May-18 6:14am    
So you want us to help you speed up writing to Excel when you haven't told us *how* you are writing to Excel, and you post a chunk of code that doesn't include the line you have a problem with?

Regardless, writing that amount of data is always going to be slow. There are some things you could probably do to help, but it's never going to be fast. The fact that you are storing the data in a session implies you are getting the code in one action and saving to Excel in another. If that's so it's a bad idea. All that data just eats up memory, so have the page that writes to Excel access the data itself. Also if it's a lot of data don't use ToList to convert it to a list first, use IEnumerable to go through the data line by line. Again it's not going to make it "fast" but it's going to use less resources than ToList which is effectively forcing all of the data into memory in one go.
CHill60 24-May-18 11:17am    
OP is trying to respond to your comment but doesn't get the concept of "Reply" :)
phatlee 24-May-18 10:54am    
@F-ES Sitecore, exporting to excel is fine once it is loaded. Loading and searching takes way too long when I tried to store it in session. So, if I have to store the result from my search temporarily so that I can export it to excel if needed how should I do it. Now, i know I can't use session. Sorry, english is my second language and I am new to programming so if you don't understand, I can explain again. please let me know. thanks.
F-ES Sitecore 24-May-18 11:43am    
You need something like

foreach(var result in aPPET)
{
// export result to Excel
}

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900