data on repeater takes too long to load

Question

I have a page that runs reports on sales for a time period. When I run it for a month with around 88 transactions, its take a while but I can live with it for now. Problem is when I try run the page for a year or over 3 months, its takes around 30minutes then goes to an error page saying "The cms.kics.com.au isnt working.. It didnt send any data...err_empty_response". ] First thing comes to mind is there is too much data to return and its timing out on the request. I have increased the execution time in the web.config and also the page timeout but no luck.

<httpRuntime maxRequestLength="409600" executionTimeout="720" />

Page.Server.ScriptTimeout = 3600;

I also tried flushing the data each time it goes in the repeater but doesn't do anything.

The code behide calculates the totals for the data range works fine but when it populates the salesrepeater with all the products that was sold, I get a timeout.

Is there a way to resolve this? I was thinking maybe a stored procedure to run in the background that calculates the total for all products and on the repeater, just to output each item. sales by 3 months can go up to 300-400.

Please see below code.

  protected void BtnFilter_Click(object sender, EventArgs e)
{
    if (StartDate.SelectedDate == null && FinishDate.SelectedDate == null)
        return;

    startDate = DateTime.Parse(StartDate.SelectedDate.ToString()).Date;
    endDate = DateTime.Parse(FinishDate.SelectedDate.ToString()).Date;

    DateTime now = DAL.TimeZoneConversion.ConvertDateTime(DateTime.UtcNow, thisShop.Company.TimeZoneLocationID);

    CurrentDateTimeLiteral.Text = now.ToString() + " (" + thisShop.Company.TimeZoneLocationID + ")";
    DateRangeLiteral.Text = startDate.ToShortDateString() + " - " + endDate.ToShortDateString();
    TaxTypeLiteral.Text = thisShop.TaxName;
    TaxTypeLiteral2.Text = thisShop.TaxName;
    TaxTypeLiteral3.Text = thisShop.TaxName;
    TaxTypeLiteral4.Text = thisShop.TaxName;
    TaxTypeLiteral5.Text = thisShop.TaxName;
    TaxTypeLiteral6.Text = thisShop.TaxName;
    TaxTypeLiteral7.Text = thisShop.TaxName;
    TaxTypeLiteral8.Text = thisShop.TaxName;
    TaxTypeLiteral9.Text = thisShop.TaxName;

    IObjectScope scope = ORM.ScopeFactory.GetPerRequestScope(HttpContext.Current);

    decimal totalPAmount = 0;
    decimal totalPTax = 0;
    decimal totalDAmount = 0;
    decimal totalDTax = 0;
    decimal totalAmount = 0;
    decimal totalTax = 0;

    var shopOrders = (from shopOrder in scope.Extent<ORM.Shoporder>()
                      where shopOrder.ShopId == shopId &&
                            shopOrder.CreateDateTime.Date >= startDate && shopOrder.CreateDateTime.Date <= endDate && shopOrder.IsDeleted == false &&
                            (shopOrder.IsReceiptSent == true || shopOrder.IsReceiptSkipped == true)
                      orderby shopOrder.CreateDateTime descending
                      select shopOrder);

    foreach (ORM.Shoporder thisShopOrder in shopOrders.ToList())
    {
        decimal orderTotal = thisShopOrder.TotalCostIncludingTax; //DAL.DataClasses.ShopOrder.CalculateOrderTotal(thisShopOrder.ShopOrderId);

        totalAmount += thisShopOrder.TotalCostIncludingTax;
        totalTax += thisShopOrder.TotalTaxCost;
        totalPAmount += thisShopOrder.ProductCostIncludingTax;
        totalPTax += thisShopOrder.ProductTaxCost;
        totalDAmount += thisShopOrder.DeliveryCostIncludingTax;
        totalDTax += thisShopOrder.DeliveryTaxCost;

    }

    TotalLiteral.Text = totalAmount.ToString("C");
    TotalTaxLiteral.Text = totalTax.ToString("C");
    TotalDLiteral.Text = totalDAmount.ToString("C");
    TotalDTaxLiteral.Text = totalDTax.ToString("C");
    TotalPLiteral.Text = totalPAmount.ToString("C");
    TotalPTaxLiteral.Text = totalPTax.ToString("C");

    ReportDiv.Visible = true;
    Populate();
}


