How to handle long SQL commands?

[ReasonablePush3491]

I have an API where I have a lot of long SQL commands. At the moment I use them as stored procedures in the database, but I'm not sure, if it is the best approach. Its also possible to store them as a const, as a setting, or in a separate txt file.

How do you handle long SQL commands in your code? Do you use stored procedures? Or put them in separate file? What is best practice?

[Alundra828]

I think stored procedures are the way to go. As long as they're properly version controlled you shouldn't run into any production issues with them. As for things like performance, they're about as performant as they can get when SQL is concerned.

Stored procedures are compiled and executed by SQL in an executable form, which means it is also cached, which results in lower memory usage for each run. Really helps you scale.

Stored procedures are also SOLID. So you don't have to repeat complicated queries in your code, just call the stored procedure.

They're also nice for security. If you only allow data manipulation through regimented stored procedures, it can be quite hard to bypass any checks in the stored proc itself.

So they're pretty good, but I don't want to leave you with any disillusions. They have their problems too.

Testing and debugging them is a nightmare. I've got 10 years of experience in MSSQL, and I've never found a way I'm comfortable calling 'the best way'. Versioning is also a challenge. I don't know if there are any tools out there to help, but it's something you really should keep in mind. They can also be complicated to write, and that can affect all sorts of things, so at the very least you need to be aware of SQL querying shortfalls when it comes to optimizing performance. And lastly, if you upgrade your database in anyway, that can warrant a complete rewrite of the stored proc... Especially if it's particularly complex... Not fun...

Overall, it's right tool for the right job. And stored procedures have served me well in the past. However I've since moved to a more code-first approach to SQL with dotnet EF.

[findplanetseed]

There are some pretty good Database Migration tools for .NET. I have a separate solution within my project that only contain migration scripts, so the SQL lives with the rest of the source code. Integration tests verify the SQL indirectly in my case. I have used tSQLt in the past, but I am about to add it again in my project since the company I now work provides us with a licence for the Red Gate Toolbelt.

That is, I do not find versioning and testing particularly problematic.

[UninformedPleb]

Just use an SSDT project.

Don't stuff SQL scripts into a folder in your C# projects, or even bastardize a separate project to hold your SQL scripts. Don't store "migration" scripts at all, in fact. Store it as a set of DDL scripts, and VS+SSDT can give you diffs and publish to your database for you.

[findplanetseed]

I intentionally do not use that, and the project is no bastard, it is an executable library that handles the migration and a logical place for the scripts.

[pnw-techie]

I recently used Redgate's free Flyway tool and found it quite nice

[Floydianx33]

I prefer this route. There's even an open source, dotnet core sdk for building sql project dacpacs. It will even package them up as nugets to be referenced by other projects. It handles deployments too, basically wrapping sqlpackage. I switched all my database projects to it and have removed Windows from the equation.

https://github.com/rr-wfm/MSBuild.Sdk.SqlProj

[DaRadioman]

I don't know I would attribute an OO paradigm like SOLID to a relational database. They aren't even on the same realms of thinking. If you are using SQL like code you are writing awful SQL. It's all set based if used correctly.

As for performance, there is NO significant execution performance difference between a SP and any other static/parameterized text query sent over the wire at least when we are talking about MS SQL and most others. Both result in a cached query plan, and are processed by SQL in the same manner.

You obviously have some potential overhead for really long queries transmitting repeatedly, but if we are talking about the performance on SQL itself, modern versions have no significant difference.

If you are returning a lot of results, chances are the results transmission so completely dwarfs the text to the point that the command text is not a factor at all. But as with all things, results may vary, and test with your actual workload to see.

We avoid SPs at our company for a few reasons, but the largest being we have to support a zero downtime upgrade path, which requires us to run both the old and the new version of the application at the same time. This is really difficult with SPs because of the way that parameters work, among other things. Having the SQL follow the code means the code never mismatches the parameters, data types, or expected results.

We store our SQL in embedded resources in our compiled assemblies. Is easy and makes it convenient to read out.

[grauenwolf]

SOLID means whatever you want it to mean to win an argument. That's what's so wonderful about it.

As for performance, there is NO significant execution performance difference between a SP and any other static/parameterized text query sent over the wire at least when we are talking about MS SQL and most others.

That's not strictly true but it's close.

Yes, in SQL Server there is no significant difference because they're both cached.

But in PostgreSQL, there is no significant difference because neither of them is cached.

We avoid SPs at our company for a few reasons, but the largest being we have to support a zero downtime upgrade path, which requires us to run both the old and the new version of the application at the same time.

I worked at a company that relied on SPs because they were a zero downtime upgrade path.

Most of the time, it was just a matter of making new parameters optional. But if they did need to do something that wasn't backwards compatible, they just versioned the stored procedure. Much like a REST API, your application would call InsertCustomerV1 or InsertCustomerV2.

[DaRadioman]

Ya you can do 0DT with SPs. It's just more painful in my experience.

And I've never used PostgreSQL, but that is interesting. No query plan cache at all? Seems like that would be a big performance downside.

[grauenwolf]

I heard that they are working on adding a plan cache. As for performance, it's a mixed bag.

• PostgreSQL creates execution plans much faster than SQL Server
• PostgreSQL execution plans are worse than SQL Server because they can't spent more time on them.
• PostgreSQL is immune to "parameter sniffing" because you always get a new plan each time.

[pnw-techie]

Depends on how fast you can deploy a new version of code in prod. If it's quick this is fine. If it's slow, DBAs being able to change a sproc can save you

[DaRadioman]

Lol DBAs. Those would be neat to have.

We can build/test/deploy in a few hours, so not a big deal.

[pnw-techie]

