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

Performance penalty for LIKE when I really mean Equal?

Hi,
I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.
I was thinking of having one parameterized SQL statement like this...
SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.
Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is th
e
equivalent of = 'OT'?
Thanks.
BBMFor prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.
ML|||If @.Type is NULL, then the following will return all rows without evaluating
the like comparison:
SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
"BBM" <bbm@.bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
> Hi,
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
> I was thinking of having one parameterized SQL statement like this...
> SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
> Thanks.
> BBM|||Thanks for the tip. I'll try this.
"JT" wrote:

> If @.Type is NULL, then the following will return all rows without evaluati
ng
> the like comparison:
> SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
>
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
>
>