I could probably rant and rave about Internet Explorer (IE) for a couple of hours, reminiscing about busted box models, janky IE specific...
IE Development JavaScriptVue has been a delightful framework to work with. The first thing I noticed about it was how easy to follow the documentation was and how...
Vue Development JavaScriptWith the news that Microsoft is acquiring GitHub, I thought I would ask the team what their thoughts are on the topic. We utilize the .NE...
Security is an essential part of any application ecosystem, but it can also be a nebulous concept for an organization to grasp. During ou...
developmentLet’s say you are developing a new Infinity War SQL stored procedure. Thanos, your boss, has explicity told you to add validation to a st...
development c# sqlData can often be messy and cleaning it up falls on the shoulders of you, the developer. It’s also easy to search StackOverflow for compl...
developmentReferencing nested objects in your LINQ to SQL query does some pretty neat magic “behind the scenes”. But, there are occasions when its m...
EF SQLThe RimDev.Stuntman NuGet package has reached version 2.0! Unified package with ASP.NET Core 2.0 Support Both ASP.NET full framework an...
OSSLet’s talk about state management for a minute. There’s recently been a bunch of hubub about Redux on Twitter, and arguments about the us...
Web Vue Vuex JavaScript StateManagementHere at RIMdev, we are big fans of Vue.js, as we’ve written about before. It scales well and is very versatile whether you’re starting up...
Web Vue JavaScriptAt Ritter Insurance Marketing we value our peers and other organizations in the overall software community. We believe in the power of c...
OSSIn today’s front-end ecosystem we are breaking down code into separate files and importing them. This helps us separate out code and reus...
Web Vue JavaScript WebpackSoftware development is a funny thing. As I develop my skills, I begin to form an idea of what’s right and wrong, what’s up and down. Thi...
elasticsearchAt Ritter Insurance Marketing we have a number of repositories, each with separate continuous integration builds. This is a base setup f...
.NET Cake AppVeyorAt RIMdev, I spend most of my time writing Vue components. And I love it. But sometimes there’s unique problems that can be encountered w...
Web Vue JavaScriptIn the whirlwind that is modern web development, I thought it would be a great idea to revisit one of the fundamental parts that make the...
HTML WebSetting up a local server comes in many flavours today. My goto was the Apache vhost.conf/hosts combo, now to load something quickly - Gu...
gulpHacktoberfest 2017 is upon us and now is a great opportunity to contribute to open source and the community as a whole. At RIMdev we’ve m...
OSSWe ran across an issue where window.exports was being unexpectedly set. The cause <div id="exports"> ... </div> Why? I...
JavaScriptPicture 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.
