c# datatable vs MySql table...
 

which one of these would be more efficient in terms of speed etc for storing temporary data? (mysql table would be accessed from C#)

the temporary data may or may not be "small"....

Temporary ... from day to day ? or minute to minute ?

What exactly are you trying to do ?

There is really a lack of information to give you any beneficial advice.

Normally, MySQL database content is used to populate DataTables in C#.
The concept of "small" is relative, what you consider small may not be what we consider small. If the transactions on the data are fairly frequent and/or you need to use that data elsewhere in the program, then use a MySql table.

for example, a table containing information of all the users online...

so its temporary in that sense...

wow I was expecting exact opposite reply...
I was expecting you to say "if the data are fairly frequent and/or you need to use that data elsewhere in the program , then use a C# table"!!
I mean wouldnt accessing data from MySql table take longer than C# datatable? ... or maybe I am missing something very fundamental

Bad choice of words on my part....

Of course accessing the data from a C# datatable would be faster than accessing from MySQL tables.

If its speed you want then anything close to the source would be the best. If its maintenance or reuse outside of that program, then MySql would be best. If the C# datatable grows too large, it has a potential to become more difficult to manage and perhaps even slower to access than if it were stored in MySQL.

So you want a table that has a single column for user id? That gets a row added to it when someone logs in? And a row removed from it when they log out? In a typical website, one does not log out as much as quit clicking links. I never pressed log out 2 hours ago, but I'm just now shown as online. You could remove old entries every minute but that would be kind of silly. And what if you need to determine if 'Bob' is online? You mean you have to use two tables just for that?

Tables are usually used for distinct entities that are related to entities in other tables (relational databases) after all. A post has one user id. With one query you can find all of the posts made by 'Bob', even though he used to be name 'xxBobSlaY3rxx', because the id didn't change. Just his username in the user table did.

What I'm getting at is that being online is a property of a user. Users do not have multiple 'onlines', and 'onlines' certainly don't have multiple users. What you would want to do is add a column for 'last page visit', and SELECT for users which have a last page visit withing 10 minutes of the current time, for example. Or if it's a more stateful meaning of 'online', like being connected to a game server, make it a bool instead.


Databases are for storing data such that:
1. You can store a lot of it, available when needed

World of Warcraft has millions of accounts, and multiple characters per account. If you cancel an account, you stop getting bills, but all of the data stays there indefinitely to make it easier to come back. The forums might have years of searchable content, but most of the posts will not be viewed today, tomorrow, or ever again.

2. You can get to it

The data is in one place and accesible from any language or system that can communicate with it. You can pick about any language and there will be a libraries for the major databases. The World of Warcraft auth server has to check your password. The realm servers have to load your character data. The billing software has to get your acct. status, type, cc#, etc. The account management pages have to show account history, handle password changes, character transfers, and so on. The forums have to access the posts, but have to join with other tables to allow login, display of in-game rank/status, character names and pictures. Just in that example, a database allows Java servlets, in-house servers, and some random billing system to work together, with the ability to add in more tables and data as time goes on and features have to get added and others get replaced.

3. Fast

Databases are very good at what they are intended to do. You can query to find or modify exactly the data you want, even if pulling in multiple tables with a large number of rows and various constraints. Thinking ahead as far as structure certainly helps, but as time goes on different queries will be needed, so people need databases to guarantee that they can get the tiny fraction of the data that they need to their app for processing within a reasonable amount of time. It is the job of the database to find everything that you need to know about user X among the 10,000,000 others, but it is the job of the programmer to use it afterwards. Don't keep asking the database for stuff that you should just hold on to instead. In WoW, the realm servers will get all of the info about your character when you log in and is from then on responsible for it. A character can only be in one place at a time -- it is only when you log out that the server has reason to forget about it, and tell the database about that shiny new sword you found in the meantime. Yes, databases are fast, but ram is a hell of a lot faster. It will, however, immediately update your login state immediately, as this is important to other systems. But login state for a given user doesn't change every fraction of a second either. Don't store purely temporary stuff that has no meaning outside of your single app. You probably can't optimize your code better than the code in the database, but it is dealing with much more data and takes longer to access due to little things like the speed of light. It's slower.