At 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? ...
JavaScriptTL;DR Using ExecuteReader against SQL Server with a query using for xml or for json causes data to be chunked. A single call to ExecuteR...
.NET .NET Core 2.0 JSON XML SQL ServerAt Ritter Insurance Marketing, we continue to invest heavily in Web APIs primarily built on top of ASP.NET Web API 2. To supplement our A...
asp.net WebAPI RESTIf you CSS, you’re familiar with the browser -prefix. Chrome and Safari have -webkit, -moz for Firefox/Mozilla, and -ms for Edge. As brow...
cssWe decided not to use Material Design (#reasons) while re-developing our core application suite, although i really miss MD’s micro-intera...
UI Sass Semantic UISometimes it’s useful to ensure a project works with multiple versions of runtimes. In the following example, multiple versions of Node.j...
AppVeyorUpdate October 17, 2017 This doesn’t work with environment deployments, as this needs to run after the deployment has completed. See htt...
AppVeyorhttps://xkcd.com/1597/ While I generally don’t do that, here’s what I currently do: Setting up a repository locally Using Stuntman as...
GitHubAs a team empowered by .NET open source, we’ve always been sensitive to the people, projects, and conferences that make the .NET communit...
conference oss teamThe RIMdev team composition is a unique blend of disciplines forged in the crucible of time. We have backend developers, frontend develop...
Vue.js JavaScript frontend clientsidePreviously, I wrote about Publishing to npm Using AppVeyor. This is a similar post, but specifically geared toward publishing an npm pack...
AppVeyor npm MyGetStuntman is an open source library we developed at RIMdev to make testing user scenarios easier during development time. There are times ...
Windows Azure Azure Stuntman OSSTypically, running a Jekyll site locally for the first time requires a basic knowledge of using the command line. It usually involves a f...
jekyllA picture is worth a thousand words, so I assume a diagram is worth a billion words. Expressing our ideas is important and our blog is an...
JekyllBefore claiming we are in a Renaissance or that it is time for one, we should take the time to understand the meaning of the word and its...
.NET Community OSSPicture 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.
