New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Make use of prepared commands #5459

Open
roji opened this Issue May 22, 2016 · 9 comments

Comments

Projects
None yet
4 participants
@roji
Contributor

roji commented May 22, 2016

ADO.NET allows users to prepare commands, which allows databases to cache query plans and parses. IIRC in modern version of SqlServer this doesn't have much effect (any?), but in other providers this is an important feature - repeated complicated queries in PostgreSQL definitely benefit from prepared commands.

I know there's already an SQL caching mechanism inside EFCore, and although I'm sure it would be non-trivial to cache commands as well it would be a great feature.

Definitely a lower-priority backlog feature request.

@bricelam

This comment has been minimized.

Member

bricelam commented May 23, 2016

If we do this, we should definitely do aspnet/Microsoft.Data.Sqlite#235 😉

@roji

This comment has been minimized.

Contributor

roji commented Jul 31, 2016

FYI, I'm working on some benchmarking and performance aspects of Npgsql, and prepared command use in EFCore would potentially be huge. I hope it gets prioritized at some point.

@roji

This comment has been minimized.

Contributor

roji commented Aug 28, 2016

Note that for the next release of Npgsql (3.2), I plan to introduce an automatic/implicit preparation mechanism at the driver level - any statement used more than X times would be automatically prepared up to a certain quota, etc. This should increase performance with EFCore in a very significant way, without any change on your end.

However, it seems like EFCore may have more knowledge as to which statements to prepare - in any case it seems better-placed to make such decisions than the ADO.NET driver. So I think this feature is still relevant at the EFCore level.

@bricelam

This comment has been minimized.

Member

bricelam commented Jul 20, 2017

Microsoft.Data.Sqlite will support prepared statements in version 2.1.0. PR: aspnet/Microsoft.Data.Sqlite#388

@bricelam

This comment has been minimized.

Member

bricelam commented Jul 20, 2017

The biggest impact this feature would have on SQLite is in the update pipeline. Ironically, not batching statements, but instead re-using single-statement commands yields the best performance.

@roji

This comment has been minimized.

Contributor

roji commented Jul 21, 2017

@bricelam is it not possible to both batch and use prepared statements at the same time? With Npgsql/PostgreSQL this yields the best performance.

@bricelam

This comment has been minimized.

Member

bricelam commented Jul 21, 2017

SQLite doesn't actually support batching. 😀 We fake it in the ADO.NET layer.

@smitpatel

This comment has been minimized.

Contributor

smitpatel commented Jul 21, 2017

Fake it till you make it :trollface:

@roji

This comment has been minimized.

Contributor

roji commented Jul 21, 2017

Oh right, for a moment I forgot we're talking about a local database where batching would be of limited utility anyway :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment