Slow Loading

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.

Answer 1

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.

Answer 2

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

Answer 3

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