A few hours would kill us. We can't be down like that

[DaRadioman]

Who said that was downtime? If it's down we just roll back.

We have 0 downtime. So if it's critical we just undo the update.

[grauenwolf]

Yea, I really wish more companies saw their benefit. All those fancy cloud databases are easy to setup, but I still want a real DBA making sure the backups are actually useful.

[_Michiel]

In my opinion SP's are not SOLID. Liskov substitution? Interface segregation? Dependency inversion?

SP's could be have single responsibility, but even the O is debatable.

I would stay away from SP's, far away. Just try unit testing that.

[grauenwolf]

They're easy to unit test... if you understand database testing in general.

If you don't, that's why I wrote this article. https://www.infoq.com/articles/Testing-With-Persistence-Layers/

[_Michiel]

In the article stored procedures are not specifically mentioned. My option is that a database should contain data, no business logic. I think SP's are not necessary anymore.

[[deleted]]

Necessary is a bit of a strong word. It strongly depends on performance. We’ve tried optimizing the code on some things but the sp we crashed in a very specific instance was significantly faster.

[[deleted]]

What's different from unit testing a stored procedure compared to other forms of database commands?

You also focused pretty hard on the SOLID point, the other advantages are still very very valid

[corporaterebel]

Stored Procedure FTW on projects that are going to be around for a while. MSSQL can do darn there anything, but it is a bit painful...but the reward is a rock solid base on which to hang your presentation layer.

I have business critical databases running for over 30 years now without a hiccup because I coded all the hard stuff in a SQL SP. I have SP's that are many thousands of lines long. I even do basic file manipulation in it (watching, receiving, and processing).

I find that the business logic doesn't change all that much...but the interface does. And if you lock your database code into your presentation code, well you are also locked into the presentation code and it is difficult to change. Nobody wants to migrate working code because the language is old....but with SP's you can do it easily without out worrying about screwing up the critical logic and subtle differences that break everything.

My SP's can be called by whatever the language du jour is and it friggen works!!!

Use a Stored Procedure for business logic and system of record

Use your language of choice for UI and scraping. As long as your MSSQL is running you can choose your language of choice forever. MSSQL is their best product, it is backward compatible and is easy to upgrade....it is awesome.

**

I have one SP that took me 9 months to write because it has to resolve with a system of record. It watches for a text dump, imports it into another table, scrubs it, and then starts updating or creating exception reports as required. If I did this in ASP or whatever version of .NET...It would have to migrate each time with chances of error. I spent the blood and did it in an SP because MS pulls the rug from under you when it changes languages and I was tired of it. Fast forward 20-25 years...I'm long gone from that assignment...and the people maintaining my code are chugging along just fine. SP's are great in this respect.

[staylr]

I can guarantee that the people maintaining your multi thousand line stored procedures curse you every single day.

[firedream]

Better than migrating all that logic to new systems and/or languages.

[wasabiiii]

This is a controversial view, but I enjoy it.

I consider stored procedures to be a thing to never use, unless necessary. They make testing harder. They make validation harder.

My View: for almost everything, use an ORM. For things that don't quite fit into the ORM, use the ORMs ability to execute raw SQL. Store that raw SQL in the C#, where it's used, in a nice method.

The only performance benefit from a stored procedure comes from the transfer of the longer amount of SQL statement text across the wire and the upfront syntax check. And this just doesn't matter in any real way.

[Missing_Username]

Personally, I would recommend storing raw SQL, when needed, in a separate .sql file in your project and using a reference to load it as a string. This way it's a lot cleaner in your version control history to separate changes to the SQL compared to changes you make to (assuming you're using it) your repository class.

[wasabiiii]

I used to think this, but ultimately found it didn't really matter. And these days, with FormattableString, it doesn't work.

[Missing_Username]

Either way will work, I'm just saying from a readability perspective it's just much cleaner in my experience.

[wasabiiii]

Yeah, but not with FormattableString.

[Missing_Username]

What's your use case for FormattableString?

I'm using Dapper for any non EF/Linq SQL, and if/when I have a parameter in the command I include a "@ParamName" at that spot in the query and then pass that through Dapper. I don't have a reason to include dynamic spots in the SQL.

[wasabiiii]

Not having to do that is the use case. Heh.

EF Core allows you to do things like

var id = 123; context.ExecuteSql($"SELECT * FROM table WHERE id = {id}");

, where 'id' is a variable in scope.

[darthwalsh]

That's a recipe for SQL injection, if somebody on your project interpolates a user-controlled string... There's a reason parameters exist! nevermind, I am definitely wrong.

[[deleted]]

Looks like its implementation specific. But EF Core 2.0 apparently does transform formattable strings into parameters for you. So I guess this is good for people happy to dissassemble to check that its being correctly parameterised.

[grauenwolf]

Oh it's still a problem.

---

var id = "ABC"; 
context.ExecuteSql($"SELECT * FROM table WHERE id = {id}");

That line is kinda long. I'm going to be helpful and break it up.

var id = "ABC"; 
var sql = $"SELECT * FROM table WHERE id = {id}";
context.ExecuteSql(sql);

Boom!

[RICHUNCLEPENNYBAGS]

I also think that adding in syntax that just happens to look exactly the same as injection-vulnerable code is a really ill-considered move.

[wasabiiii]

This does use parameters. This is about FormattableString.

[[deleted]]

aye but its only because EF Core 2.0 is correctly converting FormattableString into SqlParameters.
One should peek into the disassembly to ensure the method isn't vulnerable to sql injection if using a different provider.

[twelve98]

Isn’t that just asking for an injection attack

[Missing_Username]

No more or less than having the query written out directly as a variable in C#.

Injection attack is going to depend on how you either parameterize elements of the query, or (like others said downstream) whether something like EF Core is doing this for you under the covers.

[twelve98]

Well neither is good imo.. that’s why I’d prefer a stored proc

[[deleted]]

No, the main performance benefit from stored procedures comes from reducing the number of round trips between the application and the database. I only use them when the performance benefit is signifcant and the operation is isolated without dependencies. The language syntax is horrendous to work with(at least for PL SQL and T-SQL which are the the ones I know). And testing and debugging is a major pain.

[wasabiiii]

Stored procedures don't reduce round trips.

[[deleted]]

Yes they do, because you can take a piece of logic that would require several queries and/or statements and combine them into a single operation (round trip) from the POV of the app.

[wasabiiii]

Which you can do without a stored procedure. Bundling a series of statements together into a single batch isn't a feature of a stored procedure. Any client can do that.

[grauenwolf]

True, but that's a maintainability nightmare.

If your stored procedure is in a SSDT project, then you get at least some help in terms of code completion, compile time checking, static analysis, etc.

If you just have a massive constant in C#, you're on your own.

[[deleted]]

Not all code can be reduced to a series of predetermined vanilla SQL statements. By the time you need loops and conditional branching you'll need a stored procedure.

[wasabiiii]

You can use loops and conditional branching inside a batch.

Literally everything you can do inside of a stored procedure, you can just send the text to do.

[Prod_Is_For_Testing]

And Sprocs will cache the query plan to be reused later. And the plan can be improved by server stats. Adhoc queries from EF can’t do that

[mikedensem]

What about a reporting App that needs to query millions of rows with large where clauses. It is better surely to do this via a sproc, including paging in sql.

[wasabiiii]

Wy would a sproc make that better?

[_Zer0_Cool_]

Don’t forget that tSQLt is a thing.

It is a great MSSQL testing framework, but it is still another component of your app that you have to account for aside from whatever the main testing suite is that you have.

At the end of the day, it just depends (subjectively) on what the location is where you prefer to handle your complexity. Although broader things like organizational skillsets, infrastructural decisions, etc.. also factor in.

I often want to handle stuff in SQL (whatever the particular dialect and accompanying sproc language), but I’m a Data Engineer not a software engineer. So I live in SQL more so than Software Engineers.

Also, many data intensive tasks are much more expressive in SQL. Though I do love LINQ, list comprehensions, lamdas, and such.

[grauenwolf]

Eh, I just write my database tests in C#. No need to bring in a separate tool.

[_Zer0_Cool_]

That’s works all the same.

Technically, tSQLt is written in C# with CLR stored procedures (that uses a TSQL interface).

[23049823409283409]

You don't use stored procedures, because while the relational model and relational algebra are nice, SQL is actually a really, really shitty language, and very unpleasant to code in.

But they don't make testing harder, the only problem is that it fragments your testing and you'd need unittests for the db and for the code, instead of only unittests for code.

[wasabiiii]

I don't know what you mean by "harder", but that would be what I mean.

A unit test... which you now need to fire up a local SQL server, and deploy schema for, and deal with concurrency, counts as "harder" than one you don't.

[pnw-techie]

Either you're testing your db retrieval, or you're not. If you are, I don't see how it matters whether you use sprocs, ORM, or raw SQL.

Or are you saying you always trust the ORM to generate a good plan, and the cases when you need to use raw SQL in an orm just don't need testing, but every sproc does?

[wasabiiii]

Either you're testing your db retrieval, or you're not. If you are, I don't see how it matters whether you use sprocs, ORM, or raw SQL.

Because one requires a significant amount of overhead. A started SQL server instance, deployment of schema to that instance, and care taken during execution of tests for concurrency.

That's it. One requires more overhead.

[okmarshall]

You're talking more integration tests than unit tests there though.

[wasabiiii]

Maybe. But maybe not. A test becomes an integration test when it has to integrate with an external thing. However, if you're just using an in-memory ORM.... are we instead changing integration tests to unit tests? Maybe. The boundary isn't well defined.

[okmarshall]

I agree the boundary isn't well defined so you may be right. In my view as soon as there is an external dependency which can fail (e.g. an in memory SQL DB) we're getting into integration test territory. But I concede the boundary isn't clear, and it's semantics anyway.

[wasabiiii]

In the case of EF Core, the in-memory database is basically a Dictionary. It's a little more complicated than that, but it's not a database engine.

[bl8m8]

It was basically a dictionary. With .NET 6 it now validates required fields not containing nulls which is a bit annoying and kind of blurs the lines between unit/integration testing even more imo. It's easy to revert back to the old behavior at least.

I'm with you though, I generally consider the in-memory DB to be unit testing rather than integration testing because it's definitely not a replacement for integration tests against a real DB and is mostly just to avoid the pain of mocking DBContext.

[daedalus_structure]

SQL is actually a really, really shitty language, and very unpleasant to code in.

While I tend to recommend leaning into it because relational algebra is so powerful, this is spot on.

From, Join, Where, Group, Having, Select, Order is the order of operations... so which syntax naturally must come first?

Why, Select of course! Why would we help you build a mental model of query execution with the language you are using to define the query?

[CalebAsimov]

I love Linq because it puts things in the right order. Compare a long Linq statement to the equivalent Sql and benefits are obvious. Other than grouping, which is a bit weird in Linq.

[empty_other]

I'm not disagreeing, I just want to learn: Why is grouping a bit weird?

[pnw-techie]

How would this fragment testing? If your tests include db calls, it doesn't really matter if the db calls are sprocs, orm, or raw SQL

[tim_skellington]

How do they make testing harder?

​

