I have a table where 15,000 to 20,000 rows are being inserted every hour.
Table Schema is something like
ColumnName DataType ID BIGINT IDENTITY(1,1) Column1 INT Column2 INT Column3 DATETIME DEFAULT = GETDATE()
No row is ever update nor deleted. As you can imagine table grows huge in very short time.
The table is queried as well time to time. SELECT statements with filtering on DATETIME column and sometimes other filtering on INT column too.
To my knowledge, I have two options
- indexing columns (Datetime, some int columns) to get better performance on select statements and get a performance hit on my INSERTS
- Keep indexing minimum, Primary key Clustered index and a Datetime index and keeping the inserts very fast and get a performance hit on selects.
Another option that was suggested to me, was to create another table, Populate that table from this current table and index the living hell out of it to help all the possible select queries. Read data from this duplicated table.
Keep the original table as it is with minimum indexing, for quick inserts.
Since I am duplicating the data, I know this option violates the basic rules of normilazation, but this sounds like a good option for keeping inserts and reads as fast as possible.
The problem is how can I maintain this near real time copy of this table inside the same database.
I do not want to use any After Insert triggers as this will end up firing 15,000 to 20,000 times an hour.
What other options I have to keep this near real time copy of table in the same database??? Or maybe another approach altogether, any suggestion or pointers in the right direction are much appreciated.