Architectural question

Hi,
Currently I have basically a 2 tier application that creates a lot of database hits. This application is running on about 10 clients, and for the most part it is requesting the same information from the database for each user. This is resulting in my sql server running quite high CPU utilisation.

I was thinking that maybe I should have a middle tier which handles the database requests, but this alone won't reduce the load on the server. Is there an easy way to cache or group together the database requests. Eg, if user 1 and user 2 are both asking for the same thing within a certain time of each other, is there an easy way to only request that information once from the database and send it to both clients?

I tried a basic cache where a server held in memory the table which is automatically refreshed every 5 seconds and requests hit that in memory representation rather than requesting from the sql server... but this seemed to be a lot slower to process requests (I assume because SQL server has indexes etc to speed up requests which the list doesn't).

Is there some other architectural or design principals I could use?

The database requests are very lightweight, and the table is indexed appropriately, I am just trying to reduce the shear number of requests.

Any help appreciated.

Rob

As I started reading your post, I immediately thought that the Server could cache the data in memory. Only to read later that you had tried this and it had not helped.

As an example.... Are your clients accessing 'todays' data from a table that has the last 10 years worth? If so, another option is to create another table in the database, that is refreshed overnight. This table just contains today's data. This may speed up the access as this new table could contain exactly what is required (allowing SELECT * FROM ...) rather than a query across many tables. So it's like an in-database cache as opposed to an in-memory cache.

Hmm, true, I would guess most of the requests are for todays data... I may look into this more, cheers.

As soon as data is sent to 1st client try to cache that data in some memory.. and whenever the pending remaining clients request comes .. .try to send this CACHED data itself , instead of querying freshly from the sql server.. as it is told that all 10 clients expect the same data from the server database..

vc... I think Robbo had already experimented with that option.