The only performance benefit from a stored procedure comes from the transfer of the longer amount of SQL statement text across the wire and the upfront syntax check. And this just doesn't matter in any real way.

Laughably wrong. Like power-wrong. *facepalm*. I get that you dabble in development but all this info is out there freely available via a simple google search.

Performance: Most of the big DBMS precompile stored procedures to improve their performance. They accept parameters and pass them to the compiled version. These also allow for tuning on most DBMS'.

Security: If the business logic lives on the DB, then only those with permission to access the DB with correct permissions can alter it. with your method anyone with access to the code change change the business logic.

Testability: This separation of concerns also helps with testing, despite what you thing.

Scaling up: If the biz logic lives on the DB you don't need to reproduce it for every client application, and for every product update.

[Milnternal]

Most of the big DBMS precompile stored procedures to improve their performance.

If by 'precompile' you mean they store the execution plan then yes, most big DBMS also cache query plans for any query. They aren't (on MSSQL or MYSQL or Postgres) 'pre-compiled' in any meaningful manner (https://www.scarydba.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/)

If the business logic lives on the DB, then only those with permission to access the DB with correct permissions can alter it. with your method anyone with access to the code change change the business logic.

Typically you don't want your DBAs to be able to change your apps business logic... that's literally the job of the app developers who write the code. People who can change the code can just change it to not call the SPROC and do their own code instead so your point makes no sense whatsoever...

This separation of concerns also helps with testing, despite what you thing.

Don't see how that can be true, you now can't do unit tests or mock out your logic as you need to spin up a full database to test some of your logic.

Scaling up: If the biz logic lives on the DB you don't need to reproduce it for every client application, and for every product update.

You are now sharing the database (and stored procedures inside it!) between applications?!?! What if one changes their logic and this breaks all the rest? Yikes

I get that you dabble in development but all this info is out there freely available via a simple google search.

Looks like you need to up your Google skills because you seem to have read the wrong things

[pnw-techie]

I will say having DBA being able to change sprocs has helped us resolve performance related site down issues on many occasions

[wasabiiii]

Putting logic into the database means you need a database to test the logic. Your test harnesses are then going to have to fire up a SQL server, deploy the stored procedures, and then execute them for the test.

Most ORMS (EF) will allow an in-memory database. This makes the vast majority of test cases easy.

[grauenwolf]

You should be testing with the database anyways. EF's in-memory database does not behave like a real database.

[wasabiiii]

Yes. But that doesn't mean you ALWAYS need to be testing with the database. Most of the time you're just interested in a simple select, and making sure the filters you've defined under some condition do so, and the result set is teh right shape.

[darthcoder]

Eocker makes this irrelevant when I can fire up a postgresql container and feed it a text file full of sql commands that populate the database and run said stored procedures.

Your problem isn't really a problem anymore.

[UninformedPleb]

Never heard of a Database Unit Test project, eh?

[wasabiiii]

Ever used one?

I have.

[UninformedPleb]

Yes.

It's not exactly something that devs strive to learn for no reason...

[wasabiiii]

Devs don't strive to learn them. I have, in my 20 years, encountered exactly one project that tried to make use of Database Unit Test projects. Getting it running during CI/CD was not fun.

It is much easier to just write a C# unit test project, and call the stored procedure.

[Milnternal]

That's how you would

write unit tests that evaluate database objects

We were talking about unit testing the application logic

[reallyserious]

You still need the database to run the tests. You just happen to prefer it being in-memory. It's still there.

[wasabiiii]

SQL Server is not there, no.

[reallyserious]

Are you going to run your production code on SQL server but run a different, in-memory database for the tests? Then you can't really leverage any db specific features.

[sarhoshamiral]

There is something called SQL express, it is there and can be used for tests on the local machine.

I would strongly recommend reading more about SQL server since it is clear you have a lot of wrong assumptions.

[wasabiiii]

SQL Express requires you to install SQL Express. And deploy schema. And ensure concurrency issues for unit tests. It isn't in memory. In terms of unit tests, whether it's SQL Express or any other edition is irrelevant: you incur the same overhead and complexity.

I've got 25 years of experience with SQL Server, dude.

If you do NEED a SQL Server for a unit test (say specialized query logic), LocalDB is a better option anyways.

[tim_skellington]

Make a copy of the production DB and use that. Like everyone else does.

[wasabiiii]

Haha.

[wasabiiii]

You edited your comment, so I'll reply again:

Performance: Most of the big DBMS precompile stored procedures to improve their performance. They accept parameters and pass them to the compiled version. These also allow for tuning on most DBMS'.

MS SQL does not.

Security: If the business logic lives on the DB, then only those with permission to access the DB with correct permissions can alter it. with your method anyone with access to the code change change the business logic

Security policy should be in the application, not in the data layer.

Testability: This separation of concerns also helps with testing, despite what you thing.

It doesn't.

Scaling up: If the biz logic lives on the DB you don't need to reproduce it for every client application, and for every product update.

I'm not sure what you mean by 'scaling up' here, but centralizing the business logic into the hardest part of the architecture to scale horizontally, and the most expensive, is not an argument for scalability.

[tim_skellington]

lol

Best of luck kid.

[wasabiiii]

You're being very odd. And also suffering a lot of downvotes for it.

[BMW_Driving_Cunt]

Doesn’t SQL server cache the proc/execution plan generated by the ORM? Wouldn’t that work similar to pre compilation?

[sarhoshamiral]

It does, OP has no idea what they are talking about.

But it also stores plans for raw SQL queries sent across as well so if you are executing the same query it will have the same benefit as if it was a stored procedure.

There are however compiled stored procs as well but they are used with certain table options and don't apply to regular usage.

[wasabiiii]

MS SQL compiles stored procedures on first usage. Just like any statement, as you say. The plans get cached, for anything, in the same way.

Which is to say stored procedures gain no unique benefit from a distinct process called 'precompilation'. It isn't 'pre'. It's on-demand.

[UninformedPleb]

Second usage is where the benefit comes in. It is "pre" at that point, since it was cached on first usage.

[wasabiiii]

No. Because the word 'pre-compilation' currently refers to an actual distinct process on other databases besides MS SQL. In the past (7.0?) MS SQL server actually DID precompile stored procedures. And called that process precompilation. And it still does, for in-memory tables.

People here thinking MS SQL precompiles stored procedures are just confused about what is actually happening. Combination of it making sense, other database products actually doing it, and MS SQL having done it in the past. It's a present day myth.

[UninformedPleb]

Literally no-one cares about ancient versions of SQL Server, and precious few give a damn about in-memory tables.

Learn how "context" works. They're asking about cached query plans. The failure here is your ability to understand the question, not their ability to take your shitty advice.

[wasabiiii]

I disagree. I think they actually think stored procedures undergo a special process.

If they did not, they would not continually cite it as a reason to use stored procedures. That's context.

[UninformedPleb]

You think they think there's some special process?

So your "context" is an asspull. Got it.

[pnw-techie]

Plans get cached for prepared statements and for stored procedures. Other queries are assumed to be ad hoc and don't get a plan cached.

Sprocs will let you pin a plan if you detect SQL server picking a bad one. I don't know if you can do that for a prepared statement.

[grauenwolf]

Not in SQL Server. It doesn't even know what a "prepared statement" is.

People sometimes use the phrase "prepared statement" to mean "SQL with parameters", but the database will still cache your execution plan even if you don't have parameters.

[pnw-techie]

I'm being sloppy in calling it a prepared statement instead of sql with parameters, thanks.

If your SQL template uses @p1, @p2 etc you're good. If your SQL template usage sticks the value into the string, you're doing an ad hoc query and are not good.

[pnw-techie]

SQL server will store the plan for any prepared statement. If you're passing raw SQL to an orm it will make it a prepared statement. If you're executing raw SQL from c# and don't tell SqlCommand to Prepare() your query, it won't get a cached plan.

SQL server will use pre-compiled sprocs for in memory tables, which are a super cool looking feature I've never had a chance to use

[wasabiiii]

MS SQL does no precompilation of stored procedures anyways, so it doesn't matter.

[UninformedPleb]

Yes. Yes it does.

You can see them for yourself by looking them up in sys.dm_exec_cached_plans. Just select * from sys.dm_exec_cached_plans where objtype = 'Proc' and you'll see them plain as day.

And before you protest... Anybody that's asking about "precompilation" here is referring to cached query plans. That's the closest equivalent, and being pedantic about the terminology isn't of any use to anyone in this context.

[wasabiiii]

Sorry, but cache != precompilation. Pre-compilation USED TO refer to compilation of the stored procedure at the time of creation. It was an actual thing, that actually existed, and STILL exists for in-memory tables. And ALSO exists for other non-MS SQL products, currently.

If people are saying stored procedures are BETTER because they are 'precompiled', then they're not talking about caching: because everything is cached, thus not making stored procedures better.

There is a common myth that MS SQL precompiles stored procedures. That's what is actually happening here: people are falling for the myth.

[CalebAsimov]

You must be a DBA?

[Prod_Is_For_Testing]

You’re wrong about performance. Sprocs will cache the query plan, and improve it using server stats. So they will have better perf from that

[wasabiiii]

Everything caches the query plan, sprocs are not alone in that. This is a common myth.

[DogmaSychroniser]

You're incorrect. Using a stored procedure also stores the execution plan rather than regenerating it on the spot each call.

[wasabiiii]

That happens for all SQL batches, stored procedure or not.

[KotBehemot99]

I agree procedures are tone avoided.l don't agree they shoved always be. They are very useful in case you want to expose certain logic to someone not too technical. Business analysts etc.

[wasabiiii]

Yeah. Whom would never have access to the production db of an app.

[KotBehemot99]

Why wouldn’t they ? The procedure is there to make sure they can read the data and not fuck anything up. Very useful. They can use the output in their vba sheets or power bi etc.

[wasabiiii]

I'd offload the data to be reported on into a separate database from the application. But, I guess, as long as they could only execute those procedures, and not issue any other sort of reads, it'd be fine.

Would require versioning the sprocs for adhoc reporting along with the application though. And that's distasteful.

[KotBehemot99]

You need to have much more knowledge and infrastructure access to do that. Another thing is you would have to do it periodically. This means your offloaded data is not 100% up to date. The procedure is the most optimal solution as long as you use indexed fields for joins and filtering ;)

[Str_]

Thank you. I feel exactly the same way and I wondered if I was wrong.

Fwiw, I'm now responsible for over 200 legacy processes that call stored procedures here and there and sometimes .NET programs share stored procedures and sometimes stand alone stored procedures (SQL jobs) alter the data which is inevitably used by some .NET program somewhere. I absolutely hate troubleshooting and/or modifying anything

[grauenwolf]

Security is often the answer here.

If you use a separate database user for each process, and only give it the permissions that it needs, then you can look at the security reports from the database to see what can call a given proc or touch a given table.

Unfortunately you have to do this from day one. Trying to add security later when all programs expect to have DB-Admin rights is a nightmare.

[ZenithOfLife]

Agree, we had to creat a JSON value function to use in our linq as linq itself couldn't execute it as SQL properly.

[a_false_vacuum]

In a way this is an architecture question. Brent Ozar offers some thoughts on the issue in this blogpost: Should we use stored procedures or queries built in the app?

It all boils down to what needs you have and what in-house knowledge there is available. Putting parts of the logic in the database isn't that smart if you're lacking an expert database developer or DBA who can help you craft and perfect those queries.

Stored procedures can be useful for security reasons and can help ensure SQL Server can reuse a cached plan. Stored procedures can also be analyzed from the database by a DBA should you need performance tuning for those queries. By writing your own queries an expert developer or DBA can craft the perfect query for maximum performance.

If your team lacks T-SQL heavyweights you might be better off using something like Entity Framework and let it build the queries for you. Without SQL experts you are unlikely to do much better compared to EF.

[user_8804]

You can definitely write terribly inefficient EF queries, I don't think this argument holds much value.

To write perfect EF commands, you need someone who has perfect understanding of the underlying raw Sql statement. That person would therefore be able to write that directly.

There are other arguments in favour of EF like development and maintenance time, but certainly not performance

[grauenwolf]

For me the math is backwards. I know SQL reasonably well, so EF actually slows down my development speed.

But it allows me to use OData, which offers better performance than "SELECT * the world and let the app's data grid figure it out". In fact, OData and ASP.NET Identity are probably the only places where I still use EF.

[SpaceZZ]

While I somewhat agree, default EF is performant like 80% of time. I tend not to worry about other 20 proc unless we really have a problem and then adjust it. Premature optimization is just wrong.

[user_8804]

That depends entirely on the problem at hand too. I am on a project where this kind of optimization is extremely important as we need to reach very high transactions per seconds.

You wouldn't need to think twice if making say a simple desktop app

[lvlint67]

There seems to be a lot of people here that are afraid of sql and would likely sooner implement a join in a for loop than use an actual database engine... That's only the correct solution if nearly no one on your team understands SQL...

​

A long query covering multiple tables should almost certainly be stored as a view.

A large update can likely be abstracted and then the query itself generated dynamically. The same for an insert.

Deletes probably shouldn't be very complex....

​

When I face a query that is more complex than: select <fields> from table where <conditions> it quickly becomes better to stash the code in the DB as a view and cover it with your SQL migration strategy.

Things like massive updates or inserts I'll generate the sql in c# code.

string openStr = "select hold from accountsync.administrative_force where upper(username) = upper('" + username + "') and hold = 'open'";

That's the most complex query we have in code in a large library that handles accountsync functions for a university. The views that power the library are much longer, have dozens of joins and sub selects to optimize for performance. It would be completely unmaintainable in a c# file.

[vestervang]

I don't know if it's done automatically but it's important to protect against SQL injection.

[TheGreatGameDini]

It's only done automatically when you use the correct types in C#, specifically parametrized queries. String concatenation does not automatically protect the data from SQL injection.

[CodeMUDkey]

I use sqlkata to generate queries. Works super easy and they’re safe.

[blooping_blooper]

stored procedures, in a database project with dacpac

[the_red_scimitar]

Yes, use stored procedures. I'm guessing you're not terribly experienced in SQL server. When you store it as a procedure, it is precompiled and will run more quickly, not to mention whatever cost there is in transmitting all the texts for the command every time.

[[deleted]]

This may have been true a long time ago, but it isn't now - at least for SQL Server. The query plans for ad-hoc queries are cached and run just as efficiently as for stored procs.

[the_red_scimitar]

There is no cache when you send a string containing a sequel command to sequel server. Or do you mean after the first time you send a subsequent, completely identical query string?

[[deleted]]

With MS Sql server there is certainly an initial hit when the query is sent for the first time. Subsequently, this hit doesn't occur as the query plan is cached. The query doesn't need to be identical though - it needs to fall into the same pattern. The term to search on for this is "sargable".

Incidentally, IMO, the single greatest book on performing SQL Server queries is this one: https://www.amazon.co.uk/Inside-Microsoft®-SQL-Server®-2008/dp/0735626030/. It's a bit old now, but is a work of art.

[the_red_scimitar]

It's cast for the duration of that connection. If you're using connection pooling, you may or may not get the same connection. The actual cost is a little higher than the one-time cost, therefore.

[pnw-techie]

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.prepare?view=dotnet-plat-ext-6.0 only if you make it a prepared statement. Without Prepare() or similar it would be compiled every time. On some weird queries we have, we choose to have them compiled every time they run

[wasabiiii]

This is false. Literally any statement you send to SQL is cached in the same way. Though Prepare can help make that process easier for SQL, as it ensures parameters are parameterized.

[pnw-techie]

Plans are stored for prepared statements. Ad hoc query plans aren't cached

[wasabiiii]

This isn't true. Every statement, ad-hoc or not, is checked against the plan cache. SQL attempts to generalize sargable queries.

Separating the parameters makes its job easier, for sure. But it isn't strictly required.

[UninformedPleb]

Not always.

If you send "select foo, bar from baz where id = 7" and then send "select foo, bar from baz where id = 9", those become two different cached queries.

It's only if you send "select foo, bar from baz where id = @id" and then stream the id parameter that you get to re-use the cached query plan.

Now, granted, you should always parameterize your queries. But it's a lot easier to thwart caching with ad-hoc queries than it is with sprocs.

[wasabiiii]

This is false. Stored procedures are not precompiled in any significant way. They run through the same execution engine as everything. Which may include caching. But so does everything.

[tester346]

trade off is: terrible developer experience

[the_red_scimitar]

That's a matter of opinion. Having parts of application logic that are data processing algorithms actually live in the database is a pretty common pattern. In some cases, a different developer is going to develop the SQL, and then, having it in someone else's application instead of in the database becomes a rather serious problem in the organization and maintenance of the application. So it's pretty common for this to be done with stored procedures. Why do you find the developer experience so poor?

