Incredibly slow query speed

[mejobloggs-cw]

In linqpad, basic queries run in about 0.1 seconds.

In NetPad, the exact same queries take 4 to 30 seconds. Sometimes it even times out

Is the any way I can improve the query speed in NetPad?

Here is a query for example
Vendors.Where(x => x.VendorId == 11594).Select(x => x.VendorId)

LinqPad: 0.003 seconds
NetPad: 4.1 seconds

Using NetPad v0.6.1 with latest .Net 8

[tareqimbasher]

There a couple reasons for this difference:

• NetPad compiles an assembly and runs it in a new isolated process each time you run a script; ie. it uses process isolation. LINQPad on the other hand uses AppDomain isolation. A query in LINQPad runs in an isolated AppDomain within the same process (by default, LINQPad offers a way to run in an isolated process), so there is no overhead of starting up a new .NET process each time you run your script.
• The timing you see in NetPad includes the time it takes to compile the script and the time it takes to start the isolated process, LINQPad's timing excludes any setup/teardown time.

While LINQPad supports .NET Core, its built using .NET Framework. NetPad is fully built using .NET Core, where AppDomains do not offer the same isolation features as they did in .NET Framework, so that's not an option. Reducing start-to-end timing is something I am personally invested in reducing and was thinking of opening a discussion on the topic to solicit input.

Restricting the "elapsed time" to actual "run time" and taking out the compiling/process-start timings is something on the agenda. While it will not make anything run faster, it will still be much more informative.

Keep in mind LINQPad has been around for a good 17 years, NetPad is pretty new, it needs a bit more time to catch up, but it'll get better 😄 Its a constant battle deciding which feature/enhancement to focus on next. The strategy so far has been focusing on core functionality.

[tareqimbasher]

Question. Is your query running against a database? Or is Vendors an in-memory collection?

[mejobloggs-cw]

Thanks for the info. Yeah running against a database.

Interesting to hear about the isolation issues. Better for security I guess, but seems a big step backward for this use-case!

[tareqimbasher]

The choice to not use AppDomains in NetPad is not a matter of security, but that AppDomains cannot, by design, be used to fully unload a compiled script assembly from memory. It just doesn't work like that in .NET Core. It did however in .NET Framework.

In .NET Core if you load an assembly into a separate AssemblyLoadContext (the AppDomain replacement in .NET Core) you cannot reliably fully unload that assembly from memory. It is highly dependent on what your script is doing, and what the assemblies you're referencing are doing too (example). Again, this is a limitation inherent to .NET Core. This means you're stuck with additional assembly(ies) in memory everytime you run your script. This results in the app using more and more memory as you continue to use it which, as you can imagine, isn't great. As of right now, I am unaware of a way around this when opting for in-memory execution.

That being said, efforts are planned to optimize and make execution faster.

[davidroth]

While LINQPad supports .NET Core, its built using .NET Framework.

I am not sure this is correct.

IMHO it is as following:

Linqpad 6/7/8 is built on .NET Core. It runs on .NET Core and can therefore only run .NET Core stuff.
Linqpad <= 5 is built on .NET Framework and runs on .NET FX and can only execute NETFX stuff.

[tareqimbasher]

@davidroth I believe you are right. My info was outdated and thinking of LINQPad 5. LINQPad 6+ is indeed built with .NET Core.

An important contributor to slower speed when using a data connection is Entity Framework taking a relatively long time to initialize its model at runtime the first time you use it. LINQPad utilizes a client/server type structure to keep a query alive and running so while the first run of the query might take some time, subsequent runs are much faster. You can see this EF init time in NetPad as well with something like this:

var sw = new Stopwatch();

sw.Start();
Vendors.Where(x => x.VendorId == 123).Dump();
sw.ElapsedMilliseconds.Dump();

sw.Restart();
Vendors.Where(x => x.VendorId == 456).Dump();
sw.ElapsedMilliseconds.Dump();

The second query will be much quicker. However since NetPad starts and then completely stops a script on each run, the EF initialization has to take place each time. I'm studying some options on how to implement a better experience, possibly using a similar approach to LINQPad. In the meantime the next NetPad release will contain a scaffolding option to use a compiled model for models that are very large (100s to 1000s of entities). PR: #197

Lastly, a gentleman from the Linq2Sql team reached out to discuss adding Linq2Sql support to NetPad which might bring its own suite of perf improvements.

[mejobloggs-cw]

Ah yeah I noticed recently with Visual Studio extension called CSharpier it seems to use a client/server model, and I wondered if a similar method could help your situation. When the extension first starts, it loads a "permanent" process which I think is a small http server, then the extension talks to that

It's open source, maybe there could be some good tips in their code: https://github.com/belav/csharpier

[bgianninoto]

Here is a query for example Vendors.Where(x => x.VendorId == 11594).Select(x => x.VendorId)

Wouldn't this particular query perform better if you use .Single() or .First() instead of .Where()?

[tareqimbasher]

Yes it would, although the intent was just to demonstrate the time it takes EF Core to initialize. Even if you used .First() you will find that the first query is significantly slower than the second one.