locked
Facing peformance issue : Need suggestion on saving more data in viewstate and writing an excel file in ASP.NET Application RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User1094269964 posted

    Hi Team,

    We have developed an ASP.NET application using Telerik RadGrid.

    The following are the Functionalities we have implemented and which gives performance issues

    Issue 1 : Grid Load Performance issue with current code:-

    1. We are binding a RadGrid with more than 20 columns with all the data (DataSet) from the database (SELECT * FROM Table) and all the records (approximately the record count will be 2500 and sometimes less records will come and some times more than 2500 records will come) are saved in Viewstate to avoid database call again and again
    2. While doing the pagination we are binding the records in the viewstate back to the grid
    3. While doing the Filtering and sorting we are using the records in the ViewState and binding it to the grid

    The performance issue which we are facing here is while doing pagination the grid takes long time to bind.

    Need help,suggestions and recommendation from the forum experts on this

    1. how to improve the performance in the above approach we currently have
    2. Is it good practices to have more records in the viewstate just for the sake of avoid the database calls

    Issue 2 : Excel write and excel upload performance issue with current code:-

    1. We excel upload and excel download Functionalities in the Asp.net application
    2. During Excel upload will read all the records in the excel and it will  be in in memory DataTable. The records in the datatable will be compared with records in the database to see whether upload records are differ from records in the database. If there are changes then we will modify the DataTable structure by adding additional column IsModified and then we will bind the all the records to the grid (All the records in the database + Updated records (some of the column values are modified) in the excel which are not same as records in the database . For example the below are the sample. But our application will have more number of columns
      Actual records in the database
      ProductId ProductName UnitPrice
      1 A 100
      2 B 200
      3 C 300
      4 D 400
      5 E 500
      Modified records in the excel
      ProductId ProductName UnitPrice
      4 D 900
      5 E 700
      InMemory DataTable records to
      be bind with Grid
      ProductId ProductName UnitPrice IsModified
      1 A 100 NULL
      2 B 200 NULL
      3 C 300 NULL
      4 D 900 TRUE
      5 E 700 TRUE
    3. For excel download we are fetching all the records in the ASP.NET and we are using OpenXML and we are writing an excel file. The record count will be more than 2500 and column count will be more than 20 

    Issue : Now the issue is excel upload and download takes long time and some times we are getting unhandled exception and httphandler timeout exception

    Need help,suggestions and recommendation from the forum experts on this

    1. Is it a better practice to read and write excel which has more number of records in ASP.NET Application
    2. Is it better practice to do the data comparison in the ASP.NET application which is explained in the section 2

    Please help  us..

    we are ready to change the approach...

    Wednesday, January 21, 2015 2:48 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-821857111 posted

    bsurendiran

    Is it good practices to have more records in the viewstate just for the sake of avoid the database calls

    Absolutely not. First, you are extracting way more data than you need, and adding that to every request in an encrypted format. It will be a performance killer. A database call that gets just the records you need will not kill performance. 

    Depending on your SQL Server version, use either ROW_NUMBER or OFFSET and FETCH to only bring back the data you need for the grid

    SQL Server 2012 Offset And Fetch:

    SELECT * FROM Products ORDER BY ProductId OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

    SQL Server 2005+ ROW_NUMBER:

    ;WITH x AS (SELECT *, ROW_NUMBER()  OVER (ORDER BY ProductID) AS rownumber FROM Products)
SELECT * FROM x WHERE rownumber BETWEEN 21 AND 30

    As to your Excel issue, I would read the content into a temporary table in SQL Server (using OPENROWSET or similar, no need for DataTables at all) and then perform the comparisons using SQL Server rather than .NET. You can find all the records that have not changed by doing a simple join.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 21, 2015 3:17 AM