www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Slow Loading

  1. #1
    Join Date
    Nov 2008
    Posts
    42

    Slow Loading

    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.

  2. #2
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    You would need to post your code so we can see what's going on. You may have multiple unnecessary loops and need a better query.

  3. #3
    Join Date
    Nov 2008
    Posts
    42
    ...
    Last edited by APD; 01-23-2009 at 12:24 PM.

  4. #4
    Join Date
    Nov 2008
    Posts
    42
    I also think that I'm using too many loops, but is tghere a way to simplify it:

    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 fldCust = rsCust!fldID AND fldProd = rsProd!fldID
    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 fldCust = rsCust!fldID AND fldProd = rsProd!fldID

    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

  5. #5
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    Where are your rsProd and rsCust queries?

  6. #6
    Join Date
    Jan 2009
    Posts
    8
    I see in your code you like counters. Why are you using a counter? and especially if there is only 1 record and if there is either a record or there isn't why use any type of loop?

    Code:
     
    If Not rsOrder.EOF Then  '0 to 1 Record
         confSales = confSales + rsOrders.Fields(...)
         unconfSales = unconfSales + rsOrders.Fields(...)
    Else
         rsProj.Open SELECT * FROM ... Where fldCust = rsCust!fldID AND fldProd = rsProd!fldID
    Also would a join of the tables work here? (I cannot tell because of the ... in your query)


    Another thing I noticed is that you use SELECT * FROM Table in your queries. That is a very bad practice especially if you are not grabbing all columns in the table.

    For each column (field) of each row there is a round trip to the database
    so if you have 10 columns (fields in the table) and 1000 rows of records you are making 10001 round trips to the database. (additional one for EOF)

    If you are only using 1 or 2 of the fields you are better off using SELECT FieldA, FieldB FROM Table

    Now instead of 10001 round trips to the database there is 2001 round trips.

    An even better method is to also use the .GetRows method to get all of the rows and store the data in an array that makes 1 trip to the database


    The last thing to look at is how is your database set up? Proper database design can also speed up the way a page loads. Is the database normalized? Are Primary and foreign keys set up? What about indexes? A properly designed database will return the records much faster.

  7. #7
    Join Date
    Nov 2008
    Posts
    42
    Hi,

    To answer everyone's questions and to provide a bit more detail:

    I am actually using an Access 2000 database, connecting to it w/dbq, using the following tables:

    • Produce: A list of produce (110)

    • Customers: A list of Customers (240)

    • UsualOrders: What produce qty does each cust usually orders each day of the week. Note, in this table I have:
    o AccessID (AutoNumber, Primary Key)
    o CustID (Long)
    o ProdID (Long)
    o Day (Single Number: 0=Sun; 1=Mon;…6-Sat)
    o Order (Double: qty)

    • RealOrders: Same as the UsualOrders table, except Day is Date (short)

    The Problem: Based on the last 2 tables, I need to project how much produce that my client needs to import on any given future date, so my logic is as follows:

    For each date from today until the given future date (first loop), and for each produce (second loop), see if each customer (third loop) first has anything in the RealOrders where the date = from the first loop, if not see if there’s anything in UsualOrders where day is the converted date from he first loop.

    I know that the slowness is due to the fact that I’m using so many loops, 3 in my general logic, plus another 5 as mj1223 noticed as counters. The reason why I use loop counters is to use If recCount <> 0. For some reason, my rs.RecordCount always returns -1.

    This logic is taking 65s for one day in the future, if more day are included, this will just be a multiplier to the 65s, which is a disaster.

    I was thinking of doing the following changes:

    • Avoiding SELECT *

    • In my UsualOrders replacing the Day and Order fields with Order0, Order1,…Order6, so I have less rows to read.

    • Instead of my counter loops, using If rs.BOF <> rs.EOF

    But considering the overall logic, this might be a lot of work (especially point 2) for 2-3 sec less.

    I think if I used lesser loops from my logic, JOINs, or a different table structure, it might help, but I cant see how?

    How do I normalize/index an Access DB? I have Primary Keys, but Secondary, I just have the fields in Access as Long, and fill them in by code.

    I will greatly appreciate any advice.

  8. #8
    Join Date
    Jan 2009
    Posts
    8
    When using Access unless you are using the AdOpenStatic cursor, rs.RecordCount will always return -1

    You mentioned that you want to use If recCount <> 0 Then However, you can accomplish the same thing with If NOT rs.EOF Then (if there are no records then EOF is returned. No need for a costly loop and counter or) Some people like to use If NOT rs.BOF And NOT rs.EOF Then however checking for BOF and EOF is not necessary, just checking for EOF does the same thing.

    Normalization of data is breaking the tables down to as small as possible of chunks so that the same info for a record is never in more than 1 table. for normalization more rows of records is always better than more fields in a table. (Especially if some fields will have null or empty values)

    To set the foreign keys in Access click on Tools ---> Relationships then list your tables finally drag the primary key field from one table to its associated foreign key in another table.

  9. #9
    Join Date
    Nov 2008
    Posts
    42
    mj1223, thanks for your post.

    If you don’t mind, I have a few more questions…

    Basically, I understand that I must reduce the number of loops and calls I make to my db. Also, if you recall my objective is to make inventory projections of a future date. I have implemented a restriction of maximum 365 days.

    Having said this, what do you thing of the following, and is it possible:

    - Merging my UsualOrders and RealOrders (described above) into one? If I do this, I’ll have 8 columns, by 9,716,300 rows (110 produce x 242 customers x 365 day). Is having this many rows possible in Access? What is the maximum?

    - With this method, I will loop 220 times (110 produce x 2 order types (usual, real)), instead of 9.7million, but I will be accessing the same info, but this time with SELECT SUM(fldOrder) AS OrderSum WHERE prodID = [from my loop]. Will this make a difference in my speed, or it won’t as I’m still performing the same calculation, but in SQL rather then looping?

    - Do you think if I have used a SQL db rather than an Access db, this would have made a difference?

  10. #10
    Join Date
    Nov 2008
    Posts
    42
    Hi, sorry for so many posts, but with the method described in my previous post, would it be also possible to do the following (separated events):

    - When a usual order is changed for a day of the week (mon, tues, …), this change would need to be applied 52 times, for each produce. Would it be efficient if I used a produce loop and a SQL (UPDATE tbl SET fld=’value’ WHERE prodID=[from loop]). So, in theory, 110 SQL statements will be executed, and each will update 52 rows. A total of 5,720 rows are updated in one event. This is for one order, but a max 30 orders can be edited at once

    - In doing a 365-day projection, and as each day passes, I plan on recycling my rows. For example, as January 30, 2009 passes (26,620 rows). I plan on executing 1 SQL UPDATE statement to change 3 fields.

    - in having so many rows, do you think its safe to use a AutoNumber as Primary Key?

  11. #11
    Join Date
    Jan 2009
    Posts
    46
    Can you, with out mentioning any programming you think is required, describe what information you need from the database? It seems to me that all the looping may not be necessary, unless I'm not understanding what you're trying to accomplish. If you don't care who ordered or usually ordered what and are simply looking for totals, there are much easier ways to get that data.

  12. #12
    Join Date
    Nov 2008
    Posts
    42
    Downtime,

    A few times I’ve thought about that there must be a simpler way, but I just can’t get my head around it. You’re also right, I just care about getting the final totals.

    I basically need these 3 components:

    1- Projected Customer Orders: This component basically tracks what each customer usually orders each day of the week. For example:

    Every Monday:

    ABC usually orders 50 Egg Plants; 60 Okras
    Int’l Market usually orders 60 Egg Plants; 45 Okras

    Every Tuesday:

    ABC usually orders 0 Egg Plants; …

    So on…

    2- Real Customer Orders: This component is pretty much the same as the first, but it is based on a specific date, rather then day. For convenience, this component also copies the projected orders for the day that the selected date falls under, but allowing the user to change these orders for that specific date. For example:

    The user selects Feb 9 (Monday), and only ABC for now. ABC’s Monday order is copied as Feb 9th order, but the user changes it to 55 Egg Plants; 40 Okras.

    These two component are done, and their info is stored into 2 separate tables. I’m willing to change this if I must.

    3- Inventory Projection: This is the component that I’m stuck on, and is giving me an ulcer. The user can select any future date between tomorrow and the next 365 days, and they are to get their projected inventory for that date by using the formula: [Vendor Orders] – [Customer Orders]. The Vendor Orders have the same 2 components as above.

    Currently, I’m working on the Customer Orders calculation, and for example sake, lets say that the above data is the only data in the system right now.
    To complete my example, let’s say Today is Jan 31, the user can project for any date between February 1, 2009 and February 1, 2010. For this example, the user has selected Feb 23, 2009, and this is the calculation that should happen:

    Egg Plant: .........................Proj.............................................Real
    .............Feb 1......................0.................................................0
    .............Feb 2..................150 (50+100)..................................0
    .............Feb 3......................0.................................................0


    .............Feb 9..................100 (50 was made real)...................55 (50 was edited to 55)

    .............Feb 16..................150
    .............Feb 23..................150

    .........................................550..............................................55


    Okra:..................................Proj...........................................Real
    .............Feb 1.......................0...............................................0
    .............Feb 2....................105 (45+60).................................0
    .............Feb 3.......................0...............................................0


    ..............Feb 9.....................45 (60 was made real)..............40 (60 was edited to 40)

    .............Feb 16..................105.............................................0
    .............Feb 23..................105.............................................0

    ........................................360............................................40


    That’s about it. Again, I don’t care how I get it, but for Egg Plant I need to get 550 projected + 55 real; and for Okra 360 projected; and 40 real.

    As you can see, as more produce and orders are added, and as the user selects a date further in to the future, the heavier this load gets.

    I’ll appreciate any advice.
    Last edited by APD; 01-31-2009 at 03:16 PM.

  13. #13
    Join Date
    Nov 2008
    Posts
    42
    Me again,

    I forgot to include 1 other thing in my objective that is supposed to influence my totals, and that is that each customer has Boolean True/False “fldActive”.

    My example above assumes that both, ABC and Int’l are Active. However, if for example, Int’l becomes inactive, then my totals should be for Egg Plant 310 projected (instead of 550) + 55 real (same); and Okra 180 projected (instead of 360) + 40 real (same).

    I hope that I’m wrong here, but as there are so many factors, the only solution is having a table that summarizes everything with 9 columns by 9.7million rows. The columns would be:

    1-ID (AutoNumber, Primary)
    2-Date
    3-DayOfWeek
    4-Prod
    5-Cust
    6-CustActive (T/F)
    7-Projected
    8-Real
    9-OrderType (1chr) p-projected; r-real

    The 9.7m rows, I figure by 110 Produce x 242 Customers x 365 Days = 9,716,300.

    If you have a better solutions, by all means, please enlighten me. If not, can you please provide some feedback to my 2 postings from 01-30-2009.

    Thank you,

  14. #14
    Join Date
    Jan 2009
    Posts
    8
    Keep the tables seperated. You do not want to combine the info into one. (you can always do a join to combine the data into one query)

    Yes, SQL Server and MySql are MUCH better than Access With the number of rows you are talking about Access will have a tremendous slow down whereas the other two will not.

    Let me get back to you with some sample code... I think it can be done a lot more economically than you are currently doing it.

    I don't know about a max number of rows. I know it does slow down as it gets past 20k records more than that think SQL Server or MySQL
    Last edited by mj1223; 02-02-2009 at 05:55 PM.

  15. #15
    Join Date
    Jan 2009
    Posts
    46
    I think maybe you need to come up with ways to process the different pieces of information you require independently. To me, it seems like you're trying to do too much at one time. What if you create a working table that is updated each time this big query is run. You can then process and store each piece for all the products.

    Have a table like this:
    prodID
    actual_total (total quantity within date range)
    projected_total (projected quantity within date range)
    adjusted_total (when getting the actual, compare actual_total and projected_total)


    First calculate the projected total and fill the database with that data for each product over the current query date range.

    Then calculate the actual total and adjusted total (+/- how much was projected to be ordered verses what was ordered) and fill the database with that data over the date range.

    Now you have a table filled with the data you need and can just query that. Modify your projected_total by your adjusted_total and just pull the actual_total.

    I would zero out those values in your working table when you're done with it so that they are zeroed out when you use it again.

    Finally, you might want to see what SQL functions might be helpful. Off hand, I'd try to group by prodID and use the SQL sum function to just get the totals by product of all orders within the date range you want.

    I use w3Schools.com all the time. They have examples of the differnet functions such as groupBy:

    http://www.w3schools.com/sql/sql_groupby.asp

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles