1

I have a large amount of student's exam data which I process to display student results. Although it seems to work but it takes significantly longer time to display result. The time is increasing every passing day.

  1. I have a question table (tblQuestions) to store questions and correct answer.
  2. I have a Test Instance table (tblTestInstance) which stores test instance for each test given by student.
  3. The largest table I have is tblTestDetails which stores question IDs and option selected by student.

I bind GridView with student table and on GridView's Databound event, for each student I calculate correct answer for each question attempted by student. I calculate data inside a data table.

Problem is: It takes several minutes to process result.

Required Suggestion: I want to bind gridview, row by row (each row once the single result is ready) instead of loading all calculated results at once.

What I've tried: I have used Update Panel, with timer control. On interval of few seconds I tried reloading my datatable, but it still displays result all together.

Any suggestion please. I am using ASP.NET Web Forms.

Edit: I want to display result row by row. I mean when the first row is ready it should display to user's screen. This would not improve the performance I know but I want the user to see something on screen rather than waiting for all rows to be ready.

For example if a class has 1000 students and they have performed a test with 150 or more questions each, then evaluating answers takes longer time.

The approach I am using currently I pull students from database, and bind the grid view For each student I pull the test instance and then question set For each question I evaluate answers using question's table After I calculate marks, I display them.

askBittu
  • 38
  • 8
  • i fail to see how row by row processing is going to gain speed here - in near EVERY case row by row processing is MUCH slower then using the data engine and pulling data with sql queries. I mean, a user can't view or see more then 1 page of data - how many rows are you pulling here? A page of data should appear near instant - even for hairy ball super ugly sql - such pages appear instant. Some detail here does not make sense. I mean how many rows of data is being pulled here? I mean 100 rows with the world's worst sql will still occur well under one second. – Albert D. Kallal May 03 '21 at 17:35
  • On data item bound IS IN FACT row by row processing. Some big detail is missing. Row by row processing or using item databound amounts to the same thing. The amount of data here should not matter - but will matter is the number of separate sql quires you execute - that has a high cost - often in most cases a larger number of separate sql queries is far more expensive then the amount of data being pulled. This suggests that way too much "new" from scratch sql queries are being executed in the on data bound event. Pull the data one time into a datatable and persist that for data bound event. – Albert D. Kallal May 03 '21 at 17:41
  • 1
    There are not many SQL queries are being executed. To generalize, I am pulling students using SELECT. For each student I am fetching question sets. Then each question is being evaluated against the correct answer, using the db table. If a class has 1000 students they have performed tests with 150 questions each then the data bound event will produce slower result. I want instead of waiting for result all together, a one by one approach would be more helpful. User will at least see something instead of blank page for 10 minutes. – askBittu May 03 '21 at 18:01
  • 1
    Perhaps better matching routines for the sql against the answers would help? Perhaps a set of sub-quires in the view that does this matching? I suppose you could display the students and then select one row to drill down. I still don't see that 150 matches per student would be an issue unless the sql queries can't match up the Q and A's., and row by row processing is occurring here. If those questions + answer matching can't be done with sql then this will be slow. – Albert D. Kallal May 03 '21 at 18:38
  • 1
    To summarize @AlbertD.Kallal, you should be doing one, and only one, database call if possible, using stored procs or similar. If you are doing subsequent calls to the db for each initial row returned, no wonder this is taking minutes. – Jon P May 04 '21 at 01:22
  • I much agree. I don't think these data sets are all that large. I done the most hairy ball queries with dozens of sub queries - ugly as could be - absolute horrid sql in a store proc - and multiple CTE's nested to get the desired result. And this was against much larger datasets then outlined here - and response was still under 1 second. ugly as could be? yes, but it still ran fast - so much so to this day I am stunned. So, I agree - this problem needs some work. It not clear how the matching up is occurring - but limiting the number of separate queries fired is the key concept here. – Albert D. Kallal May 04 '21 at 05:48
  • Ok. I am now planning to move my entire logic inside single SP and I hope to see a significant change in performance. – askBittu May 04 '21 at 07:43

1 Answers1

1

One trick it to flush the data as they come out using the Response.Flush();

here is an example

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField HeaderText="Item" >
            <ItemTemplate>
                <%#cRenderLine(Container.DataItem)%>
                <% 
                    // show every 10 lines what I have
                    if (++cLine % 10 == 0)
                    {
                        Response.Flush(); 
                        // simulate the delay here
                        // System.Threading.Thread.Sleep(1000);
                    }
                %>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

and on code behind.

public int cLine = 0;

List<int> oMainIds = new List<int>();

protected void Page_Load(object sender, EventArgs e)
{
    for (int i = 0; i < 10000; i++)
    {
        oMainIds.Add(i);
    }

    GridView1.DataSource = oMainIds;
    GridView1.DataBind();

}

protected string cRenderLine(object oItem)
{
    return oItem.ToString();
}

Other way is to use a virtual ListView, means show the data as you scroll on page - I have give an example on this answer -> Virtual Listview for ASP.net?

Aristos
  • 66,005
  • 16
  • 114
  • 150