In my experience, it saves a lot of dev time, using my current ORM (Linq with Entity Framework 4.0) I can get probably-fast-enough CRUD against a new entity in just a few minutes without writing any SQL at all.
When it's not fast enough (high volume, specific complex queries, whatever) I can write specifically tuned SQL.
Performance tuning on anything that isn't an actual bottleneck is waste.
Realistically, you should never be writing your own CRUD, whether you use an ORM or not.
CRUD stored procedures can be generated directly from the database schema, wrapped in C# objects using the same code generator, and compiled into your project whenever you make a schema change. That gives you all the advantages of an ORM, without any magic runtime SQL generation.
Serious question: Why is having stored procedures generated at design time better than having dynamic SQL generated at run time? It's not like there has been a meaningful performance benefit for the last several years.
I'd also like to hear a reasonable answer to that question.
For me, the only advantages of stored Procs over straight SQL (generate or hand coded) are:
* Security - you can revoke rights to directly access / change the tables from the app layer, making all access be through procs. However, in general you can probably create a similar security setup using views.
* Many SQL calls that need to be run as a set to give a single answer - this avoids the round trip latency of firing many SQL calls from the application.
I'd am genuinely interested to hear of more advantages.
No, performance isn't really a reason to go with Stored Procedures.
It's all about the readability, maintainability and debugability of your code. If you have everything generated in simple classes backed by simple stored procedures, you can look at a stack trace and immediately see what's going on without having to mentally unravel what you think your ORM might have been doing at that point. That is, you'll be looking at a single line of code that does a single thing, rather than a null reference in a hashtable of hashtables that was loaded at runtime from an XML file.
It's also a lot less duplication of effort. Realistically, the SQL calls that your app makes for CRUD actions will only ever change when you change your schema. With that in mind, it seems a bit wasteful to re-generate that same SQL over and over again with every page request. In my mind I'd rather build it all once after a schema change, and drop it into source control where it's part of the record of what your code is doing.
You can't write it faster than an ORM. anObject.save, your SQL can't beat that in tersness and by the time you've written CRUD SQL for one table the guy with the ORM is far ahead of you in getting actual work done.
> I don't think it saves dev time at all.
It saves massive amounts of time, if you don't think so, then I doubt you've used one long enough to be able to have a valid opinion on the matter.