Performance question

We have a server product which talks to SQL Server. One of the tables
we maintain is a session table. So a session row is added when login
occurs, it is looked up on subsequent requests and at certain points
may be updated to reflect recent activity (meaning the session's
lifetime is extended).
So what we essentially have is a fairly dynamic table of data whose
value is limited to however long the server is running. If the server
or DBMS machine is restarted, there is no particular need to retain
the contents of this table.
While this is currently a standard database table, I wondered whether
might be a more performant way to hold this information. For
instance, I note in Kalen Delaney's "Inside Microsoft SQL Server 2000"
that because of reduced logging requirements, "...data modification
operations on tables in tempdb can be up to four times faster than the
same operations in other databases." (p.310)
However reading more on the web I get the impression that some feel
tempdb operations can be less performant - the fear seems to relate to
contention.
Can anyone offer suggestions, particularly with regard to the scenario
I outlined above?
Thanks in advance.
Ed BarrettI do wonder though what happens if a client connection bombs out for some
reason. For example, if the client becomes disconnected in a non-proper
manner, e.g., network wire disconnected, blue screen. What happens if that
occurrs while you're updating this temp table? Will you get bad data? I
assume you already know to keep your transactions short and to update and
access the tables always in the same order whereby you can reduce the
probablility of contention.
Perhaps if you're looking for performance you can look to hardware. Since
high IOs are important, have you considered moving your special database (or
TEMPDB if you so choose) to an array of numerous spindles? If it's
available to you, go with a vacant BUS, private RAID CARD, and new drives
(RAID 10 recommended) for the best IO you can achieve. Testing of course is
recommended, but I am sure you know that.
Moving TEMPDB to its own RAID array is always recommended anyway.
hth
Eric
"Ed Barrett" <ebarrett@.metastorm.com> wrote in message
news:4026318.0310200720.21613799@.posting.google.com...
> We have a server product which talks to SQL Server. One of the tables
> we maintain is a session table. So a session row is added when login
> occurs, it is looked up on subsequent requests and at certain points
> may be updated to reflect recent activity (meaning the session's
> lifetime is extended).
> So what we essentially have is a fairly dynamic table of data whose
> value is limited to however long the server is running. If the server
> or DBMS machine is restarted, there is no particular need to retain
> the contents of this table.
> While this is currently a standard database table, I wondered whether
> might be a more performant way to hold this information. For
> instance, I note in Kalen Delaney's "Inside Microsoft SQL Server 2000"
> that because of reduced logging requirements, "...data modification
> operations on tables in tempdb can be up to four times faster than the
> same operations in other databases." (p.310)
> However reading more on the web I get the impression that some feel
> tempdb operations can be less performant - the fear seems to relate to
> contention.
> Can anyone offer suggestions, particularly with regard to the scenario
> I outlined above?
> Thanks in advance.
> Ed Barrett