private void Populate()
{
    IObjectScope scope = ORM.ScopeFactory.GetPerRequestScope(HttpContext.Current);

    var result = from o in scope.Extent<ORM.Shopproductvariationprice>()
                 where o.Shopproduct.Shopcategory.ShopId == shopId
                 orderby o.Name, o.PriceIncludingTax
                 select o;

    SaleRepeater.DataSource = result.ToList();
    SaleRepeater.DataBind();
}

protected void SaleRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    IObjectScope scope = ORM.ScopeFactory.GetPerRequestScope(HttpContext.Current);

    RepeaterItem item = e.Item;
    if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
    {
        ORM.Shopproductvariationprice thisPrice = (ORM.Shopproductvariationprice)item.DataItem;

        Literal ProductNameLiteral = (Literal)item.FindControl("ProductNameLiteral");
        Literal ProductCountLiteral = (Literal)item.FindControl("ProductCountLiteral");
        Literal ProductAmountIncludingTaxLiteral = (Literal)item.FindControl("ProductAmountIncludingTaxLiteral");
        Literal ProductAmountExcludingTaxLiteral = (Literal)item.FindControl("ProductAmountExcludingTaxLiteral");
        Literal ProductTaxLiteral = (Literal)item.FindControl("ProductTaxLiteral");

        int productCount = 0;
        decimal productAmount = 0;
        decimal productTax = 0;

        ProductNameLiteral.Text = thisPrice.Name;

        var shopOrders = (from shopOrder in scope.Extent<ORM.Shoporder>()
                          where shopOrder.ShopId == shopId &&
                                shopOrder.CreateDateTime.Date >= startDate && shopOrder.CreateDateTime.Date <= endDate && shopOrder.IsDeleted == false &&
                                (shopOrder.IsReceiptSent == true || shopOrder.IsReceiptSkipped == true)
                          orderby shopOrder.CreateDateTime descending
                          select shopOrder);

        foreach (ORM.Shoporder thisShopOrder in shopOrders.ToList())
        {
            foreach (ORM.Shoporderproduct thisShopOrderProduct in DAL.DataClasses.ShopOrder.GetProductsInOrder(thisShopOrder.ShopOrderId))
            {
                if (thisShopOrderProduct.ShopProductVariationPriceId == thisPrice.ShopProductVariationPriceId)
                {
                    productCount += thisShopOrderProduct.Quantity;
                    productAmount += (thisShopOrderProduct.Quantity * thisShopOrderProduct.Shopproductvariationprice.PriceIncludingTax);

                    if (thisShopOrderProduct.Shopproductvariationprice.Shopproduct.IsTaxable)
                    {
                        decimal taxRate = 100 + thisShop.TaxPercent;
                        decimal thisProductPriceExcludingTax = thisShopOrderProduct.Shopproductvariationprice.PriceIncludingTax * 100;
                        thisProductPriceExcludingTax = thisProductPriceExcludingTax / taxRate;
                        decimal thisProductTax = thisShopOrderProduct.Shopproductvariationprice.PriceIncludingTax - thisProductPriceExcludingTax;

                        productTax += (thisShopOrderProduct.Quantity * thisProductTax);
                    }
                }
            }
        }

        ProductCountLiteral.Text = productCount.ToString();
        ProductAmountIncludingTaxLiteral.Text = productAmount.ToString("C");
        ProductAmountExcludingTaxLiteral.Text = (productAmount - productTax).ToString("C");
        ProductTaxLiteral.Text = productTax.ToString("C");

        totalProductAmount += productAmount;
        totalProductTax += productTax;
        totalItemsSold += productCount;

        Response.Flush();

    }

    if (item.ItemType == ListItemType.Footer)
    {
        Literal TotalCountLiteral = (Literal)item.FindControl("TotalCountLiteral");
        Literal TotalAmountIncludingTaxLiteral = (Literal)item.FindControl("TotalAmountIncludingTaxLiteral");
        Literal TotalAmountExcludingTaxLiteral = (Literal)item.FindControl("TotalAmountExcludingTaxLiteral");
        Literal TotalTaxLiteral = (Literal)item.FindControl("TotalTaxLiteral");

        TotalCountLiteral.Text = totalItemsSold.ToString();
        TotalAmountIncludingTaxLiteral.Text = totalProductAmount.ToString("C");
        TotalAmountExcludingTaxLiteral.Text = (totalProductAmount - totalProductTax).ToString("C");
        TotalTaxLiteral.Text = totalProductTax.ToString("C");
    }
}

}