System.Threading.Tasks
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder
{
IntegratedSecurity = true,
MaxPoolSize = 500,
DataSource = ".",
InitialCatalog = "Local.StackExchange.Meta"
};
const string LinqPadPath = @"C:\Linqpad\lprun.exe";
const bool runSequential = false;
const int defaultThreads = 1;
const int defaultIterations = 2000;
const int toSkip = 0;
int? toRun = 50;
List runs = new List
{
new Run(@"select /* Models\Post.Answers.cs@51 */
p.* from Posts p where p.ParentId = @Id", IdQueries.Questions),
new Run(@"
select c.Id
, c.PostId
, c.[Text]
, c.CreationDate
, convert(int, isnull(c.EditCount, 0)) [EditCount]
, c.Score
, c.ApplicationId
, c.UserId [OwnerId]
, coalesce(u.DisplayName, c.UserDisplayName, 'Anonymous') [OwnerName]
, convert(nvarchar(20), u.Id) [OwnerUrl]
, convert(bit, case when u.UserTypeId = 4 then 1 else 0 end) [OwnerIsMod]
, isnull(u.Reputation, 0) [OwnerReputation]
, convert(bit, 0) [UserHasVoted]
, convert(bit, 0) [UserHasFlagged]
from ( select row_number() over ( partition by cOrdered.PostId order by isnull(cOrdered.Score, 0) desc, cOrdered.CreationDate ) [Rank]
, cOrdered.*
from PostComments cOrdered
where cOrdered.PostId in (Select p.Id From Posts p Where p.ParentId = @postId Union Select @postId)
and cOrdered.DeletionDate is null
and isnull(cOrdered.Score, 0) >= 0) [c]
left outer join Users u on c.UserId = u.Id
where c.[Rank] <= 5
order by c.CreationDate
option ( optimize for ( @postId unknown ) )", IdQueries.Questions, "postId") { Iterations = 2000 },
new Run( @"select p.*, isnull(pm.ViewCount,0) ViewCount from Posts p /* Models\Post.Selecting.cs@122 */
left join PostMetadata pm on pm.PostId = p.Id
where p.Id = @id", IdQueries.Questions),
new Run(@"Declare @Type tinyint = (2);
select p.Id /* Models\Post.RelatedQuestions.cs@114 */
, p.Title
, p.Score
, CAST(CASE
WHEN p.AcceptedAnswerId IS NULL THEN 0
ELSE 1
END AS BIT) AS HasAcceptedAnswer
, pl.Rank
, pl.CreationDate
from PostLinks pl
join Posts p on pl.RelatedPostId = p.Id
where pl.PostId = @Id
and pl.LinkTypeId = @Type
and p.DeletionDate is null", IdQueries.Questions),
new Run(@"SELECT [t0].[Id], [t0].[UserTypeId], [t0].[Reputation], [t0].[LastAccessDate], [t0].[LastDailySiteAccessDate], [t0].[LastLoginDate], [t0].[LastEmailDate], [t0].[LastLoginIP], [t0].[CreationDate], [t0].[Email], [t0].[DisplayName], [t0].[WebsiteUrl], [t0].[RealName], [t0].[Location], [t0].[Birthday], [t0].[BadgeSummary], [t0].[OptInEmail], [t0].[PreferencesRaw], [t0].[HasReplies], [t0].[TimedPenaltyDate], [t0].[DaysVisitedConsecutive], [t0].[DaysVisitedTotal], [t0].[LastModifiedDate], [t0].[IsVeteran], [t0].[ReputationToday], [t0].[ReputationWeek], [t0].[ReputationMonth], [t0].[ReputationQuarter], [t0].[ReputationYear], [t0].[ReputationSinceLastCheck], [t0].[AcceptRateAsked], [t0].[AcceptRateAccepted], [t0].[AccountId], [t0].[ProfileImageUrl], [t0].[HasAboutMeExcerpt]
FROM [dbo].[Users] AS [t0]
WHERE [t0].[Id] = @p0", IdQueries.Users, "p0"),
new Run(@"Select top 50
p.Id
, p.Title
, p.Score
, CAST(CASE WHEN p.AcceptedAnswerId IS NULL THEN 0 ELSE 1 END AS BIT) AS HasAcceptedAnswer
, pm.ViewCount
, p.AnswerCount
, p.AnswerScore
, x.LinkTypeId
, p.CreationDate
, p.LastActivityDate
From Posts p
Join PostMetadata pm On p.Id = pm.PostId
Join (select pl.RelatedPostId Id, pl.LinkTypeId
from PostLinks pl
where pl.PostId = @Id
and pl.LinkTypeId in (1, 3)
union
select pl.PostId, pl.LinkTypeId
from PostLinks pl
where pl.RelatedPostId = @Id
and pl.LinkTypeId in (1, 3)) x On p.Id = x.Id
Where p.DeletionDate is null
and p.PostTypeId = 1
Order By x.LinkTypeId Desc, ( ( log(isnull(ViewCount, 1) + 1) * 4 ) + ( ( ( isnull(AnswerCount, 1) * Score ) + 1 ) / 5 ) + ( isnull(AnswerScore, 0) ) ) Desc", IdQueries.Questions),
new Run(@"SELECT [t0].[Id], [t0].[PostTypeId], [t0].[CreationDate], [t0].[Score], [t0].[Body], [t0].[OwnerUserId], [t0].[LastEditorUserId], [t0].[LastEditDate], [t0].[LastActivityDate], [t0].[LastActivityUserId], [t0].[ParentId], [t0].[AcceptedAnswerId], [t0].[Title], [t0].[Tags], [t0].[CommunityOwnedDate], [t0].[HistorySummary], [t0].[AnswerScore], [t0].[AnswerCount], [t0].[CommentCount], [t0].[FavoriteCount], [t0].[DeletionDate], [t0].[ClosedDate], [t0].[LockedDate], [t0].[OwnerDisplayName], [t0].[LastEditorDisplayName], [t0].[BountyAmount], [t0].[BountyCloses], [t0].[BountyClosed], [t0].[LastOwnerEmailDate], [t0].[ProtectedDate], [t0].[MigrationDate], [t0].[IsAcceptedAnswer], [t0].[IsAnswered], [t0].[HasNotices], [t0].[IsFrozen] AS [IsFrozenRaw], [t0].[QualityScore], [t0].[IsClosedAsDuplicate], [t0].[LastActivityTypeId]
FROM [dbo].[Posts] AS [t0]
WHERE [t0].[Id] = @p0", IdQueries.AllPosts, "p0"),
new Run(@"SELECT /* Controllers\QuestionsController.Show.cs@318 */
MAX(CreationDate) FROM PostComments WHERE DeletionDate IS NULL AND PostId IN (SELECT Id FROM Posts WHERE Id = @Id OR ParentId = @Id)", IdQueries.Questions),
new Run(@"SELECT /* Controllers\QuestionsController.Show.cs@330 */
LastRelatedQuestionRefreshDate from PostMetadata WHERE PostId = @Id", IdQueries.Questions),
new Run(@"
SELECT /* Controllers\QuestionsController.Show.cs@324 */
MAX(DeletionDate) FROM Posts WHERE DeletionDate IS NOT NULL AND (Id = @Id OR ParentId = @Id)", IdQueries.Questions),
new Run(@"
select u.Id, /* Controllers\QuestionsController.Show.cs@496 */
u.UserTypeId,
u.DisplayName,
u.Reputation,
u.BadgeSummary,
u.Email,
u.LastLoginIP,
u.TimedPenaltyDate,
u.ProfileImageUrl,
u.HasAboutMeExcerpt,
AcceptRateAsked,
AcceptRateAccepted
from Users u
where Id in (@Id)", IdQueries.Users),
new Run(@"
Select PostTypeId,
Sum(Case When ClosedDate Is Not Null Then 1 Else 0 End) as ClosedPosts,
Sum(Case When ClosedDate Is Null And DeletionDate Is Null Then 1 Else 0 End) as OpenPosts,
Sum(Case When DeletionDate Is Not Null Then 1 Else 0 End) as DeletedPosts,
Sum(IsNull(CommentCount, 0)) Comments
From Posts
Where PostTypeId In (1,2)
Group By PostTypeId
Option(MAXDOP 4)"),
};
void Main(string[] args)
{
try
{
if (args != null && args.Length == 1)
{
int index;
if (int.TryParse(args[0], out index) && index < runs.Count)
DoRun(index);
}
else
{
var runningCount = Math.Min(toSkip + toRun.GetValueOrDefault(runs.Count), runs.Count) - toSkip;
$"{runningCount} Quer{(runningCount == 1 ? "y" : "ies")} running...".Dump();
var timers = new List(runs.Count);
for (var i = toSkip; i < toSkip + runningCount; i++)
{
var j = i;
var run = runs[j];
var procInfo = new ProcessStartInfo()
{
FileName = LinqPadPath,
Arguments = $@"""{Util.CurrentQueryPath}"" {j}",
UseShellExecute = false,
RedirectStandardOutput = true,
RedirectStandardError = true,
CreateNoWindow = true,
WindowStyle = ProcessWindowStyle.Hidden
};
var pb = new Util.ProgressBar($"Query {j}")
{
HideWhenCompleted = true
};
var timer = Stopwatch.StartNew();
timers.Add(timer);
var proc = Process.Start(procInfo);
proc.EnableRaisingEvents = true;
proc.BeginOutputReadLine();
proc.OutputDataReceived += (s, e) =>
{
if (e.Data?.Contains("workers started") == true) timer.Restart();
if (e.Data?.Contains("Done!") == true)
{
timer.Stop();
pb.Visible = false;
}
if (e.Data?.Contains(" to run") == true)
{
var val = int.Parse(e.Data.Replace(" to run", ""));
run.Iterations = val;
pb.Dump();
}
if (e.Data?.Contains(" runs complete") == true)
{
var val = int.Parse(e.Data.Replace(" runs complete", ""));
if (run.Iterations > 0)
{
pb.Fraction = (val / (double)run.Iterations.GetValueOrDefault());
pb.Caption = $"Query {j}: {val} / {run.Iterations.GetValueOrDefault()}";
}
}
};
if (runSequential) proc.WaitForExit();
}
while (timers.Any(t => t.IsRunning))
{
Util.Progress = (timers.Count(t => !t.IsRunning) / timers.Count);
Thread.Sleep(1000);
}
var totalMs = timers.Sum(t => t.ElapsedMilliseconds);
var results = new List();
$"Total Run: {totalMs:n0}ms".Dump();
for (var i = 0; i < timers.Count; i++)
{
results.Add(new Result { Id = i, Duration = timers[i].Elapsed, Run = runs[i] });
}
results.Select(r => new { r.Id, r.Run.Iterations, r.Duration.TotalMilliseconds, r.Run.Query }).Dump();
}
}
catch (Exception e)
{
$"{e}".Dump();
}
}
public class Result
{
public int Id { get; set; }
public TimeSpan Duration { get; set; }
public Run Run { get; set; }
}
private SqlConnection GetConnection => new SqlConnection(csb.ConnectionString);
List> questionIds = new List>();
private void DoRun(int id)
{
var run = runs[id];
ThreadPool.SetMinThreads(run.Threads + 2, run.Threads + 2);
$"Id: {id}".Dump();
$"Query: {run.Query}".Dump();
$"Threads: {run.Threads}".Dump();
for (var i = 0; i < run.Threads; i++)
{
questionIds.Add(new List());
}
if (!string.IsNullOrEmpty(run.IdQuery))
{
using (var conn = GetConnection)
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = run.IdQuery;
cmd.Parameters.Add("runs", SqlDbType.Int).Value = run.Iterations;
using (var dr = cmd.ExecuteReader())
{
int i = 0;
while (dr.Read() && i < run.Iterations)
{
questionIds[i % run.Threads].Add(dr.GetInt32(0));
i++;
}
$"{i} to run".Dump();
}
}
$"{questionIds.Count} queues loaded".Dump();
for (var i = 0; i < questionIds.Count; i++)
{
var j = i;
$"Queue {j}: {questionIds[j].Count} items".Dump();
}
}
else
{
$"{run.Iterations} to run".Dump();
}
var workers = new Task[run.Threads];
for (int i = 0; i < run.Threads; i++)
{
var j = i;
Task t = Task.Factory.StartNew(() => RunQueries(run, questionIds[j]), TaskCreationOptions.LongRunning);
workers[j] = t;
}
$"{workers.Length} workers started".Dump();
Task.WaitAll(workers);
$"Done!".Dump();
}
public static int procRuns;
private void RunQueries(Run run, List queue)
{
var hasIds = !string.IsNullOrEmpty(run.IdQuery);
using (var conn = GetConnection)
{
conn.Open();
using (var ru = conn.CreateCommand())
{
ru.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
ru.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = run.Query;
cmd.CommandTimeout = 600;
if (!string.IsNullOrEmpty(run.IdQuery))
{
cmd.Parameters.Add(run.IdParam, run.ParamType);
foreach (var Id in queue)
{
cmd.Parameters[run.IdParam].Value = Id;
cmd.ExecuteNonQuery();
var result = Interlocked.Increment(ref procRuns);
$"{result} runs complete".Dump();
}
}
else
{
foreach (var Id in queue) cmd.ExecuteNonQuery();
}
}
}
}
public class Run
{
public string Query { get; set; }
public string IdQuery { get; set; }
public string IdParam { get; set; }
public SqlDbType ParamType { get; set; }
public int Threads { get; set; }
public int? Iterations { get; set; }
public Run(string query, string idQuery = null, string idParam = "Id", SqlDbType paramType = SqlDbType.Int)
{
Query = query;
IdParam = idParam;
ParamType = paramType;
IdQuery = idQuery;
Threads = defaultThreads;
Iterations = defaultIterations;
}
}
public static class IdQueries
{
public const string Questions = "Select Top (@runs) Id From Posts Where PostTypeId = 1 Order By Id";
public const string Answers = "Select Top (@runs) Id From Posts Where PostTypeId = 2 Order By Id";
public const string Users = "Select Top (@runs) Id From Users Order By Id";
public const string AllPosts = "Select Top (@runs) Id From Posts Order By Id";
public const string Tags = "Select Top (@runs) Id From Tags Order By Id";
public const string ReviewTaskTypes = "Select Top (@runs) Cast(Id as int) Id From ReviewTaskTypes Order By Id";
public const string Accounts = "Select Top (@runs) AccountId From Users Where AccountId Is Not Null Order By Id";
public const string Badges = "Select Top (@runs) Id From Badges Order By Id";
}