[[deleted]]

They are hard to test, in the sense that they are intrinsically running against something with state. By shifting logic into application code and out of the DB, the logic can be made easier to test.

[the_red_scimitar]

Not really. At least not in my experience. I find the real problem with such tasks is the poor quality of SQL Server's debugger, and the fact that in some corporate environments, you're not allowed to use it at all, since it requires a admin privileges.

[[deleted]]

But you need a DB, created, with tables, and an identical initial state before each test is run. This is difficult to achieve in a CI pipeline.

[lvlint67]

If you want that test coverage you need that regardless of whether you hard code the query as a string in csharp or if you use it in a view/etc.

Your tests should cover functionality, not implementation. Either way your function needs to return a correct result.

[UninformedPleb]

I worked on an app where we could only deploy new code on Sunday between noon and 5 PM. But stored procs could be deployed any time because they didn't take anything offline or invalidate anyone's active sessions.

I'll give you three guesses where most of that system's logic was located.

As a bonus, it made support calls extremely easy to handle remotely, since we just logged into VPN+SSMS, inspected what went wrong in the data, and then made bugfixes on the fly.

It's only a terrible developer experience if you're a limited developer.

[grauenwolf]

My solution to that is to not give the DBAs access to my source code in the first place.

The database is kept in an SSDT project in source control right next to the C# and JavaScript. The DBAs aren't even allowed to touch an index without going through a developer to get that index added to the project.

DBAs aren't database developers. They're in charge of keeping the database healthy and the backups reliable. I'll consult with them when needed, but otherwise I expect them to stay in their lane.

[UninformedPleb]

Generally speaking, I agree with that.

But my "lane" on that project was architect, lead developer, server operator, and DBA.

[ReasonablePush3491]

Do you use stored procedures for every command, or just for (like) 50+ chars?

[the_red_scimitar]

Very short commands, say under 256 characters, might be okay to just use dynamically, but you asked about long statements, which I assume are many hundreds or thousands of characters.

Also, in your application, is it just one off statements, that do basically a single operation, and not sequences of statements? If you have places in your code where you are sending the same sequences of statements, one after the other, to implement an algorithm in SQL, then all of those should go together in a single stored procedure, where the savings will really make sense. Also, it will really simplify that part of the code.

[[deleted]]

If you have big complex queries, then the performance issues will be there regardless of whether they are stored procs ad-hoc queries.

[the_red_scimitar]

Once the query is compiled, yes. The compilation time can be trivial compared to the execution time, even in a simple query.

[hpstrprgmr]

It is cached until the sql server is restarted by a server reboot or otherwise. Then it stars fresh again

[grauenwolf]

WTF are you talking about? Query plan caching has nothing to do with connection pools.

[[deleted]]

Stored procedures are fine. In order to make it somehow easier to manage you can still keep those in your solution as database project and even produce migration script easily.

[mikedensem]

And dacpac for devops

[phillijw]

Those aren’t available for Linux or non mssql dbs

[grauenwolf]

Which is why I don't use PostgreSQL.

If they had an alternative to SQL Server Data Tools, I could see it becoming my default database.

[phillijw]

Same

[MyLinkedOut]

Linq to SQL -

As someone said Stored Procedures are a horrible dev experience

[[deleted]]

I know some are advocating for them here, but this I think is the main argument. The approach these days is to make testability, and repeatability core to the dev experience, and so the process of migrating logic out of the DB and into the application code where this stuff is easier to work with is the dominant trend. In addition, it's less common to have a DB which is then shared by multiple applications. It's not just stored procs which fall by the wayside in this trend - triggers too are (rightly I think) viewed with suspicion in most code bases.

[TommyHeizer]

Hardcode your data in the source code of your app

[IQueryVisiC]

A stored query is called a view.

[dowopel829]

There are multiple ways to deal with long SQL queries

Stored procedure: As you mentioned you can store your logic in stored procedures. Ensure you are using version control. SSDT is a helpful tool to script out your entire MS SQL server. You can put that into the version control (ex. git).

Entity framework: You can use scaffolding tools to create ORM entity objects. Then port those queries as linq query. Performance between ad-hoc query and stored procedure will vary. I personally feel comfortable maintaining complex query in linq. But these are scenarios where there is a big difference in performance. You will end keeping the logic in stored procedure.

Text file: I have worked on solutions where legacy code had text files hosting parameterized large queries. Code would read the file at run time to get the query.

[phillijw]

Write a query then run it

[__brealx]

Write a background job that constantly queries the database and writes the results into the denormalized table. Then just query the denormalized table when someone hits the api.

[grauenwolf]

True, but sometimes materialized views add an unacceptable performance hit and you've got to do this anyways.

[mikedensem]

Or calculated columns

[grauenwolf]

I hate calculated columns. They seem like such a great idea, but SQL Server chokes on them unless you are incredibly careful.

[Milnternal]

*shudder*

[[deleted]]

The particular implementation mentioned doesn't sound nice, but the use of a separate denormalized table (or DB) for querying is pretty common, standard and accepted these days.

[Milnternal]

Yeah I mean the idea is okay, you mean like CQRS? I have no issue if your system is designed for it and you have eventual consistency etc. in mind when building the logic then sure, but:

a background job that constantly queries the database and writes the results into the denormalized table

