Solved

Optimizing Classic ASP Site w/SQL Server 2012 R2 Express

Posted on 2014-11-10
16
189 Views
Last Modified: 2014-11-13
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?
0
Comment
Question by:Bob Schneider
  • 8
  • 7
16 Comments
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 40434229
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
 

Author Comment

by:Bob Schneider
ID: 40434971
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40435006
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
 

Author Comment

by:Bob Schneider
ID: 40435053
Will do.  Thanks!
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40435079
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40436339
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
 

Author Comment

by:Bob Schneider
ID: 40436451
I will try that.  I might post a question to get specific assistance.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40438297
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40438318
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
 

Author Comment

by:Bob Schneider
ID: 40439898
Thanks Scott.  I have recently moved to liquid web and it has been a great move.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40439980
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
 

Author Comment

by:Bob Schneider
ID: 40440712
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40441207
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
 

Author Comment

by:Bob Schneider
ID: 40441224
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40441301
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
 

Author Comment

by:Bob Schneider
ID: 40441510
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

21 Experts available now in Live!

Get 1:1 Help Now