Solved

Optimizing Classic ASP Site w/SQL Server 2012 R2 Express

Posted on 2014-11-10
16
194 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 33

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Viewers will learn how the fundamental information of how to create a table.

809 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