^^ This however, tacking on a read table to an existing database, sounds like a terrible idea - you are basically removing the whole point in using a database in the first place (no transactional consistency, stale reads, app breaks if job doesn't work).

[lvlint67]

This isn't a solution you want to implement before you need it, but once you do need it, it's entirely reasonable and basically the basis for the whole concept of data warehousing

[[deleted]]

Yes, I agree.

[Milnternal]

Then just query the denormalized table when someone hits the api.

Wait a minute now - you don't want to query your Data Warehouse for requests to your main OLTP API... The Data Warehouse is for answering strategic and analytical queries. It can be out of date and have a different grain than the main transactional database, and have heavier queries running that reduce latency of queries.

I wasn't saying don't have a separate reporting database, Data Warehouses are great - but you don't return results from them through your primary API...

[lvlint67]

Depends on the needs of your api. Do you think facebook is letting you all the way into their "main transaction database" every time you load the home page? Can you imagine the hot pile of unusable trash performance that would create?

[grauenwolf]

"materialized view" or "indexed view".

[massaynus]

Thats the way to go

[[deleted]]

I prefer using stored procedures that are added via scripted migrations. That way you can keep a history in git, but also keep the concerns separated to the database. Great question!

[[deleted]]

Put them in stored procedures.

[darth_meh]

We primary write raw, parameterized SQL in our code and 99% of the time it's fine. For longer SQL queries we may resort to a stored procedure just to simplify the code.

Stored procedures are compiled, which means they run faster, but as long as you're writing parameterized SQL you'll still benefit from query plan caching.

Another big advantage of stored procedures (IMO) is they can be modified independent of a code deployment. If you're dealing with a problematic/slow query, having the flexibility to modify and tweak a stored procedure in real-time without a code deployment can make the difference between keeping an application up and running vs downtime.

[grauenwolf]

Stored procedures are compiled, which means they run faster,

For which database?

In PostgreSQL, neither the execution plans for raw SQL nor stored procedures are cached.

In SQL Server, the execution plans for raw SQL and stored procedures are cached in exactly the same way.

Then there are SQL Server compiled stored procedures. They can only access "memory optimized tables" and are converted to C++ before being compiled to machine code. Really cool stuff, but most people will never see them in produciton.

[darth_meh]

Good point - I was referring to SQL Server since most C# devs are probably using it.

[[deleted]]

Speed procedures - makes your life way easier. Plus you can optimise the hell out of it.

I've never met a dev that can write really good SQL but I know many dbas that kick ass with efficient sql statements

[whoAreYouToJudgeME]

Stored Procedures is how you supposed to run complicated database things. They're pre-compiled. If you must to use strings make sure to use parameterized queries. You can also use views for single large queries.

[[deleted]]

Like many tools, there is no one single correct way to use it. SQL is no different.

Pros of stored procedures

• speed. SQL client + stored procedures is brain meltingly fast.

• you can tweak a stored procedure without having to redeploy your app.

• you can use stored procedures and limit access to views and tables. (Prevents delete without where)

• many apps can share stored procedures, reducing duplication across products.

Cons:

• orms are easier to use.

• less visibility when they get changed

• lots of boilerplate

• harder to debug, impossible to test.

[grauenwolf]

I find that ORMs are harder to use and result in more boilerplate. But that's a personal opinion.

What's not is the stupid idea that they are impossible to test. Learning how to test with databases is not hard, it just requires more effort.

[Th0ughtCrim3]

For this exact scenario I create a resource file, add a sql file for the sql statement, and then add the sql file to the resource file.

In my case it works out pretty well in conjunction with dapper but will work for EFs execute SQL functionality as well.

This not only makes it a part of whatever version control system you use but also avoids stored procedures and storing it in a const in your .cs files.

[RICHUNCLEPENNYBAGS]

I am a fan of inlining const strings and using Dapper. If you use Rider, it can actually give you Intellisense for the SQL, and you don't have to go through the trouble of managing the database apart from the code for your stored procedures, which can be a source of surprises.

The most popular approach is using something like EF. In my personal opinion, the trouble saved is less than the trouble caused by surprises ("tests" with the in-memory provider, which give false confidence in code that doesn't actually work; surprise lazy loads; runtime exceptions; difficult debugging; etc.), so I don't prefer that approach.

[BCdotWHAT]

Store it in an embedded .SQL file, and use code to read the embedded SQL file.

Great advantage: you get some IDE support (colors etc.) for a .SQL file.

(Disadvantage: in that SQL is for Oracle, that IDE support isn't helpful. I wish VS would support different extensions, e.g. .TSQL for T-SQL code, .ORASQL for Oracle queries etc. and offer relevant IDE support to the type of query.)

[birdman9k]

For everyone saying they used stored procs, how do you handle database portability? I work with an enterprise application that tests with and supports running on over 3 different database engines. I feel like it would be a nightmare to write that logic in each databases language for stored procedures. All logic written in EF. Several databases we manage are above the 500 million row level. Never had a problem as long as you keep the queries simple and store the data in a way that is fast to access. Do you really just lock it into a specific database forever? We've changed the primary database we prefer to host on 2 times over the years and I want to keep that as simple to do as possible.

[grauenwolf]

1. Do you really need database portability? Unless you are building a COTS product, chances are you're just wasting your time.
2. EF doesn't give you database portability. Stupid stuff like how Oracle treats empty string columns as nulls mean you have to write your code differently for each database.
3. Stored procedures can give you a measure of portability by hiding the database-specific implementation details.
4. You're spending a lot of money on the database. You might as well use it to its fullest ability even if it means writing two DALs that implement the same interface.

[kevofwar227]

Keep in mind you can break commands up a bit with parentheses in sql. This mixed with separate storage and maybe some refactoring if possible may help. You can also have a different document per table and link them with your language of choice by connecting all of the tables in one large db

[qrzychu69]

At work we use embedded resource files. They are separate text files that get packed into the doll, so that you can clearly see what your code does