Can I make my SSSSLLLLOOOWWWW page load any faster?

Posted on 2004-04-13
Last Modified: 2008-02-01
For my company I am basically creating a web based report using ASP and SQL Server.  
Each row of the report contains a month-to-date total and then loops through the weeks displaying the totals for each week.  (the raw data is in daily form)
Currently ten weeks are being displayed.  Therefore each row has 11 database calls (10 weeks + month-to-date).  There are 50 rows on the page.  
The page takes close to five minutes to load.

Any advice?

Question by:jholton
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Expert Comment

ID: 10813677
I would suggest looking into DataShaping. which is basically returning a recordset of recordsets. Works really well in scenarios like this. Its a big and scary subject at first though. Have a loojk here. You will return the same data with only one call to the DB.
ALso look for the advanced da\tashaping one also on that site
LVL 15

Expert Comment

ID: 10813872
Fully explore the use of the GROUP BY clause in conjunction with SQL date functions. I find it hard to believe that you can't do this in a single query.
LVL 28

Expert Comment

ID: 10814220
agree with deighc: you are probably using many recordsets, and maybe even creating recordsets while looping through another recordset. Show your code.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Author Comment

ID: 10814741
Yes I am (creating recordsets while looping through another recordset) for some of the report.  This report displays information on product defects/repairs.

My SQL table looks something like this:

CustAccountNo     |     CallEntered                     |     CallCompleted              |     ProductCode     |     ProblemCode     |      OfficeOnly    
11111                       4/1/2004 06:34 AM               4/3/2004 03:23 PM                    AA                           P1                           N
22222                       4/2/2004 08:23 AM               4/2/2004 08:56 AM                    AA                           P2                           Y
33333                       4/3/2004 03:45 PM               4/4/2004 02:45 PM                    BB                            P3                           N

First I list the different five products and the number of trouble calls per product and a row showing the "rate" (percentage) of trouble calls for the total number of customers.  That number comes from another table.  I have the total number of products added to that table every week.

ProductCode     |     AsOfDate     |     ProductCount
     AA                       4/4/2004              11000
     BB                       4/4/2004               9000


So, I end up with the top portion of the report that looks like this:
Product                  |     MTD     |     Apr 10     |     Apr 3     |     Mar 27       |     Mar 20     |
AA                         |    4,529     |      735        |      2793     |       2819       |       2602
AA Rate                 |       6%     |        5%       |       7%      |          6%       |         5%
[Other]                  |    4,529     |      735        |      2793     |       2819       |       2602
[Other] Rate           |       6%     |        5%       |       7%      |          6%      |         5%
% Office Only         |      7%      |        11%      |      6%        |          6%       |         7%
% Field                  |     93%     |        89%      |      94%      |          94%     |         93%
     Total Field         |    4212      |        649        |      2625     |        2645      |         2406
% repaired 12 Hrs   |      29%   |        36%      |      26%      |         26%        |        31%
% repaired 24 Hrs   |     22%    |        20%      |      24%      |          21%       |         24%
AVG Repair Time     |    44        |        37         |      38         |         38           |         34
Top Ten Solution Codes
'Here I execute a query that grabs the ten most used "ProblemCode".  Then I loop through the recordset creating a row for each one.
       P1                  |2719         |       66          |       249      |           246        |        241
loop through the rest

I have developed stored procedures that I use over and over.  
Given that today is 4/13, for the MTD  I grab my totals from 4/1 through 4/12.    Data for 4/13 will be reported tomorrow.
So, for the first row, I grab the totals for product "AA" from 4/1 through 4/12.  Then I grab the data from 4/10 to 4/12.  Then 4/3 through 4/9.  etc.  Five database queries for the row.  That's basically what I do for the whole table.  I have stored procedures that I use over and over by section.

What SHOULD I be doing?


LVL 15

Expert Comment

ID: 10814963
It's hard to provide a full answer without knowing table and field names but should be able to use your basic SELECT syntax with the following modifications:

SELECT <your current fields in here>, YEAR(<name of date field>) AS dateYear, MONTH(<name of date field>) AS dateMonth, DAY(<name of date field>) AS dateDay
WHERE <your current WHERE clauses here>
ORDER BY YEAR(<name of date field>), MONTH(<name of date field>), DAY(<name of date field>), <existing ORDER BY clauses - if you have them)
GROUP BY YEAR(<name of date field>), MONTH(<name of date field>), DAY(<name of date field>)

and if you have any WHERE clauses that strictly specify a date for a specific day then remove them. This will give you a result set for the entire set of records, so you may want to limit it to a given year and/or month:

eg for April 2004:
WHERE YEAR(<name of date field>) = 2004 AND MONTH(<name of date field>) = 4

So now you'll have a recordset with a multiple rows. Each row will be the results for a specific day-month-year combination. You can loop thru the recordset and make your report using the aliased field names dateYear, dateMonth and dateDay to determine which date the results are for.

This is obviously just a loose guide to grouping by date values, but I hope it helps.

Author Comment

ID: 10824015
So, it's much faster to just get a huge recordset and movenext, moveprevious, movefirst, movelast and loop and loop than to do so many database queries?

deighc, In your above example, do you know how I could perform that select and group on a weekly basis instead of daily?

LVL 15

Accepted Solution

deighc earned 100 total points
ID: 10824326
> So, it's much faster to just get a huge recordset and movenext, moveprevious,
> movefirst, movelast and loop and loop than to do so many database queries?

Yes. Maybe. Probably.

When you have a recordset object, you get the best performance by using a foward moving cursor type - ADO constant adOpenForwardOnly (ie. 0)  - in conjunction with a read-only lock type - ADO constant adLockReadOnly (ie. 1).

You get a bit of a performance hit if you want to move back and fowards thru the recordset because you have to use a dynamic cursor type - either adOpenDynamic (ie. 2), or adOpenStatic (ie. 3). And you get a further performance hit if you want an updateable recordset because you have to use an updateable lock-type - either adLockOptimistic (ie. 3) for row-by-row locking, or adLockBatchOptimistic (ie.4) for batch updates (I'm guessing that you DON'T need an updateable recordset since you're generating a report, so stick to a read-only lock type).

And bear in mind that in ADO every time you fetch a data item from a recordset object you're actually going back to the database over the connection to get it. However this, I suspect, will always be faster than creating lots of recordsets because there's always overhead in creating the object and executing the query on the database.

But it raises some other points: if you have to re-use the data from your recordset many times then it's probably best that you write the value(s) into local variables and use those instead. You could also investigate the use of the GetRows method of the recordset object. This pulls all the values from the recordset object into a 2 dimensional array. Once you've done that you can destroy your recordset object and just use the array (arrays are VERY fast of course). In fact for pure performance this is definitely the best option. But it's also the hardest to maintain because you reference your database columns with numeric values. So every time you add or remove values from your SELECT statement you have to change your array indexes. A pain in the bum. And, for the same reason, you also end up with code that isn't especially logical to look at because you simply have alot of numeric indexes everywhere.

Maybe have a bit of a read on MSDN about the recordset object. It provides a good reference for the various cursor types, lock types etc:

... and info about the GetRows method:

> do you know how I could perform that select and group on a weekly basis instead of daily?

I can't think of a way of doing this entirely in SQL so I suggest you determine the start and end dates of the week in script then pass these values into the SQL query.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question