Hello , i need someone please to criticize this following concept and tell if there are better way to use.

The Background:
I''m working on a partial E-Commerce website , people won't buy by credit card but they will fill-in an order form or Order by phone by phone. It's Estimated that the website will contain around 500 item when it's in it's full working power.

The user will see a list of items in front of him , if he clicks any of the items, a more detailed window will be opened.

The website is built using ASP.Net

The Problem:
The Ordinary technique that is being used recommends The Following: Whenever Database query is needed, A connection to the database is Opened, Data Retreived then Connection closed.

My concern is the following: With every user that browse the products a new databse connection is created, the databse retreived is a list of all the items which falls under some Category , The Server resources may be consumed and a lot of data will have to be transferred if a lot of requests is made in some moment.

My proposed Solution: I'll Keep in the system a Copy of the Table of the items lisr. So if many useres browsed , no connection to the databse is needed as they all will retreive data from that table. However if someone decided to see a detailed information about some product, then at this moment a connection is made to the databse.

This table will be updated when an items is added or edited in the items table.

Should i go for this approach or it won't really make much diffirence??
Thanls for help.

Hi

In .NET you can cache tables in the web server cache. This minimises calls to the db. However, it is tricky to get this table to update whenever the source table in the database is updated (depending, of course, how it is updated). This is the tradeoff.

As with most theoretical performance bottlenecks you really need to prove that your concerns are founded on facts not guesses. You could do with guestimating the maximum\ target number of simultaneous users and load testing your application with that number of users. If there is no marked performance decrease then you've saved yourself the trouble of fixing a problem!

HTH

On top of whatpootle flump has recomended you could try Connection Pooling

"In software engineering, a connection pool is a cache of database connections maintained by the database so that the connections can be reused when the database receives future requests for data."

You should go with the simplest solution and just pull the data from the database. If you have say 10 categories on 500 products then this would be 50 records each time - which is a tiny amount of work for any database. Also keep in mind that the database will cache any frequently accessed data so it will be kept in memory anyway.

Writing code to do the caching for yourself is just wasted effort - you'll be trying to solving a problem that doesn't really exist. You'll just end up with complex code, more bugs and no real gain.

You'd also do much better to use permanent connections (assuming PHP) to the database rather than disconnecting each time.

Always go with the simple solution.

Mike

EDIT : sorry Ronnyy - you must have posted this info will I was writing.