Fast ad hoc on heavy insert table

Last Post 30 Jan 2009 07:39 AM by Krypto. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Krypto
New Member
New Member

--
30 Jan 2009 07:39 AM
I have a table (logger2) that is 16 columns wide, and has 1000 (random, unique) inserts per second.

They want to be able to return ad-hoc queries on this table in less then one minute.

Here' some examples of the ad-hoc queries

select * from logger2 where KeyId = '084e0348-bf56-460a-8067-e243689b6bc7'
select * from logger2 where (severity=2 or severity=1)
select * from logger2 where username = ‘joe’

I know I need to stop them from using the '*' and use the column.

What would be a good way to index this table for 'ad-hocs' so I get the fastest results ? Can it even be done?

Could auto parameterization or sp_executesql be of use to me on this?


Acceptable Use Policy
---