Solved

Can I make my SSSSLLLLOOOWWWW page load any faster?

Posted on 2004-04-13
7
189 Views
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?

Thanks.
0
Comment
Question by:jholton
7 Comments
 
LVL 11

Expert Comment

by:Slimshaneey
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.

http://www.4guysfromrolla.com/webtech/092599-1.shtml
ALso look for the advanced da\tashaping one also on that site
0
 
LVL 15

Expert Comment

by:deighc
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.
0
 
LVL 28

Expert Comment

by:sybe
ID: 10814220
agree with deighc: you are probably using many recordsets, and maybe even creating recordsets while looping through another recordset. Show your code.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:jholton
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
etc.

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

etc.

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
        (HRS)    
---------------------------------------------------------------------------------------------------------------------------
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?

Thanks.

0
 
LVL 15

Expert Comment

by:deighc
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.
0
 

Author Comment

by:jholton
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?

Thanks.
0
 
LVL 15

Accepted Solution

by:
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:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjodbrec.asp?frame=true

... and info about the GetRows method:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthgetrows.asp?frame=true

> 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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now