private void StartInstanceDownload()
{
List <Task> newTasks = new List <Task>();
//foreach (SqlDownloadType current in Enum.GetValues(typeof(SqlDownloadType)).Cast<SqlDownloadType>().Where(x => !runningTasks.ContainsKey(x)))
foreach (var key_value in LastRunTasks.OrderBy(t => t.Value))
{
SqlDownloadType download = key_value.Key;
if (DontDownloadTypes.Contains(download) || runningTasks.ContainsKey(key_value.Key))
{
continue;
}
Task newTask = new Task(() => DownloadEntities(download));
if (runningTasks.AddOrUpdate(download, newTask, (x, y) => y) == newTask)
{
runningDownloaders.TryAdd(newTask, this);
newTask.ContinueWith(task =>
{
Task checkTask;
SqlInterface checkInterface;
runningTasks.TryRemove(download, out checkTask);
LastRunTasks.AddOrUpdate(download, DateTime.UtcNow, (x, y) => y);
runningDownloaders.TryRemove(task, out checkInterface);
});
newTasks.Add(newTask);
}
}
newTasks.ForEach(x => x.Start());
}
private int GetDegreeOfParallelism(SqlDownloadType type)
{
switch (type)
{
case SqlDownloadType.Datasource:
return(4);
case SqlDownloadType.DatasourceMention:
case SqlDownloadType.Tag:
case SqlDownloadType.TagAssociation:
case SqlDownloadType.Theme:
case SqlDownloadType.ThemeMention:
return(1);
case SqlDownloadType.Mention:
return(4);
default:
return(1);
}
}
private string GetQueryString(SqlDownloadType downloadType, int mod, int rem, DateTime updated, long maxid)
{
//Tuple<DateTime,long> cutoff;
//lastUpdates.TryGetValue(downloadType, out cutoff);
//var updated = cutoff == null ? default(DateTime) : cutoff.Item1;
//var maxid = cutoff == null ? default(long) : cutoff.Item2;
bool isDeltaUpdate = updated > default(DateTime) || maxid > default(long);
string query = null;
switch (downloadType)
{
case SqlDownloadType.Mention:
query = string.Format("SELECT Id, OccurredOn, UpdatedOn, Name, Evaluation, UniqueId, Description, CreatedOn, Type, Username, Influence, Followers, KloutScore, ChildCount FROM Instances with(NOLOCK) WHERE UniqueId IS NOT NULL AND OccurredOn IS NOT NULL AND Id%{0}={1}", mod, rem);
if (isDeltaUpdate) query += " AND UpdatedOn IS NOT NULL AND UpdatedOn >= @updated";
query += " ORDER BY UpdatedOn";
break;
case SqlDownloadType.TagAssociation:
query = string.Format("SELECT Id, TagId, InstanceId, IsDisabled, UpdatedOn FROM TagAssociations with(NOLOCK) WHERE InstanceId%{0}={1}", mod, rem);
if (isDeltaUpdate) query += " AND UpdatedOn IS NOT NULL AND UpdatedOn >= @updated";
query += " ORDER BY UpdatedOn";
break;
case SqlDownloadType.Tag:
query = string.Format("SELECT Id, Name, CreatedOn FROM Tags with(NOLOCK)");
if (isDeltaUpdate) query += " WHERE CreatedOn >= @updated";
query += " ORDER BY CreatedOn";
break;
case SqlDownloadType.Datasource:
query = string.Format("SELECT Id, Name, CreatedOn, Value FROM DownloadItems with(NOLOCK) WHERE IsDisabled = 0 AND IsVisible=1");// AND IsVisible = 1");
if (isDeltaUpdate) query += " AND CreatedOn >= @updated";
query += " ORDER BY CreatedOn";
break;
case SqlDownloadType.DatasourceMention:
query = string.Format("SELECT Id, DownloadItemId, MentionId, IsDisabled, UpdatedOn FROM DownloadItemMentions with(NOLOCK) WHERE MentionId%{0}={1}", mod, rem);
if (isDeltaUpdate) query += " AND UpdatedOn >= @updated";
query += " ORDER BY UpdatedOn";
break;
case SqlDownloadType.Theme:
query = string.Format("SELECT Id, Text FROM Phrases with(NOLOCK)");
if (isDeltaUpdate) query += " WHERE Id >= @id";
query += " ORDER BY Id";
break;
case SqlDownloadType.ThemeMention:
query = string.Format("SELECT Id, InstanceId, PhraseId, IsDisabled, UpdatedOn FROM PhraseInstances with(NOLOCK) WHERE InstanceId%{0}={1}", mod, rem);
if (isDeltaUpdate) query += " WHERE UpdatedOn >= @updated";
query += " ORDER BY UpdatedOn";
break;
}
return query;
}
private int GetDegreeOfParallelism(SqlDownloadType type)
{
switch (type)
{
case SqlDownloadType.Datasource:
return 4;
case SqlDownloadType.DatasourceMention:
case SqlDownloadType.Tag:
case SqlDownloadType.TagAssociation:
case SqlDownloadType.Theme:
case SqlDownloadType.ThemeMention:
return 1;
case SqlDownloadType.Mention:
return 4;
default:
return 1;
}
}
private void DownloadEntities(SqlDownloadType downloadType)
{
//if (downloadType != SqlDownloadType.Mention)
// return;
Tuple<DateTime, long> cutoff;
lastUpdates.TryGetValue(downloadType, out cutoff);
var updatedCutoff = cutoff != null ? cutoff.Item1 : default(DateTime);
var idCutoff = cutoff!=null ? cutoff.Item2 : default(long);
Trace.WriteLine("Starting to load with Mod:" + Mod + " Rem:" + Rem + " Domain:" + Name + " Type:" + downloadType);
Stopwatch sw = new Stopwatch();
sw.Start();
//int ii = 0;
//object iil = new object();
int total = 0;
int DegreeOfParallelism = GetDegreeOfParallelism(downloadType);
System.Threading.ManualResetEvent eventError = new System.Threading.ManualResetEvent(false);
Enumerable.Range(0, DegreeOfParallelism).AsParallel().ForAll(p =>
{
Stopwatch sw2 = new Stopwatch();
sw2.Start();
var localUpdatedCutoff = updatedCutoff;
var localIdCutoff = idCutoff;
//int i = 0;
int current = 0;
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
int mod = Mod * DegreeOfParallelism;
int rem = Rem * DegreeOfParallelism + p;
//PrintLoadingMetrics(Name, downloadType.ToString(), sw.Elapsed, TimeSpan.MinValue, i, 0, mod, rem);
string query = GetQueryString(downloadType, mod, rem, localUpdatedCutoff, localIdCutoff);
//var iii = ii;
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandTimeout = 5 * 60;
if (localUpdatedCutoff > default(DateTime))
{
command.Parameters.AddWithValue("@updated", localUpdatedCutoff);
}
if (localIdCutoff > default(long))
{
command.Parameters.AddWithValue("@id", localIdCutoff);
}
using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
switch (downloadType)
{
case SqlDownloadType.Mention:
var mention = GetMentionFromDataReader(dr);
if (mention.UpdatedOnTicks > localUpdatedCutoff.Ticks) localUpdatedCutoff = mention.UpdatedOn;
AddMention(mention);
//var anothermMention = GetMentionFromDataReader(dr);
//anothermMention.Id = anothermMention.Id + 1000000000;
//if (anothermMention.UpdatedOnTicks > localUpdatedCutoff.Ticks) localUpdatedCutoff = anothermMention.UpdatedOn;
//AddMention(anothermMention);
break;
case SqlDownloadType.TagAssociation:
var tagAssociation = GetTagAssociationFromDataReader(dr);
if (tagAssociation.UpdatedOn > localUpdatedCutoff) localUpdatedCutoff = tagAssociation.UpdatedOn;
AddTagAssociation(tagAssociation);
break;
case SqlDownloadType.Tag:
var tag = GetTagFromDataReader(dr);
if (tag.CreatedOn > localUpdatedCutoff) localUpdatedCutoff = tag.CreatedOn;
AddTag(tag);
break;
case SqlDownloadType.Datasource:
var datasource = GetDatasourceFromDataReader(dr);
if (datasource.CreatedOn > localUpdatedCutoff) localUpdatedCutoff = datasource.CreatedOn;
AddDatasource(datasource);
break;
case SqlDownloadType.DatasourceMention:
var datasourceMention = GetDatasourceMentionFromDataReader(dr);
if (datasourceMention.UpdatedOn > localUpdatedCutoff) localUpdatedCutoff = datasourceMention.UpdatedOn;
AddDatasourceMention(datasourceMention);
break;
case SqlDownloadType.Theme:
var theme = GetThemeFromDataReader(dr);
if (theme.Id > localIdCutoff) localIdCutoff = theme.Id;
AddTheme(theme);
break;
case SqlDownloadType.ThemeMention:
var themeMention = GetThemeMentionFromDataReader(dr);
if (themeMention.UpdatedOn > localUpdatedCutoff) localUpdatedCutoff = themeMention.UpdatedOn;
AddThemeMention(themeMention);
break;
}
current++;
System.Threading.Interlocked.Increment(ref total);
//i++;
//iii++;
if (current % 1000 == 0)
PrintLoadingMetrics(Name, downloadType.ToString(), sw.Elapsed, sw2.Elapsed, total, current, mod, rem);
if (eventError.WaitOne(0))
return;
}
PrintLoadingMetrics(Name, downloadType.ToString(), sw.Elapsed, sw2.Elapsed, total, current, mod, rem);
}
}
//lock (iil)
//{
// ii += i;
//}
}
}
catch (SqlException sql)
{
Trace.WriteLine("ERROR TALKING TO SQL: " + sql.Message);
eventError.Set();
}
catch (InvalidOperationException ix)
{
Trace.WriteLine("ERROR: " + ix.Message);
eventError.Set();
}
catch (Exception ex)
{
Trace.WriteLine("ERROR: " + ex.Message);
eventError.Set();
}
sw.Stop();
var newCutoff = new Tuple<DateTime, long>(localUpdatedCutoff, localIdCutoff);
lastUpdates.AddOrUpdate(downloadType, newCutoff, (x, y) => y != null && (y.Item1 > localUpdatedCutoff || y.Item2 > localIdCutoff) ? y : newCutoff);
});
sw.Stop();
if (eventError.WaitOne(0))
{
var newCutoff = new Tuple<DateTime, long>(updatedCutoff, idCutoff);
lastUpdates.AddOrUpdate(downloadType, newCutoff, (x, y) => y);
}
//var newCutoff = new Tuple<DateTime, long>(updatedCutoff, idCutoff);
//lastUpdates.AddOrUpdate(downloadType, newCutoff, (x, y) => y != null && (y.Item1 > updatedCutoff || y.Item2 > idCutoff) ? y : newCutoff);
}
private string GetQueryString(SqlDownloadType downloadType, int mod, int rem, DateTime updated, long maxid)
{
//Tuple<DateTime,long> cutoff;
//lastUpdates.TryGetValue(downloadType, out cutoff);
//var updated = cutoff == null ? default(DateTime) : cutoff.Item1;
//var maxid = cutoff == null ? default(long) : cutoff.Item2;
bool isDeltaUpdate = updated > default(DateTime) || maxid > default(long);
string query = null;
switch (downloadType)
{
case SqlDownloadType.Mention:
query = string.Format("SELECT Id, OccurredOn, UpdatedOn, Name, Evaluation, UniqueId, Description, CreatedOn, Type, Username, Influence, Followers, KloutScore, ChildCount FROM Instances with(NOLOCK) WHERE UniqueId IS NOT NULL AND OccurredOn IS NOT NULL AND Id%{0}={1}", mod, rem);
if (isDeltaUpdate)
{
query += " AND UpdatedOn IS NOT NULL AND UpdatedOn >= @updated";
}
query += " ORDER BY UpdatedOn";
break;
case SqlDownloadType.TagAssociation:
query = string.Format("SELECT Id, TagId, InstanceId, IsDisabled, UpdatedOn FROM TagAssociations with(NOLOCK) WHERE InstanceId%{0}={1}", mod, rem);
if (isDeltaUpdate)
{
query += " AND UpdatedOn IS NOT NULL AND UpdatedOn >= @updated";
}
query += " ORDER BY UpdatedOn";
break;
case SqlDownloadType.Tag:
query = string.Format("SELECT Id, Name, CreatedOn FROM Tags with(NOLOCK)");
if (isDeltaUpdate)
{
query += " WHERE CreatedOn >= @updated";
}
query += " ORDER BY CreatedOn";
break;
case SqlDownloadType.Datasource:
query = string.Format("SELECT Id, Name, CreatedOn, Value FROM DownloadItems with(NOLOCK) WHERE IsDisabled = 0 AND IsVisible=1"); // AND IsVisible = 1");
if (isDeltaUpdate)
{
query += " AND CreatedOn >= @updated";
}
query += " ORDER BY CreatedOn";
break;
case SqlDownloadType.DatasourceMention:
query = string.Format("SELECT Id, DownloadItemId, MentionId, IsDisabled, UpdatedOn FROM DownloadItemMentions with(NOLOCK) WHERE MentionId%{0}={1}", mod, rem);
if (isDeltaUpdate)
{
query += " AND UpdatedOn >= @updated";
}
query += " ORDER BY UpdatedOn";
break;
case SqlDownloadType.Theme:
query = string.Format("SELECT Id, Text FROM Phrases with(NOLOCK)");
if (isDeltaUpdate)
{
query += " WHERE Id >= @id";
}
query += " ORDER BY Id";
break;
case SqlDownloadType.ThemeMention:
query = string.Format("SELECT Id, InstanceId, PhraseId, IsDisabled, UpdatedOn FROM PhraseInstances with(NOLOCK) WHERE InstanceId%{0}={1}", mod, rem);
if (isDeltaUpdate)
{
query += " WHERE UpdatedOn >= @updated";
}
query += " ORDER BY UpdatedOn";
break;
}
return(query);
}
private void DownloadEntities(SqlDownloadType downloadType)
{
//if (downloadType != SqlDownloadType.Mention)
// return;
Tuple <DateTime, long> cutoff;
lastUpdates.TryGetValue(downloadType, out cutoff);
var updatedCutoff = cutoff != null ? cutoff.Item1 : default(DateTime);
var idCutoff = cutoff != null ? cutoff.Item2 : default(long);
Trace.WriteLine("Starting to load with Mod:" + Mod + " Rem:" + Rem + " Domain:" + Name + " Type:" + downloadType);
Stopwatch sw = new Stopwatch();
sw.Start();
//int ii = 0;
//object iil = new object();
int total = 0;
int DegreeOfParallelism = GetDegreeOfParallelism(downloadType);
System.Threading.ManualResetEvent eventError = new System.Threading.ManualResetEvent(false);
Enumerable.Range(0, DegreeOfParallelism).AsParallel().ForAll(p =>
{
Stopwatch sw2 = new Stopwatch();
sw2.Start();
var localUpdatedCutoff = updatedCutoff;
var localIdCutoff = idCutoff;
//int i = 0;
int current = 0;
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
int mod = Mod * DegreeOfParallelism;
int rem = Rem * DegreeOfParallelism + p;
//PrintLoadingMetrics(Name, downloadType.ToString(), sw.Elapsed, TimeSpan.MinValue, i, 0, mod, rem);
string query = GetQueryString(downloadType, mod, rem, localUpdatedCutoff, localIdCutoff);
//var iii = ii;
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandTimeout = 5 * 60;
if (localUpdatedCutoff > default(DateTime))
{
command.Parameters.AddWithValue("@updated", localUpdatedCutoff);
}
if (localIdCutoff > default(long))
{
command.Parameters.AddWithValue("@id", localIdCutoff);
}
using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
switch (downloadType)
{
case SqlDownloadType.Mention:
var mention = GetMentionFromDataReader(dr);
if (mention.UpdatedOnTicks > localUpdatedCutoff.Ticks)
{
localUpdatedCutoff = mention.UpdatedOn;
}
AddMention(mention);
//var anothermMention = GetMentionFromDataReader(dr);
//anothermMention.Id = anothermMention.Id + 1000000000;
//if (anothermMention.UpdatedOnTicks > localUpdatedCutoff.Ticks) localUpdatedCutoff = anothermMention.UpdatedOn;
//AddMention(anothermMention);
break;
case SqlDownloadType.TagAssociation:
var tagAssociation = GetTagAssociationFromDataReader(dr);
if (tagAssociation.UpdatedOn > localUpdatedCutoff)
{
localUpdatedCutoff = tagAssociation.UpdatedOn;
}
AddTagAssociation(tagAssociation);
break;
case SqlDownloadType.Tag:
var tag = GetTagFromDataReader(dr);
if (tag.CreatedOn > localUpdatedCutoff)
{
localUpdatedCutoff = tag.CreatedOn;
}
AddTag(tag);
break;
case SqlDownloadType.Datasource:
var datasource = GetDatasourceFromDataReader(dr);
if (datasource.CreatedOn > localUpdatedCutoff)
{
localUpdatedCutoff = datasource.CreatedOn;
}
AddDatasource(datasource);
break;
case SqlDownloadType.DatasourceMention:
var datasourceMention = GetDatasourceMentionFromDataReader(dr);
if (datasourceMention.UpdatedOn > localUpdatedCutoff)
{
localUpdatedCutoff = datasourceMention.UpdatedOn;
}
AddDatasourceMention(datasourceMention);
break;
case SqlDownloadType.Theme:
var theme = GetThemeFromDataReader(dr);
if (theme.Id > localIdCutoff)
{
localIdCutoff = theme.Id;
}
AddTheme(theme);
break;
case SqlDownloadType.ThemeMention:
var themeMention = GetThemeMentionFromDataReader(dr);
if (themeMention.UpdatedOn > localUpdatedCutoff)
{
localUpdatedCutoff = themeMention.UpdatedOn;
}
AddThemeMention(themeMention);
break;
}
current++;
System.Threading.Interlocked.Increment(ref total);
//i++;
//iii++;
if (current % 1000 == 0)
{
PrintLoadingMetrics(Name, downloadType.ToString(), sw.Elapsed, sw2.Elapsed, total, current, mod, rem);
}
if (eventError.WaitOne(0))
{
return;
}
}
PrintLoadingMetrics(Name, downloadType.ToString(), sw.Elapsed, sw2.Elapsed, total, current, mod, rem);
}
}
//lock (iil)
//{
// ii += i;
//}
}
}
catch (SqlException sql)
{
Trace.WriteLine("ERROR TALKING TO SQL: " + sql.Message);
eventError.Set();
}
catch (InvalidOperationException ix)
{
Trace.WriteLine("ERROR: " + ix.Message);
eventError.Set();
}
catch (Exception ex)
{
Trace.WriteLine("ERROR: " + ex.Message);
eventError.Set();
}
sw.Stop();
var newCutoff = new Tuple <DateTime, long>(localUpdatedCutoff, localIdCutoff);
lastUpdates.AddOrUpdate(downloadType, newCutoff, (x, y) => y != null && (y.Item1 > localUpdatedCutoff || y.Item2 > localIdCutoff) ? y : newCutoff);
});
sw.Stop();
if (eventError.WaitOne(0))
{
var newCutoff = new Tuple <DateTime, long>(updatedCutoff, idCutoff);
lastUpdates.AddOrUpdate(downloadType, newCutoff, (x, y) => y);
}
//var newCutoff = new Tuple<DateTime, long>(updatedCutoff, idCutoff);
//lastUpdates.AddOrUpdate(downloadType, newCutoff, (x, y) => y != null && (y.Item1 > updatedCutoff || y.Item2 > idCutoff) ? y : newCutoff);
}
