Database and LINQ - Handle Huge Volume Of Data

Question

Hi alll..

How to handle huge volume of data ( more than 10 lakhs records) ??

Requirement - In database (either Oracle or SQL Server ), I want a facility such that more than 5 lakhs records be inserted , and can be displayed in gridview.

Loading huge amount of records is causing SQL Server Error - "Time Expired".

One option is database-level paging using Row_number() function with page index and page size.

But, one more requirement is - on button click, I want all these records to be exported to excel..

How do I do this ?

Also, How do we handle this large volume of data in LINQ ??

PLease guide

Rahul Chavan · Answer

WCF have improved performance compared to old services. For expert I will suggest to use to use Bulk copy program(BCP). It will export data to CSV.

Riddhi Valecha · Answer

Also... I want to know - How WCF Services help in running the asp.net website run smoothly ?? Is this true that WCF Services reduce the time ??

Ghouse Hussain · Answer

Write linque query public List Getemp(emp pp) { var x=from n in db.emps select n return x.tolist (); } and call Grid.Datasource=x.tolist(); Grid.Databind(); use this methode even u will have Bulk of data also it wil controll Beacause Here no dataAdaptor & DataReader

Riddhi Valecha · Answer

Hi... For export to excel  on one button click event I have to export 3 lakhs records at a time. This is taking time (2-3mins) and still giving error -Time Expired. How do I do this ? Also I want solutions for all 3 ways of handling the huge amount of data...

Manas Mohapatra · Answer

From 3 options what are going to implement. Not like you will implement all concepts. Follow one and try to implement that one only..

Riddhi Valecha · Answer

1. Use paging concept. Load 50 records per page it will easy to maintain. Remarks - Yes I have done database level paging (using Row_Number()  Page Index and Page Size). I want to know how to do paging using LINQ ?? 2. Use lazy loading concept. Means on scroll down browser it loads the records. Is this using JQuery Scrolling text ?? 3. Export the 3 lakh records to an excel sheet to see it. And perform your action. I need this concept using LINQ - Fetch all 3 lakhs records in LINQ Query and export in excel. In excel I have used the following code - #region ExportToExcel public override void VerifyRenderingInServerForm(Control ctrl) { return; } private string GetString(object o) { if (o == null) return ; return o.ToString(); } private void ExportToExcel() { try { table = new DataTable(); table = (DataTable)Session[Data]; if (table != null) { grdv = new GridView(); grdv.DataSource = table; grdv.DataBind(); Response.Clear(); Response.Buffer = true; Response.ContentType = application/vnd.ms-excel; Response.AddHeader(content-disposition attachment;filename=Employee Report.xls); Response.Charset = ; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHTMLTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); grdv.RenderControl(oHTMLTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } } catch (Exception err) { err.Message.ToString(); } } #endregion

Manas Mohapatra · Answer

I am not agree to display 3 lakh records in gridview due to following reasons: 1. If you will render 3 lakh record inside gridview it will take time to display. Sometimes your browser would hang up(not responding. 2. You may get server time out issue to fetch so many records. 3. User has to 3-4 minutes to see the records which feels irritate them. So in order to resolve problem follow below solutions: 1. Use paging concept. Load 50 records per page it will easy to maintain. 2. Use lazy loading concept. Means on scroll down browser it loads the records. 3. Export the 3 lakh records to an excel sheet to see it. And perform your action.