View source for 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

<pre>
  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
</pre>

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 


<pre>
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
</pre>

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:

<pre>
  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
</pre>

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.

[[Category:Performance]]

[[Category:SqlServer]]