C
C#2y ago
İrşat

Calculating post contents without fetching

Stopwatch elapsedTimeForExtras = new Stopwatch();
elapsedTimeForExtras.Start();
foreach (PostDtoRead_1 post in posts_onepage)
{
//remove if the chapters are not published
//Maybe I can fix this from the root later
if (post.chapters != null && post.chapters.Count() > 0)
post.chapters = post.chapters.Where(c => c.IsPublished == true).ToList();

//Get comment and reply count
var commentIds = _db.Comments
.Where(x => x.targetPostId == post.id &&
x.deletedStatus.body == "Default")
.Select(x => x.id);
var replyIds = _db.Replies
.Where(x => commentIds.Contains(x.commentId ?? 0) &&
x.deletedStatus.body == "Default")
.Select(x => x.id);
post.comRepLength = commentIds.Count() + replyIds.Count();

//Get the sum of words in chapters of the post
char[] wordSeparator = new char[] {' ', '\r', '\n' };
var chbodyList = _db.Chapters
.Where(x => x.postId == post.id &&
x.deletedStatus.body == "Default" &&
x.isPublished == true)
.Select(x => x.body);
foreach(string? chbody in chbodyList){
post.wordsLength += chbody != null
? chbody.Split(wordSeparator, StringSplitOptions.RemoveEmptyEntries).Length : 0;
}

//check vote by user
if (userId != null)
{
Vote? checkVoteByUser = await _db.Votes.SingleOrDefaultAsync(v =>
v.accountId == userId &&
v.targetPostId == post.id);
if (checkVoteByUser != null)
post.VotedByUser = checkVoteByUser.body;
}
}
elapsedTimeForExtras.Stop();
Stopwatch elapsedTimeForExtras = new Stopwatch();
elapsedTimeForExtras.Start();
foreach (PostDtoRead_1 post in posts_onepage)
{
//remove if the chapters are not published
//Maybe I can fix this from the root later
if (post.chapters != null && post.chapters.Count() > 0)
post.chapters = post.chapters.Where(c => c.IsPublished == true).ToList();

//Get comment and reply count
var commentIds = _db.Comments
.Where(x => x.targetPostId == post.id &&
x.deletedStatus.body == "Default")
.Select(x => x.id);
var replyIds = _db.Replies
.Where(x => commentIds.Contains(x.commentId ?? 0) &&
x.deletedStatus.body == "Default")
.Select(x => x.id);
post.comRepLength = commentIds.Count() + replyIds.Count();

//Get the sum of words in chapters of the post
char[] wordSeparator = new char[] {' ', '\r', '\n' };
var chbodyList = _db.Chapters
.Where(x => x.postId == post.id &&
x.deletedStatus.body == "Default" &&
x.isPublished == true)
.Select(x => x.body);
foreach(string? chbody in chbodyList){
post.wordsLength += chbody != null
? chbody.Split(wordSeparator, StringSplitOptions.RemoveEmptyEntries).Length : 0;
}

//check vote by user
if (userId != null)
{
Vote? checkVoteByUser = await _db.Votes.SingleOrDefaultAsync(v =>
v.accountId == userId &&
v.targetPostId == post.id);
if (checkVoteByUser != null)
post.VotedByUser = checkVoteByUser.body;
}
}
elapsedTimeForExtras.Stop();
This alone takes more than 100ms. I wonder if it's optimized. With the rest, it's around 150-200ms. There are barelly any text in chapters and comments. So I am confused why it's that long. I tested by commenting the counting reply. It's the one with 100ms.
2 Replies
İrşat
İrşat2y ago
Changed the second part with
var replyIdCount = _db.Replies
.Count(x => commentIds.Contains(x.commentId ?? -1) &&
x.deletedStatus.body == "Default");
var replyIdCount = _db.Replies
.Count(x => commentIds.Contains(x.commentId ?? -1) &&
x.deletedStatus.body == "Default");
It's 60ms now
undisputed world champions
"without fetching" do you mean without db access? because you access the db a couple of times depending on where your db stands, this takes a while (network in general takes quite a bit longer then doing stuff in memory)