Optimizing Classic ASP Site w/SQL Server 2012 R2 Express

I time races and we have a couple of big ones on Thanksgiving.  A year ago (with fewer runners) we had some troubles overloading the server when people were looking for their results.  I want to make sure it does not happen again this year.  I am looking for some advice on how to best prevent any issues.  I have eliminated any ReDim Preserve.  I can post my code at some point in this thread if need be but does anything jump out as Web 101 ("Make sure you aren't...).

Would an Ajax/JQuery call to the results help?
Bob SchneiderCo-OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Make your sql queries as lean as possible.  Instead of "Select * from mytable", select only the fields you need, "select field1, field2 from mytable where field3 = 'something'"

Close your recordsets as soon as you can.  

Use getrows to create your output as an array.

<%
' if not rs.eof then
     myArray = rs.getrows
end if
%>

Open in new window


Ajax is not the answer if over done. But if you have a lot of data it can help.  You can also force data to output to the screen if you have a lot of data with response.flush.

The sql/queries themselves need to be looked at.  If you have something very complex, chances are it can be done better.  Check with the sql folks here.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob SchneiderCo-OwnerAuthor Commented:
Here is the main results query.  It looks like I have adhered to most of your suggestions.  This is the one that i would consider using Ajax on, thinking that it would render large amounts of data more quickly:
                sql = "SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St "
                sql = sql & "FROM Participant  p INNER JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID "
                sql = sql & "INNER JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID "
                sql = sql & "INNER JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID "
                sql = sql & "WHERE ir.RaceID = " & lRaceID & " AND ir.FnlTime IS NOT NULL AND ir.EventPl >= " & iFirstRcd 
                sql = sql & " AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl"
            Else
                sql = "SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St "
                sql = sql & "FROM Participant  p INNER JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID "
                sql = sql & "INNER JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID "
                sql = sql & "INNER JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID "
                sql = sql & "WHERE ir.RaceID = " & lRaceID & " AND p.Gender = '" & sGender & "' AND ir.FnlTime IS NOT NULL AND ir.EventPl >= " & iFirstRcd 
                sql = sql & " AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl"
            End If

            Set rs = Server.CreateObject("ADODB.Recordset")
            rs.Open sql, conn, 1, 2
            If rs.RecordCount > 0 Then
                IndRslts = rs.GetRows()
            Else
                ReDim IndRslts(9, 0)
            End If
            rs.Close
            Set rs = Nothing
          
            're-order if by time as opposed to place
    	    If sSortRsltsBy = "FnlTime" Then
                For i = 0 To UBound(IndRslts, 2) - 1
                    For m = i + 1 To UBound(IndRslts, 2)
                        If ConvertToSeconds(IndRslts(5, i)) > ConvertToSeconds(IndRslts(5, m)) Then
                            For n = 0 To 9
                                TempArr(n) = IndRslts(n, i)
                                IndRslts(n, i) = IndRslts(n, m)
                                IndRslts(n, m) = TempArr(n)
                            Next
                        End If
                    Next
                Next

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
What you should do is create the rendered sql statement along with some sample data and table set up like you have done in the past, then post a question about your sql just using the sql server topic.  Also ask about the best way to index all the tables.  

Proper indexing will also speed things up a lot as well.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Bob SchneiderCo-OwnerAuthor Commented:
Will do.  Thanks!
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
When you post to the sql question, keep the syntax similar to below and just a few rows of sample data

CREATE TABLE supportContacts 
	(
     id int identity primary key, 
     type varchar(20), 
     details varchar(30)
    );
 
INSERT INTO supportContacts
(type, details)
VALUES
('Email', 'admin@sqlfiddle.com'),
('Twitter', '@sqlfiddle');

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
seeing how you have a couple joins in your sql statement, you may consider creating views with the sql statement (without the where clause), then in your asp code, query that view with the appropriate where clause. I've used this technique in my own apps and it's sped things up dramatically.
0
Bob SchneiderCo-OwnerAuthor Commented:
I will try that.  I might post a question to get specific assistance.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
A view is not going to speed things up.  The advice you have on the thread about optimizing your db is going to make the difference.  http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28555067.html

The big caution with creating views in sql manager is you may end up doing things because it is easier for you to understand but actually slows things down.  I only know from experience.

Proper indexing plays a very big role as does the more complex sql you may not have thought of.

Your last bottleneck is using express.   sql express will only use a maximum of 1 gig of ram.  Sql server needs lots of ram and there is no such thing as too much.    I would explore going to  a dedicated server where you can get at least 4 gigs of ram and use sql server web edition.  It will cost about $35 to $50 per month (sql web edition).  You get  the full ram avail.  The only difference between the web version and full I think is the ability for advanced reporting like data cubes.   http://msdn.microsoft.com/en-us/library/cc645993.aspx
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I forgot to mention using Azure for your db  http://azure.microsoft.com/en-us/  Just do the math on pricing.

Also you can get a free year of hosting and db from amazon http://aws.amazon.com/free/  Just watch the final pricing for what you are doing because you will eventually need to pay.

I recommend using liquidweb  https://www.liquidweb.com/dedicated/single-processor.html.  The single processor servers will be more than what you need and come with 8gigs of ram.  It shows they start at $200 but adding windows and sql server etc will add another $40 to $50.
0
Bob SchneiderCo-OwnerAuthor Commented:
Thanks Scott.  I have recently moved to liquid web and it has been a great move.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Great, then it should be easy to add sql server web if you are on dedicated.  If you are on a vps with 2gigs of ram, you will end up in trouble.    If you compare the economics of the 4gig vps vs 8gig dedicated with the  Xeon E3-1240 V1 3.3Ghz, Quad Core they are about the same monthly if you pay the set up fee.   With the sql server web edition, you will have access to the full 8 gigs of memory.
0
Bob SchneiderCo-OwnerAuthor Commented:
What do you mean "add sql server web"?  I thought by having sql server intalled on the server and using it as the back-end of my sites there was nothing more to do.  No?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You said you are using sql server express.  That only will use 1 gig of ram even if you have 8 gigs available.  The other versions of sql server will use more.  The web version I think uses up to 64 gigs of ram and if you need more than 64 gigs of ram you use the full version.  If you have 64 gigs of ram on a webserver, you are probably going to be paying in the $500 range each month.

For what you are doing with one site, 4 gigs should be plenty.  If you are on a dedicated server from LW, then you have 8 gigs of ram.  To fully utilize all of that you can't use sql server express because of the 1 gig limit for express.   If you are on a vps with 2gigs of ram then going with sql web will probably not be that much help because the other gig of ram is already being used.  

RDP into your server and look at the RAM being used and that will give you some clue.
0
Bob SchneiderCo-OwnerAuthor Commented:
Got it.  So if I wanted to use more than 1 GB of RAM I have to upgrade my sql server.  What version has 4GB and, if you know, how much does that run?  Also, could using express be a contributing factor to my site erroring out under high volume?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Yes, using express could very well be a contributing factor along with sql queries, indexing etc.   But the key is sql is thirsty for RAM.  For high volume data intensive sites, express may not cut it.    The next step up for sql server is the web version.   If you are on a dedicated server with LW, that should be a $35 month charge and I saw on the vps it is $37.50.  

If you have 4 gigs on a dedicated server, inquire with LW about switching servers since the single processor servers they offer now are 8 gigs.   If you are on a VPS with 4 gigs, you are probably paying about as much as a dedicated.  

Since you are cutting it close to your big race, I would just go with what you have now and upgrade to using sql server web edition.    Then after the dust settles, look to upgrading the server if it makes sense.

From experience, I have tried a 2 gig server with sql server and it was continually maxed out and having to restart.
0
Bob SchneiderCo-OwnerAuthor Commented:
I am on an unmanaged server with LW...I assume that is synonymous with dedicated server?  It had sql server 2012 express on it.  I know my server
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.