Hi All,
In our web app, we are creating session table in database to store temporary data. So the temp table will be created and destroyed for every user. I have some 300 users for this web app. So for every user these table will be created and destroyed.
i heard that this way of design is not good due to performance issues. I am using MS Sql server 2005. Is there any way to store a result set temporarily without creating any table.
Please suggest me some solution. Thanks.
I think, a solution at GenerateData is what you are looking for. You can create test/sample databases their and delete them when needed.
Depending on what you're actually doing (and whether you can refactor it) it may be more appropriate to use table variables which are highly performant in general.
There is a question of whther the DB is really an appropriate place to even be trying to persist data sets if it's for your applications benefit - if the question isn't just academic perhaps it would be better to keep the object representation of the data in your app memory?
Either:
use a single permanent database table for all users, with a UserID column to filter on
or
just use the session handling ability of your web platform to store the info
It sounds as if you are creating and dropping permanent tables. Have you tried using real temp tables (those with table names beginning with #). OR table variables if you havea small data set. Either of these can work quite well. If you use real temp tables, you need to make sure your tempdb is sized large enough to accomodate the usual amount of users, growing tempdb can cause delays.