September 16, 2014 at 3:02 am
Ask a developer to use a stored procedure and majority will look at you as if you are mad.
Not really in my experience. Some are better than others, but it's not hard to include a few simple test cases for the edge scenarios in a proc, as well as spot checks against known test data.
ORM is not newbie friendly. Stop giving it to them.
Application development in general is not friendly to UNSUPERVISED newbies but they can produce useful work with ORMs in say a triad with a senior and or mid level dev.
Depending on your application much required functionality is little used or is very straightforward, maybe for admin, config etc - often 80% of a project code base in my experience. No performance tuning is required and to implement such using procs is essentially premature optimisation. The 20% that is heavily used or uses intensive processing - sure, write the procs. If you get more usage later unexpectedly for a particular area and it causes problems - write the procs. No big deal.
September 16, 2014 at 5:40 am
call.copse (9/16/2014)
Ask a developer to use a stored procedure and majority will look at you as if you are mad.
Not really in my experience. Some are better than others, but it's not hard to include a few simple test cases for the edge scenarios in a proc, as well as spot checks against known test data.
You have been lucky then.
Agree, that they can be trained and test cases can be created etc. But there does have to be buy in. If the manger (pro MySQL/OpenSource) and director(semi technical) tend to say logic in the database is bad or business logic cant be done in the database then you are back to: ORMS Good, TSQL Bad.
Cant think of any business rules that I have come across that cant be done in tsql.
September 16, 2014 at 6:38 am
It's not really a case of when business rules can't be done in the database it's a case of that its a pretty bad practice to duplicate rules or logic.
At the end of the day the database is a storage tool and another medium is always there for presentation.
As classes/models start to interact or are inherited / interfaced / abstracted from others, that's where an ORM really shines and there's no way a database can/should contain the logic like that. At the end of the day stored procs or views start to become a maintenance nightmare on any sufficiently large system.
Also as things change in memory the system shouldn't be running of to the DB to ask it how to behave, that should be in the business logic which should be in your real world entities.
September 16, 2014 at 6:41 am
call.copse (9/16/2014)
Depending on your application much required functionality is little used or is very straightforward, maybe for admin, config etc - often 80% of a project code base in my experience. No performance tuning is required and to implement such using procs is essentially premature optimisation. The 20% that is heavily used or uses intensive processing - sure, write the procs. If you get more usage later unexpectedly for a particular area and it causes problems - write the procs. No big deal.
Definitely agree!
And those procs are always the ones that cause headaches as the rules change. Especially if you realise that half the rules are in the DB as well as the app.
September 16, 2014 at 6:55 am
courtney.smith (9/16/2014)
It's not really a case of when business rules can't be done in the database it's a case of that its a pretty bad practice to duplicate rules or logic.At the end of the day the database is a storage tool and another medium is always there for presentation.
As classes/models start to interact or are inherited / interfaced / abstracted from others, that's where an ORM really shines and there's no way a database can/should contain the logic like that. At the end of the day stored procs or views start to become a maintenance nightmare on any sufficiently large system.
Also as things change in memory the system shouldn't be running of to the DB to ask it how to behave, that should be in the business logic which should be in your real world entities.
I will show my age: I come from the era of client-server development and much preferred the rules in the database. Impact analysis was easier, scaling up was easier, security was easier.
September 16, 2014 at 7:09 am
Yet Another DBA (9/16/2014)
courtney.smith (9/16/2014)
It's not really a case of when business rules can't be done in the database it's a case of that its a pretty bad practice to duplicate rules or logic.At the end of the day the database is a storage tool and another medium is always there for presentation.
As classes/models start to interact or are inherited / interfaced / abstracted from others, that's where an ORM really shines and there's no way a database can/should contain the logic like that. At the end of the day stored procs or views start to become a maintenance nightmare on any sufficiently large system.
Also as things change in memory the system shouldn't be running of to the DB to ask it how to behave, that should be in the business logic which should be in your real world entities.
I will show my age: I come from the era of client-server development and much preferred the rules in the database. Impact analysis was easier, scaling up was easier, security was easier.
I too come from that era (through the standard passage of time). What I think is missed is that there can be a difference between business data rules and business application rules.
As one may gather, I recommend that the appropriate place for business logic lies in its scope i.e. business data rules should exist in the database whereas application specific business rules belong outside the database (let's not discuss n-Tier architecture, or not, here).
The key thing is that business data rules are always applicable regardless of the client application/system and when it changes in one system it requires changing all. Application business rules MAY be shared between applications but also MAY NOT. This belongs outside the database and it is up to the application developers to decide whether it is required to be produced as a component in order to facilitate reuse.
Of course, some databases may be satellite DBs and are considered only as part of an application/sub-system as opposed to part of the enterprise data repository landscape.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
September 16, 2014 at 7:37 am
Gary Varga (9/16/2014)[ ...
What I think is missed is that there can be a difference between business data rules and business application rules.
...
All too often I just hear from the developers, well those who don't understand databases very well, is the it is NHibenate, EF, ORM, that does the "Business Rules". No distinction is made between "business application rules" and "business data rules".
Same developers who then want a report done via a stored procedure/view and then want tsql to format the data, and I do mean string manipulation that is more suited for the presentation layer.
Well it keeps me in a job.
September 16, 2014 at 7:39 am
Evil Kraig F (9/15/2014)
1) If you open a transaction from any server but the database server, I will shoot you, publically, and it will be considered a justifiable homicide. Lost connections are no joke when you lock tables out.2) Implementation of standardized and parameterized queries is apparently difficult. The majority of them I've ran headfirst into run unwashed sql_executes. 40 billion execution plans in the cache, and you want me to figure out why *1* of those ran slowly? Also, who's Bobby Tables again?
3) Volume Controls: Can ORM even implement this stuff? I've seen entire tables downloaded to cache when pulling up the record for a single user.
Those are some very big problems that are common among ORM implementations. I have also seen tools such as Entity Framework decide that it needs to send the exact same call 30 times - essentially bringing down the app. 10k concurrent users sending, what should be a single call, the same call 30x each can be a bit of a problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2014 at 7:47 am
Yet Another DBA (9/16/2014)
...Same developers who then want a report done via a stored procedure/view and then want tsql to format the data, and I do mean string manipulation that is more suited for the presentation layer...
Nooooooooooo!!!
So when someone wants the same data in a different report, the display configurable per user or, heaven forfend, the report dynamically generated by the client then they have right royally stuffed themselves.
Makes me ashamed to be called a developer sometimes. These people give us all bad names.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
September 16, 2014 at 8:01 am
Gary Varga (9/16/2014)
Yet Another DBA (9/16/2014)
...Same developers who then want a report done via a stored procedure/view and then want tsql to format the data, and I do mean string manipulation that is more suited for the presentation layer...Nooooooooooo!!!
I couldn't agree more.
Think about separation of concerns (SoC)! o_O
Devs like that are as useful as a catflap in an elephant house.
I have a lot to learn, but I, too, am embarassed by the level of competence of some colleages sometimes.
@SQLRNNR: That is not the ORM deciding, that is a dev neglecting.
Typically a dev that does not grasp the concepts of an ORM and the responsibilities that come with it.
Just like you can screw up performance by writing a really bad SQL statement, you can seriously screw up an ORM by not thinking about performance and only about ease-of-use.
It would be analogous to writing a terrible SQL statement and then being surprised that the DBMS wasn't able to optimize it to something speedy.
An ORM is NOT a silver bullet or a magical now-I-can-shutdown-the-brain kinda contraption.
In this case, I am certain that the dev enumerated a list of records, traversing the relations without pre-loading that set of records with the referenced relations (eager loading).
The ORM had no idea the dev was going to traverse those relations when it fetched the records and had to fetch the related records one-by-one when the dev enumerated through the records.
EF, for instance, fetches those related records by itself (no code needed), to help the dev. This is seriously helpful in most cases, but a new dev WILL create those 10K queries.
The dev should have fetched the records, eagerly loading the relations as well. EF would have issued 1 query to fetch all of it, including the related records.
The dev could then blazingly fast use all the mega complex logic he/she wanted on the set, and after that issue the 'save' instruction to EF. EF would issue one batched request to the DB to store all changes.
I referenced EF, but (N)Hibernate and most of the other ORMs work the same way.
Sometimes I just feel like a one-legged man in an a$$-kicking contest trying to explain this one.
In short: replace the dev, not (necessarily) the ORM.
A typical PEBKAC.
September 16, 2014 at 11:10 am
columnae (9/16/2014)
@SQLRNNR: That is not the ORM deciding, that is a dev neglecting.Typically a dev that does not grasp the concepts of an ORM and the responsibilities that come with it.
Just like you can screw up performance by writing a really bad SQL statement, you can seriously screw up an ORM by not thinking about performance and only about ease-of-use.
It would be analogous to writing a terrible SQL statement and then being surprised that the DBMS wasn't able to optimize it to something speedy.
An ORM is NOT a silver bullet or a magical now-I-can-shutdown-the-brain kinda contraption.
In this case, I am certain that the dev enumerated a list of records, traversing the relations without pre-loading that set of records with the referenced relations (eager loading).
The ORM had no idea the dev was going to traverse those relations when it fetched the records and had to fetch the related records one-by-one when the dev enumerated through the records.
EF, for instance, fetches those related records by itself (no code needed), to help the dev. This is seriously helpful in most cases, but a new dev WILL create those 10K queries.
The dev should have fetched the records, eagerly loading the relations as well. EF would have issued 1 query to fetch all of it, including the related records.
The dev could then blazingly fast use all the mega complex logic he/she wanted on the set, and after that issue the 'save' instruction to EF. EF would issue one batched request to the DB to store all changes.
I referenced EF, but (N)Hibernate and most of the other ORMs work the same way.
Sometimes I just feel like a one-legged man in an a$$-kicking contest trying to explain this one.
In short: replace the dev, not (necessarily) the ORM.
A typical PEBKAC.
You jumped to some pretty big conclusions there.
1. I said ORM implementation - didn't say it was the fault of the ORM per se.
2. The query in question is not loading any relations or traversing anything additional. It is a set based return to display a simple result set from a stored procedure call. This call by EF is being executed by EF 30 times for each single click. There is nothing in code to call it that many times. Nor is there any reason for it to execute 30 times except that there is a bug in EF.
3. ORMs are great. Many clients implement them, run into massive performance issues and then call for help. I am more than happy to help a client get around the performance issues imposed by such implementations.
Can an ORM help a DEV code faster? Sure! But coding faster does not equate to faster code. Hence my previous comment in agreement that it should be perf tuned - as with all code. In this case, the code definitely needs to run through the hands of a seasoned DBA (a little garlic sea salt seasoning would do).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2014 at 11:45 am
SQLRNNR (9/16/2014)
...But coding faster does not equate to faster code...
..which does not necessarily faster delivery 😉
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
September 16, 2014 at 12:07 pm
courtney.smith (9/16/2014)
At the end of the day stored procs or views start to become a maintenance nightmare on any sufficiently large system.
I would posit that it's easier to maintain the views and procs on a large system where the calls are explicitly made from the component in question then having to root through the ORM and abstractions to figure out what needs to be modified for a front end change.
I have maintained databases with 200-300 tables and roughly a 1000-1200 procs for a massive online E-Commerce site. Though, admittedly, we had about 4 views and the majority of the work was concentrated into around 100 procs (or so, I never did finish my accounting on that). The rest were for EOY reports or taxes, one shot procs for delivering the un-mailed invoices to the mailing room, stuff like that. Once an hour kind of items, at most.
Naming conventions, intelligent Schema usage for module identification, and communication between the front and back end of the code for determination of usage keep that maintenance from being a nightmare. You do have to bring your brain to work every day though. It can be easy to trip. I can't see a sufficiently large front end being any better, though, ORM or not.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 16, 2014 at 2:55 pm
Yet Another DBA (9/16/2014)
It will always be an ongoing battle.Ask a developer to use a dll, RESTful service ... Fine no worries.
Ask a developer to use a stored procedure and majority will look at you as if you are mad.
The arguments I have heard:
* I cant debug a stored procedure
* it takes too long to write the extra code
* I cant see whats happening.
* What happens if I need to add a column
Begin trans <Rant Mode>
Welll I must not be in the majority, nor almost all the people I have ever worked around. RESTful Service No Problem! Stored procedure No Problem! Relational model love it! Denormalized model for BI even more fun. If it has code GREAT! Where is the problem!
Can't debug a stored procedure translates to not creative enough to understand how to do it, and not knowing how to monitor state as you build. SQL is just another language with an interesting syntax. It is fun, exciting, and can really do a ton of really cool things on the big iron where your data lives.
"It takes too long to write extra code" means that there is a lazy factor there that needs to be attended to.
"I cannot see what happens" is really simple foolishness. There are only a very few developers today who have even a remote idea what their code does and what is hidden from them during the execution of their java, javascript, python, or .net code.
And "What happens if I need to add a column?" is obvious. You add a column and do a little work to make the magic happen. If it changes some of your really spiffy code, so what. That spiffy code did not do the things you needed to do when the data you need is fully in place.
Who was it that said "Just Do IT!" Well get off it and do it!
End Trans <Rant Mode>
Have a better day!
M.
Not all gray hairs are Dinosaurs!
September 17, 2014 at 5:35 am
I hear you Miles. If you listen carefully you might be able to hear these people:
"Wah. Wah. It should all just automatically work. Wah Wah."
Lazy.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply