As we create CSS components in our internal framework, we typically try to use little if no JS. Our tooltips should be no different. We...
UI/UX frontend CSS Responsive DesignImagine you’re using Elasticsearch with your strongly typed C# models and nameof(MyProperty) and wondering why it doesn’t work. Turns out...
.NETI have been working as a software developer for almost three years, the most common exception or bug I made is NullReferenceException -Sy...
.NET C#Testing is an important step we can take as developers to reduce bugs in our code, but testing seems to be one of the best practices that...
Vue.js Jest TestingLast week I attended the annual user experience conference Convey UX in Seattle, where 50 UX leaders from all over the world shared their...
UI/UX frontend conference conveyux2020 conveyuxWe run a number of web applications at Ritter Insurance Marketing. Our primary datastore for these applications is MSSQL / SQL Azure. Our...
SQLWe’ve been working through upgrading our core applications from ASP.NET full framework to ASP.NET Core. Over the years we’ve assembled an...
.NETConfigurationManager has long been used by .NET Framework developers prior to .NET Core to access things like app settings and connection...
.NETWe’ve had some internal discussion around the usage of RegexOptions.Compiled in .NET – how it works and when it’s appropriate to use it. ...
.NETDebugging web applications can be difficult sometimes. When debugging a failed HTTP GET request in Application Insights you have all of t...
.NETSupporting IE comes with challenges if you are using es6, and while babel helps greatly there is a few gotcheas. One of the main one is u...
JavaScript IE 11For a long time, we were dependent on the CSS frameworks of others – and we were quite happy. 😀 As our needs grew, we needed a framework...
Sass Dart SassAutofac is an inversion of control container for .NET. It allows developers to register items and then later use those registrations to i...
.NETWorking with dates can seem a little daunting sometimes. We need specific formatting, and hopefully we don’t have to deal with timezones ...
JavaScript Vue HTMLWe recently started using SendGrid to send emails in production. As part of that, we noticed that emails to outlook.com, hotmail.com, msn...
SendGridHave you found yourself getting lost in large Vue components that have multiple features? The Composition API is a new optional syntax in...
vue3 composition frontend developmentAccording to https://www.chromestatus.com/feature/5088147346030592 at the time of this blog post Chrome 80 is targeted to default cookies...
.NETOur RimDev.FeatureFlags library uses Newtonsoft.Json as part of roundtripping the on/off state in SQL. With that we use TypeNameHandling....
.NETI never thought ordering of relational static fields and properties in C# mattered. And, then, I started getting NREs on a property I kno...
C#Picture a scenario where you want to page a large dataset and your LINQ statement has several Include-calls. The first few pages load fairly quickly, but the deeper you get into the results, the slower each page loads. You refactor the query, but still have longer wait-times the further down you go in the results. As a last-ditch effort, you decide to increase resources to your DB-server and call it a day. Things may have improved a bit, but as the dataset grows, so do your wait times. And, the cycle continues…
Given a LINQ statement like:
context.Cars
.OrderBy(x => x.Id)
.Skip(50000)
.Take(1000)
.ToList();
This roughly gets translated into:
select * from [Cars] order by [Cars].[Id] asc offset 50000 rows fetch next 1000 rows only;
Because offset and fetch are extensions of order by, they are not executed until after the select-portion runs (google). This means an expensive select with lots of join-statements are executed on the whole dataset ([Cars]) prior to getting the fetched-results.
All that is needed is taking the OrderBy, Skip, and Take statements and putting them into a Where-clause:
context.Cars
.Where(x => context.Cars.OrderBy(y => y.Id).Select(y => y.Id).Skip(50000).Take(1000).Contains(x.Id))
.ToList();
This roughly gets translated into:
exec sp_executesql N'
select * from [Cars]
where exists
(select 1 from
(select [Cars].[Id] from [Cars] order by [Cars].[Id] asc offset @p__linq__0 rows fetch next @p__linq__1 rows only
) as [Limit1]
where [Limit1].[Id] = [Cars].[Id]
)
order by [Cars].[Id] asc',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=50000,@p__linq__1=1000
So now, the outer select-statement only executes on the filtered dataset based on the where exists-clause!
Again, your mileage may vary on how much query time is saved by making the change. General rule of thumb is the more complex your select-statement and the deeper into the dataset you want to go, the more this optimization will help.
