I am trying to come up with a good way to deal withthe results of statistical simulations from a dba point of view. We generate about 500 million rows per day, most of which are "garbage" (i.e. the results are seen and discarded as not something we look for) and some need to be preserved. Dealing with them outside of partioning is hard.

Data is currently MOSTLY in a 3 table hierarchy (trade--order--update) with a trade having multiple orders which get multiple updates each. There is a 4th table (parameter) that contains the parameter for every simulation. This is small and unproblematic though.

We right now write the data to 3 staging tables and analyze there - temporary solution.

I would like some people to review this idea.

  • Partition the staging tables with x "buckets". A simulation assigns a bucket (smallint) ad then writes into this bucket. This allows fast deletion of a simulation. AS we only run about 100 simulations per week, a 1000-20000 partition set on the tables is enough to keep the data as long as we need (initial review).

  • When data is ok, we move it from the staging (via stored procedure) into final data warehosue tables. Again, we need to partition them, and we will use a similar bucket approach. As mulitple simulations will run into identical buckets (updating the data) this is a relatively small number of buckets.

Anyone done that?

The idea behind the bucket approach is that I can pregenerate the buckets and do not have to modify the partitioning function. Sadly SQL Server, contrary to Oracle, has no auto partition, otherwise I could use a simple ID field. I really try to avoid dynamically modifying the partitioning schema here. This way I can have a simple smallint "bucket id", a prepared partitioning schema and can basically assign every simulation / run a bucket id -easy to join. Any negatives?

  • 1
    This sounds reasonable. You could create some metadata tables/stored procs that manages all the partitioning stuff. Seems pretty straight-forward.
    – Jon Seigel
    Commented Jul 5, 2013 at 13:38
  • That is the idea - for every sim / load there is a field with the assigned bucket ID - and there is a filtered unique index (filter not null). Too bad there is no truncate table partition - dealing with an empty staging table is painfull when you can not guarantee only one delete happens at a specific time ;(
    – TomTom
    Commented Jul 5, 2013 at 13:40
  • 1
    Yes, and that sadly requires and empty table - which i either create dynamically with a random name or I have a problem with multiple "truncates" at the same time. Hole in SQL Server's concepts, if you ask me ;(
    – TomTom
    Commented Jul 5, 2013 at 16:59
  • 1
    There's nothing wrong with using a random name. If you're worried about name collisions, do an existence check in a loop until a name is free, or use a GUID so it's incredibly unlikely to collide with another name for the fraction of a second multiple tables will exist simultaneously, or do both.
    – Jon Seigel
    Commented Jul 5, 2013 at 17:07
  • 1
    Actually the good thing for me is that we wont do cleanup interactive now - design change. We will run a cleanup sp once per hour. That way I only need one (static) empty table. Truncate it, swap, truncate it - fast (ok, redundant). Sp gets called hourly and wipes all buckets that are falling out of use ;)
    – TomTom
    Commented Jul 5, 2013 at 17:27

2 Answers 2


We were doing something not unlike this with clickstream data. We unplugged SQLServer and plugged in Vertica (a columnar analytics ANSI-SQL compliant RDBMS)...we've never looked back. Multi-minute queries dropped to millsecond queries, and data loads dropped from hours to seconds. If/when you start outgrowing it, add more nodes and rebalance online. Very nicely done product.

The Community Edition is free for 3 nodes and 1Tb of data (which gets compressed a LOT more than you would expect, so 1Tb is quite a lot of data), and the commercial version (unlimited nodes) is about half the cost of SQLServer EE for TB-size data (it's licensed by data size). You might want to give it a look. ;-)

Btw, I have no affiliation with Vertica or HP, I'm just a very pleased customer/data architect.

Cheers, Dave Sisk

  • How are things with deletions? Because most of this data is not really usefull and NEEDS to be deleted. Adding rows is not an answer in this case.
    – TomTom
    Commented Oct 22, 2013 at 6:45

Vertica does not delete the data ! it marks it and the data is deleted when merging is run. just for you to understand better Vertica does not overwrite the datafile it creates, so small deletes on big tables are not that good. Sure you can make use of advanced projections that are tunned for delete operation but this depends as well. Best solution is for you to create and load your data into partitions and when done processing drop the partition (that will be a physical drop so is very fast). Don't use Vertica for high transactional environments.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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