locked
Slow Loading RRS feed

  • Question

  • User882019344 posted

    Hi,

    I am writing an inventory web application, and the page I'm currently working on takes 65 seconds to load, and its only 1/3 done, and by the end I'm affraid that it will take 200+ seconds, and/or it'll give me a server timeout.

     I was hoping if someone can tell me why it is taking so long, here are the specs:

    Platform:  Classic ASP / IIS 7
    Database: Access 2000, aprox 30 tables

    Code:  I'm using about 10 ADODB.Recordsets, but i do destroy them as soon as I'm done by "Set rs = Nothing"  This page is to actuaully project how much on hand inventory will there be in a future date, so I need to loop trough all dates until I reach the future date, and my current example of 65s is for the next date, but what if I'm doing a 2-week projection, that by theory would take 65s * 14 + my other functions.

    Any help will be greatly appreciated.

    Tuesday, January 13, 2009 12:33 PM

All replies

  • User-823196590 posted

    If you're trying to display all that data onto one page there's probably not much anyone can do to make it faster.  The only thing I can suggest is paging - only show so many records at a time.

    Tuesday, January 13, 2009 4:51 PM
  • User882019344 posted

    Thank you very much for your reply.

     Unfortunately, I don't think that I can use paging because, there are only 110 products.

     Do you think the speed issue is related because I'm using an Access db, vs a SQL DB, or should this type of an app not be intended for online?

    Do you think another reason for the speed might be my code structure, which basically is as follows:

    confSales = 0
    unconfSales = 0
    projSales = 0

    Do While Not rsProd.EOF  '110 Records
       Do While Not rsCust.EOF '242 Records

       rsOrder.Open SELECT * FROM ... Where ...
       x = 0
       Do While Not rsOrder.EOF '0 to 1 Record
             x = x + 1
            rsOrder.MoveNext
       Loop

       If x <> 0 Then
              rsOrder.MoveFirst
             confSales = confSales + rsOrders.Fields(...)
             unconfSales = unconfSales + rsOrders.Fields(...)
       Else
          rsProj.Open SELECT * FROM ... Where ...
          x = 0
          Do While Not rsProj.EOF '0 to 1 Record
             x = x + 1
            rsProj.MoveNext
         Loop

         If x <> 0 Then
              rsProj.MoveFirst
              projSales = projSales + rsProj.Fields(...)
          End If

       End If

       rsOrder.Close
       rsProj.Close

       rsCust.MoveNext
      Loop

    rsProd.MoveNext
    Loop

    Set rsProd = Nothing
    Set rsCust = Nothing
    Set rsOrder = Nothing
    Set rsProj = Nothing

    Tuesday, January 13, 2009 5:40 PM
  • User-1796730883 posted

    I would say,it is caused by the code and logical complexity:

    1. There are too many loops(2 layers):

    2. It'd better execute sql statement out of the loop(by adjust your sql query statement).

    3. Querying fields what is needed, do not use select *... .

    Hope it helps you

    Regards

     

     

    Monday, January 19, 2009 3:33 AM