SQL Server Performance

Where to store the data in actual or temp table??

Discussion in 'SQL Server 2005 General DBA Questions' started by KhushbuVK, May 22, 2008.

  1. KhushbuVK New Member

    • we have developed a code in .net framework and the data resides in SQL 2000. There are 7 pages in the application, we need to save the data of every page if the user does hit the save button on the page. We wish to give facility that on last page after hitting save button all the data of 7 pages should be saved. So in this case wher we can save our temp data in actual table or temp table. I have other option of storing them in temp variable what do u suggest?
  2. acki4711 Member

    On prog start you read the data for all 7 pages from sql server into prog variables.
    If user hits save on one page <> 7 you update only the data shown in this particular page.
    If user hits save on page 7 you update all data.
    The only problem your have to be aware of is other users changing the data your actualy working on (concurrency).
    This can be handled by working with a "last updated" field on the record (needs to be checked before updating)
    IMHO this is not the right place to post such a question because it has not really somthing to do with sql server.
    You might get better support in a developer forum.
  3. Kewin New Member

    The question does have som merit with SQL Server, since we do have some tools to use for things like optimistic concurrency and such.
    (timestamp datatype)
    We can also advice on things like transaction design etc.
    Hidden in this question are things like 'What to save and where to save it'?
    Should the data be resilient or not? If so, for how long? And so on.
    I can sense quite a few design issues here that isn't for the 'developer-tier' only.
    It's also about how to best use the database for all purposes.
  4. davidfarr Member

    #temp database tables and temporary variables have an advantage in that they maintain session state per user for as long as the connection remains open. The disadvantage is that they are cleared and terminated when the user closes his connection and/or closes the application.
    How long are you required to store the saved pages for? Sometimes a user's connection will close due to a technical fault or user mistake, and the user will perhaps be very dissapointed that all his saved pages are lost. If it were my application; I would store the pages as permanent tables in the application database, using User ID's, GUID and Date stamp values to identify the user and the object state, with periodic maintenance to clear old tables. In this way, users can resume their work from the last point of save even if they had to restart the client application.
  5. KhushbuVK New Member

    where wud ur permanent table reside in tempdb or master db. Also can we use temp variable or table varb here?
  6. davidfarr Member

    You should not store them in any of the system databases. You should create a new user database with any name that you choose. The table names that you use should probably be named according to the application user unique ID, such as "tblUser2345, tblUser4575, etc" , so that you can identify which tables hold the saved pages from each user that uses the application.
  7. KhushbuVK New Member

    If there are 10000 of users will it be feasible to store data in user db ?
  8. davidfarr Member

    For that many users, it is probably better to create a single permanent database table in a user database and store all users saved pages in it, with data fields to identify each user's pages and session state.
    Your original question in your first post was whether to use temporary variables, temporary tables or permanent tables. If you have 10000 concurrent users, and all of them are saving pages, then this large amount of data will either need to be saved in #temp tables (stored in tempdb), or permanent tables stored in a user DB. In either case, a lot of data will be created and a lot of space will be used. If you have the disk space available, then it is feasible. If the data quantity is too large then you will not be able to use either temporary or permanent tables, since both methods require the same amount available space.
    In my previous post I asked a question that you did not answer: How long are you required to store the pages for? During one single workday you probably do not have all 10000 users and so you probably do not need all 10000 saved user pages at the same time. You will only need to save pages for users that have logged into the application per day. If you only need the saved pages for one or two days, then you can easily run a scheduled maintenance task to drop all user tables or user records older than 2 days, to reduce the number of tables or records in the user database and maintain a managable size.

Share This Page