Server Or Database

Question

Hi guys I need your opinion.I am in a situation where I need to use ajax so I need to query users info every 30 seconds.....which is more efficient query the server first(say check a text file first) and if there is an update for a user info, do a full blown database query or just directly query the database directly every 30 seconds.Please explain your reasoning so that I can weigh my decision.......thanks in advance.

Answer 1

depend the number of user you have but doing some polling will unlikely scale.

You should take a look at the comet architecture. You should code some middle ware that would talk to the database only when needed and would do much of the things in memory or on flat file. The middle ware would do some caching between your frontend(webserver) and the actual backend (database).

I advise you also to look into memcachedb (or any key/value DB engine) for coding your middleware.

If you don't have too much users that's ok to keep some polling scheme.

Answer 2

I'd cache on the web server, lifetime of 30 seconds and pull all user data to minimize round trips to the DB. I'd also have the client use the web cache, not go to the database.

It's a lot of overhead for minimal gain otherwise

Answer 3

If you have a small number of users and few web clients, then implement the most simple solution.

Reasoning: There is no need to optimize something like this unless you have severe performance problems. I'd start thinking about this if:

• There are thousands of users in the DB
• This info was queries every second by hundreds of web clients

But if you have 10 users, then this probably translates to 10 web browsers asking for this info every 30 seconds. You should worry about the poor CPU on your servers because it's going to be bored to death ;)

Answer 4

I would measure first if the simpler version (seems to query database directly first) is not good enough.

Then, if this would be not good enough, look for better solution (still measuring results).

There are to many ifs and depends to give you good advice remotely.

Answer 5

Go for the query but keep it optimized by limiting the number of columns.

Or check the last_modified - server_time( ) to see if its less than 30 seconds before attempting a select * query.

Answer 6

Not sure I see the need for caching, but maybe I'm being naive? How does the Cache know whether the data has changed, or not?

SELECT ... lots of columns
FROM MyTable
WHERE UserID = 1234
      AND LastUpdated > @MyLastUpdated

is only going to do any serious work when the AND is true? If you get zero rows back it hasn't changed - or UserID = 1234 doesn;t exist any more, but if you need to know that the query could be changed to return that too). Thus I don't think its necessary to do a "Has UserID = 1234 changed since @MyLastUpdated" query followed by a full-blown SELECT *.

If you retreive the value of LastUpdated in the column list, store that, then its available as a parameter when you next check if UserID = 1234 has changed.

(LastUpdated is not the time you last checked, but the time that the record was last updated, obviously, but it does need to be created by the same clock each time, e.s. the SQL box itself (not the client's PC!!), and allowing for what happens with daylight saving time in the Autumn.)