search

leonibr / community-extensions-cache-postgres

A PostgreSQL Implementation of IDistributedCache interface. Using Postgresql as distributed cache in Asp.Net Core. NetStandard 2.0
54 stars 17 forks source link

Why stored procs are in use? #12

Closed MichalSznajder closed 3 years ago

MichalSznajder commented 3 years ago

It is a question than issue.

Do you have any idea why stored procs are required instead of just creating queries in memory and preparing them?

leonibr commented 3 years ago

With queries in memory the database "prepares" them every time you run, Stored procedures as you know are stored. The difference is not that significant for isolated queries but it is relevant on high database throughput

MichalSznajder commented 3 years ago

Why not use prepared statements then? Npsql offers them as described at https://www.npgsql.org/doc/prepare.html.

I had a look at SQL Server variant of IDistributedCache at according to https://github.com/dotnet/aspnetcore/blob/main/src/Caching/SqlServer/src/SqlQueries.cs they just use raw SQL queries.

leonibr commented 3 years ago

Why not use stored procedures?

MichalSznajder commented 3 years ago

To simplify deployment. With CreateInfrastructure we already have table in database but still some script must be run to install stored procedures to query it.

leonibr commented 3 years ago

To simplify deployment.

I do not agree, from user's perspective is the same step: CreateInfrastructure. With Sql Server you also have the similar step: dotnet sql-cache create "your_database_connection_string" dbo So, in fact it goes both ways, I just can't see a meaningful use case to justify one over another rather than preference...

MichalSznajder commented 3 years ago

I missed that CreateInfrastructure creates those stored proc in DB. Sorry for hassle.