Extacting Time Series with MSSQL
Validate the speeds and feeds I have used by examining the rate at which records are written to the database. For example, looking at the Weigh_Event_Log table, I construct the following query
select convert(varchar, DateTime, 112), count(1) from dbo.Event_Log where DateTime > getdate() - 360 group by convert(varchar, DateTime, 112) order by convert(varchar, DateTime, 112) desc
Note - see http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx for conversion Style Codes
And it produces the following output
Date # Log Entries 20110921 24026 20110920 32063 20110919 27166 20110918 12946 20110917 5588 20110916 26410 20110915 32061 20110914 31566 20110913 33670 20110912 27451 ... 20110725 25688 20110724 11696 20110723 5884
This shows that an upper limit for a day is around 32,000. So pick one of those days - 20110920 - and do a further drill down using the following query:
select convert(varchar, DateTime, 100), count(1) from dbo.Event_Log where Date_Time >= '2011-09-20' and Date_Time < '2011-09-21' group by convert(varchar, DateTime, 100) order by convert(varchar, DateTime, 100) desc
With some simple data manipulation and a bit of help from Excel I get the following minute by minute distribution graph - which incidentally does indeed support the thesis that the peak rate is a bit over one per second (up to 75 per minute).
In this way create a picture of processing rate of events.