• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Can I make my SSSSLLLLOOOWWWW page load any faster?

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?

1 Solution
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
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.
agree with deighc: you are probably using many recordsets, and maybe even creating recordsets while looping through another recordset. Show your code.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

jholtonAuthor Commented:
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?


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.
jholtonAuthor Commented:
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?

> 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now