Limit db connections & Deallocating Priorities

[Isadore]

Two questions:

1) Many pages on my site connect to the database to create a grid, etc., and then the client moves on to the next few pages, looking at graphs, etc... It would be nice if, when the user returns to a previous page, e.g., to select a new item, to call up the original page so that there is no need to "re-connect" to the database thereby minimizing the number of times a user does so. The JavaScript code (...history.go(-1)...) to go back one page will only work in special circumstances. Should I keep track of the page numbers and use JavaScript to overcome this? A typical session on my site would have about 12 less db connections if I could send the user back to the original page.

2) Could someone briefly prioritize deallocation, e.g., what "stuff" should be set to nothing, gotten rid of immediately (like a Session state variable) etc... Just a brief list would be nice. My ASP.NET pages create a handful of Session state variables, and dozens of connections to the database (OLE DB); I generally end the code with:

....
myreader.Close()
dbconn.Close()

...just a few ideas on deallocation -- I get the feeling that this might become a future problem if not understood -

 

[link9]

You need to look into asp.net's caching capabilities. It will cache pages based on many different parameters (including request variables) and is generally held to be one of the best things you can do to increase performance. There's simply too much to it for me to try to even scratch the surface in here. Instead, I would suggest a google search on the subject. Here are some articles to get you going:

http://aspnet.4guysfromrolla.com/articles/022802-1.aspx

http://www.aspalliance.com/aspxtreme/webapps/aspcachingfeatures.aspx

http://msdn.microsoft.com/library/en-us/dnaspnet/html/asp04262001.asp

Sounds to me like it's exactly what you're after, as it will greatly decrease the number of times your db is actually hit for data.

 

[Isadore]

Thanks Link. I posted a question regard the Access *.ldb file, created when a connection is broken. We haven't released the web site yet but when one of these ldb files are created I can't delete them from the Server (have to call the Server adminstrator this morning) and so the web site completely "shuts down". This doesn't seem practical since it seems to me that connections now and again must be broken on the net, even if the code is perfect. Is this a nitemare waiting to happen?

 

[link9]

Yes, it is. Access simply isn't designed to be multi-user and web enabled. Please see my response to that thread.

 

[Isadore]

Link: I found the answer to the Cache problem; quite simple. In the header of the aspx page put:

<% OutputCache Duration=&quot;300&quot; VaryByParam=&quot;none&quot;%>
....
....

300 seconds (5 mins) is the maximum time a Page will be held in the Cache. If Server resources get low .NET may remove it prematurely. So, a simple solution.

One quesiton. Re: Access. I don't need Access functionality on the web, on the tables. If you had to upgrade to the next best set of tables, what would you pick?

 

[Isadore]

One footnote on the above Cache scheme. The Vary by Parameters attribute can take on:

&quot;none&quot; or &quot;*&quot; or &quot;MyID&quot;

IF none, then the cached page ignores all parameters passed to the page, if * then it will store different versions of the page as a function of a parameter and finally &quot;MYID&quot; stores a page for each MYID that is passed as a database parameter. The latter would work well in a couple of areas but when MyID starts getting large, well that's a diff story.

 

[link9]

It's really just a question of cost.

I see two choices (if it were me, and that's what you asked)

SQLServer
MSDE

SQL Server (standard) is $1500
MSDE is $0

I'd pick SQL Server, but that's because I'm not writing the check ;-) I'd pick MSDE if cost were an issue, and I do pick it for contract jobs where the client doesn't have the extra cash to go full SQLServer.

 

[Isadore]

link: Cost is not an issue. We have already been given an SQL Server position here at Auburn University, we just have to make the transaction. This may sound a bit elementary but I am assuming that I will still be in Access tables, just using the SQL Server to drill out the data. Is this not right? If it is; then that returns me to the question of tables..

 

[link9]

No, you will actually physically move your data from Access tables to SQL Server tables, and if you've been given space in a SQL Server, then export your access tables over there yesterday. All errors will magically disappear.

Exporting your data is a breeze with SQL Server's DTS services... just pick your source, your destination, and hit go. It really is that easy.

All you need to have on the client computer where you develop is the Enterprise Manager, which will allow you to administer the db's.

 

[Isadore]

Thanks Paul. If you give me one more hint on caching client side calcs that would do me for quite some time. Again thanks for your help.

 

[link9]

Not sure what you mean... caching client side calcs.

Client side calculations don't use your resources, which is why I'm a bit confused by your question.

Please give an example of what you'd like to do.


paul

 

[Isadore]

Paul -- the client side calc was in reference to our other discussion -- sorry!