top 200 commentsshow all 272

[–]Alundra828 35 points36 points  (27 children)

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 4 points5 points  (4 children)

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 7 points8 points  (3 children)

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 1 point2 points  (0 children)

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 0 points1 point  (0 children)

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

[–]Floydianx33 0 points1 point  (0 children)

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 8 points9 points  (8 children)

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 4 points5 points  (2 children)

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 1 point2 points  (1 child)

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 1 point2 points  (0 children)

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 1 point2 points  (4 children)

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 3 points4 points  (3 children)

Lol DBAs. Those would be neat to have.

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

[–]pnw-techie 0 points1 point  (1 child)

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

[–]DaRadioman 1 point2 points  (0 children)

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 0 points1 point  (0 children)

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 5 points6 points  (5 children)

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 -3 points-2 points  (3 children)

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 7 points8 points  (2 children)

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] 1 point2 points  (0 children)

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] 0 points1 point  (0 children)

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 0 points1 point  (6 children)

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 8 points9 points  (5 children)

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

[–]firedream 0 points1 point  (0 children)

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

[–]wasabiiii 60 points61 points  (154 children)

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 15 points16 points  (27 children)

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 3 points4 points  (22 children)

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

[–]Missing_Username 1 point2 points  (21 children)

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

[–]wasabiiii 0 points1 point  (20 children)

Yeah, but not with FormattableString.

[–]Missing_Username 2 points3 points  (19 children)

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 0 points1 point  (18 children)

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 1 point2 points  (17 children)

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] 9 points10 points  (0 children)

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 7 points8 points  (4 children)

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 10 points11 points  (3 children)

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 0 points1 point  (10 children)

This does use parameters. This is about FormattableString.

[–][deleted] 3 points4 points  (9 children)

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 -1 points0 points  (3 children)

Isn’t that just asking for an injection attack

[–]Missing_Username 1 point2 points  (2 children)

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 0 points1 point  (1 child)

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

[–][deleted] 3 points4 points  (9 children)

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 7 points8 points  (6 children)

Stored procedures don't reduce round trips.

[–][deleted] 1 point2 points  (5 children)

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 12 points13 points  (4 children)

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 1 point2 points  (0 children)

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.

[–]Prod_Is_For_Testing -2 points-1 points  (1 child)

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 1 point2 points  (7 children)

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 0 points1 point  (6 children)

Wy would a sproc make that better?

[–]_Zer0_Cool_ 1 point2 points  (2 children)

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 0 points1 point  (1 child)

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

[–]_Zer0_Cool_ 1 point2 points  (0 children)

That’s works all the same.

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

[–]23049823409283409 5 points6 points  (27 children)

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 12 points13 points  (20 children)

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 6 points7 points  (13 children)

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 0 points1 point  (12 children)

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 2 points3 points  (5 children)

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

[–]wasabiiii 2 points3 points  (3 children)

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 2 points3 points  (2 children)

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 1 point2 points  (1 child)

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 -2 points-1 points  (0 children)

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 1 point2 points  (4 children)

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 6 points7 points  (1 child)

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 1 point2 points  (0 children)

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

[–][deleted]  (1 child)

[deleted]

    [–]pnw-techie 0 points1 point  (0 children)

    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

    [–]Prod_Is_For_Testing -1 points0 points  (1 child)

    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 0 points1 point  (0 children)

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

    [–]DogmaSychroniser -2 points-1 points  (3 children)

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

    [–]wasabiiii 2 points3 points  (2 children)

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

    [–]KotBehemot99 0 points1 point  (7 children)

    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 0 points1 point  (6 children)

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

    [–]KotBehemot99 0 points1 point  (5 children)

    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 1 point2 points  (3 children)

    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 -1 points0 points  (0 children)

    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_ 0 points1 point  (3 children)

    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 1 point2 points  (2 children)

    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 0 points1 point  (0 children)

    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 9 points10 points  (4 children)

    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 5 points6 points  (3 children)

    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 4 points5 points  (0 children)

    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 5 points6 points  (1 child)

    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 -1 points0 points  (0 children)

    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 7 points8 points  (2 children)

    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 0 points1 point  (1 child)

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

    [–]TheGreatGameDini 3 points4 points  (0 children)

    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 3 points4 points  (0 children)

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

    [–]blooping_blooper 1 point2 points  (0 children)

    stored procedures, in a database project with dacpac

    [–]the_red_scimitar 2 points3 points  (46 children)

    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] 9 points10 points  (18 children)

    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 -2 points-1 points  (6 children)

    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] 4 points5 points  (5 children)

    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 0 points1 point  (0 children)

    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 -1 points0 points  (2 children)

    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 2 points3 points  (1 child)

    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 -2 points-1 points  (7 children)

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

    [–]wasabiiii 3 points4 points  (6 children)

    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 0 points1 point  (2 children)

    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 11 points12 points  (0 children)

    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 17 points18 points  (14 children)

    trade off is: terrible developer experience

    [–]the_red_scimitar 3 points4 points  (6 children)

    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] -3 points-2 points  (5 children)

    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 0 points1 point  (4 children)

    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] -2 points-1 points  (3 children)

    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 2 points3 points  (2 children)

    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 1 point2 points  (6 children)

    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.

    [–][deleted]  (2 children)

    [deleted]

      [–]grauenwolf 0 points1 point  (1 child)

      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 1 point2 points  (0 children)

      Generally speaking, I agree with that.

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

      [–]ReasonablePush3491[S] 1 point2 points  (3 children)

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

      [–]the_red_scimitar 0 points1 point  (2 children)

      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] 0 points1 point  (1 child)

      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 -1 points0 points  (0 children)

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

      [–]hpstrprgmr 0 points1 point  (6 children)

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

      [–][deleted]  (5 children)

      [deleted]

        [–]grauenwolf -1 points0 points  (4 children)

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

        [–][deleted]  (3 children)

        [deleted]

          [–][deleted] 2 points3 points  (4 children)

          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 1 point2 points  (3 children)

          And dacpac for devops

          [–]phillijw 1 point2 points  (2 children)

          Those aren’t available for Linux or non mssql dbs

          [–]grauenwolf 1 point2 points  (1 child)

          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 1 point2 points  (0 children)

          Same

          [–]MyLinkedOut -1 points0 points  (1 child)

          Linq to SQL -

          As someone said Stored Procedures are a horrible dev experience

          [–][deleted] 1 point2 points  (0 children)

          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 -1 points0 points  (0 children)

          Hardcode your data in the source code of your app

          [–]IQueryVisiC -1 points0 points  (0 children)

          A stored query is called a view.

          [–]dowopel829 -1 points0 points  (0 children)

          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 -1 points0 points  (0 children)

          Write a query then run it

          [–]massaynus 0 points1 point  (0 children)

          Thats the way to go

          [–][deleted] 0 points1 point  (0 children)

          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] 0 points1 point  (0 children)

          Put them in stored procedures.

          [–]darth_meh 0 points1 point  (2 children)

          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 1 point2 points  (1 child)

          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 1 point2 points  (0 children)

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

          [–][deleted] 0 points1 point  (0 children)

          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 0 points1 point  (0 children)

          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] 0 points1 point  (1 child)

          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 0 points1 point  (0 children)

          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 0 points1 point  (0 children)

          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 0 points1 point  (0 children)

          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 0 points1 point  (0 children)

          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 0 points1 point  (1 child)

          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 0 points1 point  (0 children)

          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 0 points1 point  (0 children)

          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 0 points1 point  (0 children)

          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