public void PreCalculateData(DateTime date)
{
var requests = _backend.GetRequestStatisticsForAllPages(date);
using (var conn = new SqlConnection(_connectionString)) {
var cmd = conn.CreateCommand();
cmd.CommandText = "insert into PreCalculatedPagesStatistics(Date,PageId,Count,Median,Mean,Sum) select Convert(Date,@Date),@PageId,@Count,@Median,@Mean,@Sum where not exists(select * from PreCalculatedPagesStatistics pcps where pcps.Date=@Date and pcps.PageId=@PageId)"; //where not exists for optimistic concurrency
cmd.Parameters.Add("Date", SqlDbType.Date);
cmd.Parameters.Add("PageId", SqlDbType.Int);
cmd.Parameters.Add("Count", SqlDbType.Int);
cmd.Parameters.Add("Median", SqlDbType.Int);
cmd.Parameters.Add("Mean", SqlDbType.Int);
cmd.Parameters.Add("Sum", SqlDbType.Int);
cmd.Parameters["Date"].Value = date;
conn.Open();
InsertPageInCache(cmd, AllPagesPageId, requests.StatisticsForAllPages.Count, requests.StatisticsForAllPages.Median, requests.StatisticsForAllPages.Mean, requests.StatisticsForAllPages.Sum);
foreach (var page in requests.PageStatistics)
{
InsertPageInCache(cmd, _pageIdProvider.GetPageId(page.PageName), page.Count, page.Median, page.Mean, page.Sum);;
}
}
}
public Handler.PerformanceData.DataTypes.PageStatisticsTrend GetHourlyTrend(DateTime forDate, string forPage)
{
using (var conn = new SqlConnection(_connectionString)) {
var cmd = conn.CreateCommand();
cmd.CommandText = "select DATEPART(hh,Timestamp) Hour,Duration from Requests where Timestamp>=@From and Timestamp<@Till";
cmd.Parameters.Add(new SqlParameter("From", forDate));
cmd.Parameters.Add(new SqlParameter("Till", forDate.AddDays(1)));
if (forPage != null)
{
cmd.CommandText += " and PageId=@PageId";
cmd.Parameters.Add(new SqlParameter("PageId", _pageIdProvider.GetPageId(forPage)));
}
var res = new Dictionary <int, List <int> >();
conn.Open();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
var hour = (int)reader["Hour"];
var duration = (int)reader["Duration"];
if (!res.ContainsKey(hour))
{
res[hour] = new List <int>();
}
res[hour].Add(duration);
}
return(PageStatisticsTrend.HourlyFromHourDataDictionary(forDate, res));
}
}
}
public Handler.PerformanceData.DataTypes.PageDurationDistributionHistogram GetPageDistribution(DateTime forDate, string forPage)
{
using (var connection = new SqlConnection(_connectionString)) {
var cmd = connection.CreateCommand();
cmd.CommandText = "select Duration from Requests where Timestamp>=@From and Timestamp<@Till";
if (forPage != null)
{
cmd.CommandText += " and PageId=@PageId";
cmd.Parameters.Add(new SqlParameter("PageId", _pageIdProvider.GetPageId(forPage)));
}
cmd.Parameters.Add(new SqlParameter("From", forDate));
cmd.Parameters.Add(new SqlParameter("Till", forDate.AddDays(1)));
var durations = new List <int>();
connection.Open();
using (var rdr = cmd.ExecuteReader()) {
while (rdr.Read())
{
//var pageId = (int)rdr["PageId"];
//if(!perPage.ContainsKey(pageId)) {
// perPage[pageId]= new List<int>();
//}
durations.Add((int)rdr["Duration"]);
}
}
return(PageDurationDistributionHistogram.FromDistribution(durations.ToArray()));
}
}
private int GetPageId(string forPage)
{
if (forPage == null)
{
return(AllPagesPageId);
}
return(_pageIdProvider.GetPageId(forPage));
}
private int GetPageId(string forPage)
{
int pageIdValue = AllPagesPageId;
if (forPage != null)
{
pageIdValue = _pageIdProvider.GetPageId(forPage);
}
return(pageIdValue);
}
