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

  1. indexing columns (Datetime, some int columns) to get better performance on select statements and get a performance hit on my INSERTS

OR

  1. 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.

  • What is version and edition of SQL Server here ? – Shanky Oct 9 '14 at 22:03
  • SQL Server 2008 r2, enterprise edition, (edition can vary from client to client, it will never be express though). – M.Ali Oct 9 '14 at 22:05
  • 1
    Did you tried partitioning since you have enterprise edition.I am sure you can benefit from table partitioning, read brentozar.com/archive/2012/03/… – Shanky Oct 9 '14 at 22:11
  • 1
    @Shanky thanks for the suggestion, but it seems as Partitioning will be a better option for a data warehouse where large amount of data is loaded in one go and large amount of data needs to be removed from table, As I have mentioned in my question, No data will ever be removed from this table, (going forward I think we will need to) but in my case inserts are continuous, so I really do get it how it can help me to have quick inserts and allow me to index table to have faster reads by partitioning the table. – M.Ali Oct 9 '14 at 22:43
  • 1
    Maybe you're not clear on how partitioning works. You say that your queries are often filtered on datetime ranges. If you partition on datetime, then you can achieve partition elimination. Let's say you have 1 billion rows, 10 million rows in each of 100 partitions (representing 100 months of data). Do you not see how your query will be more efficient for last month's data if it doesn't even have to consider 990 million rows? It can essentially be like having a separate table just for that month. I'd do some more research before brushing it off as "not the answer." You have Enterprise, use it! – Aaron Bertrand Oct 10 '14 at 0:21

20,000/60 = 333 records per minute is not that rate where you worry about inserts, even if they are burst. Of course it depends on your hardware, but since your table is rather huge, you need something big. So I would defenitely create 1 or 2 indexes (you can omit clustered) to speed up your queries.

At a higher insert rate you probably go with partitioning and split PAGELATCH contention between partitions. Or you could use a variation of GUID clustered primary key. There is a lot of arguing here and you may want to check Tom Kejser blog post here.

Also there is no shame if you go below 3NF. People create highly denormalized data shops just for reporting purposes. Since you have enterprise edition you could use CDC (Change Data Capture) technique and create SSIS package to update reporting table, at a time you need fresh data.

  • The average is taken over 4 weeks, there are times when 20,000 rows are being inserted just under one minute and out of office hours 1000 rows maybe in 4 hours. so speed of inserting data is one of my worries. As it can harm the application's over all performance. Also GUID is one of the worst candidates for a primary key because of two reason 1) 16 byte data 2) random value leading to unnecessary page splits and many more issues. – M.Ali Oct 9 '14 at 23:23
  • Are those 20k records inserted by concurrent processes or by a single process? With GUID page split problem exists, but 'last page' contention issue you have in case of evenly increased clustered primary key is even worse. Also page splits are leveraged by an SSD. If you are afraid of GUID you can go with partitioning. – yahor Oct 9 '14 at 23:43

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.