September 21, 2017 at 11:42 pm
crow1969 - Thursday, September 21, 2017 9:34 AMI would guess the conversion routine simply drops all thousands separators, and then converts to a numeric type:declare @var money
set @var = ',1,00,00.99'
select @var
Z'actly.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2017 at 9:54 am
Jeff Moden - Thursday, September 21, 2017 11:42 PMcrow1969 - Thursday, September 21, 2017 9:34 AMI would guess the conversion routine simply drops all thousands separators, and then converts to a numeric type:declare @var money
set @var = ',1,00,00.99'
select @varZ'actly.
Just like this SELECT CONVERT(int, '')
Equals 0 ???
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 22, 2017 at 7:24 pm
Michael L John - Friday, September 22, 2017 9:54 AMJeff Moden - Thursday, September 21, 2017 11:42 PMcrow1969 - Thursday, September 21, 2017 9:34 AMI would guess the conversion routine simply drops all thousands separators, and then converts to a numeric type:declare @var money
set @var = ',1,00,00.99'
select @varZ'actly.
Just like this
SELECT CONVERT(int, '')
Equals 0 ???
Heh... Z'actly again! 😉
Nothing = Nothing... Null is not Nothing. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2017 at 5:31 pm
Entity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.
September 25, 2017 at 7:18 am
Jason A. Long - Saturday, September 23, 2017 5:31 PMEntity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.
I've come across a couple of places where a database allowed full access to apps to push in any SQL they liked. Both times the apps that did that were not a catastrophe waiting to happen, they were a catastrophe that had already happened and the databases that they manipulated were riddled with problems (hopeless security. not referential integrity, no unique keys). SO I don't think your initial reaction was way off base.
Maybe if the database stuff was done with LINQ things would not be as bad as if everything was a mixture of dynamically SQL built on the fly by code in C# and SQL was devised by C# developers who had never heard or normalisation and embedded in their C# app. But I would want to be sure that the system had a proper modular structure rather than being a single great big chunk.
What is essential is a clear definition of the interface between the app and the database as separate modules with a formally defined interface between them covering database integrity and security. If there is such a definition and it does properly ensure database integrity and security it probably doesn't really matter whether the database side is wholely SQL or is SQL embedded in C# code. But experience has made me distrustful of developers' assertions about modularity and adequately narrow interfaces between modules. Some development teams don't care about modularity or minimalisation of comlexity so they never actually achieve a modular design, even though they may convince themselves that they have done so.
Tom
September 25, 2017 at 7:19 am
Jason A. Long - Saturday, September 23, 2017 5:31 PMEntity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.
I'd say that if they are mostly doing simple CRUD operations, then managing the model through EF is probably OK (at least it's fairly standard in the .NET world). The upside is the ability for the .NET developers to represent the data model in their layer.
If they are constructing more complex queries, especially ones that encapsulate business logic, my preference would be to used stored procedures. This pattern probably works better in a monolithic architecture than a service-based one.
September 25, 2017 at 7:22 am
TomThomson - Monday, September 25, 2017 7:18 AMWhat is essential is a clear definition of the interface between the app and the database as separate modules with a formally defined interface between them covering database integrity and security.
Also, this ^^^^.
September 25, 2017 at 10:23 am
TomThomson - Monday, September 25, 2017 7:18 AMI've come across a couple of places where a database allowed full access to apps to push in any SQL they liked. Both times the apps that did that were not a catastrophe waiting to happen, they were a catastrophe that had already happened and the databases that they manipulated were riddled with problems (hopeless security. not referential integrity, no unique keys). SO I don't think your initial reaction was way off base.
Maybe if the database stuff was done with LINQ things would not be as bad as if everything was a mixture of dynamically SQL built on the fly by code in C# and SQL was devised by C# developers who had never heard or normalisation and embedded in their C# app. But I would want to be sure that the system had a proper modular structure rather than being a single great big chunk.What is essential is a clear definition of the interface between the app and the database as separate modules with a formally defined interface between them covering database integrity and security. If there is such a definition and it does properly ensure database integrity and security it probably doesn't really matter whether the database side is wholely SQL or is SQL embedded in C# code. But experience has made me distrustful of developers' assertions about modularity and adequately narrow interfaces between modules. Some development teams don't care about modularity or minimalisation of comlexity so they never actually achieve a modular design, even though they may convince themselves that they have done so.
Thanks for the feedback Tom. I think we're of similar mindsets on this topic. I the more I've looked into this, the more it seems like a fundamentally flawed concept that all but precludes the possibility of doing anything that even remotely resembles query tuning... Even the articles that defend the use of ORMs warn that the developer must be careful to take steps to prevent iterative calls (orm generated cursors) and "SELECT *...".
I figure, even under the best possible scenario, where the table structure is properly normalized and DRI is full implemented, the constant flow of nothing but ad-hoc queries would be generating constant stream of non-reusable query plans making the plan cache not just worthless but detrimental as it will be consuming memory resources forcing data pages out of cache meaning increased disk I/O.
Of course, I don't think that best case scenario is even a remote possibility... Over the past few years, I've worked with a variety of extremely talented C# developers (I'm not bagging on them at all) who simply think of the database as a "data persistence engine" for their application... and the normal forms are just esoteric theory that they had to sit through in some class a long time ago and DRI exists only to make it difficult to load data... and... apparently set based logic seems to run completely contrary to the OOP developer mindset.
Even if nothing above were an issue, removing the database logic from the database, means that it would be extraordinarily difficult (if not impossible) to ever migrate to a new application.
September 25, 2017 at 12:29 pm
According to the asterisk, SQL Server 2017 will be available for download on October 2nd.
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
September 25, 2017 at 12:55 pm
Yep, GA announced at Ignite today
September 25, 2017 at 5:48 pm
Jason A. Long - Saturday, September 23, 2017 5:31 PMEntity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.
Yes. That's the same type of environment I walked into almost 6 years ago. There had been 4 years of silly cowboy design and development. We had 4 to 5 10 minute outages a day while something ran. The front end code kept 32 CPUs hopping at 40% average. I "moved in" right in the middle of all the front end Developers so I could hear them when they had a problem and offered my assistance. I picked the top 10 worst queries (which are almost never the longest running queries in such an environment) and show the Developers how they could be improved either using their front-end-centric code or the more sane way of using stored procedures. Once they saw the rather simple changes and how much improvement it made, they were the ones that began adopting the use of stored procedures and now brag about how much they improved code especially the legacy code the previous regime left us. It's kind of ironic that our front end Developers have become better database programmers than most database programmers that I know.
Today, most of the time, the code is consuming about 5-6% CPU and we have 200 times more data and 5 times (500) more connections than we had back then and things usually sing. I'd set up a fairly aggressive "I'll find the problems and together we'll fix them" "Continuous Improvement Prrogram", 100% peer reviews which doubled as mentoring sessions and, once they saw what we were doing, had full management support shortly after we started.
It was a great adventure and still is. It could, however, have been am absolute nightmare if management didn't buy in or the Developers simply gave me the bird. Perhaps me putting on my "Developers Hat" and attitude won the Developers over. Perhaps they were tired of the black eyes they were getting when the system screamed for help. Perhaps I just got bloody lucky. In any case, I think what happened is more rare than not especially with some of the Developers that "know it all". So, no... I don't think your reaction was off base in the least.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2017 at 10:51 pm
Jeff Moden - Monday, September 25, 2017 5:48 PMJason A. Long - Saturday, September 23, 2017 5:31 PMEntity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.Yes. That's the same type of environment I walked into almost 6 years ago. There had been 4 years of silly cowboy design and development. We had 4 to 5 10 minute outages a day while something ran. The front end code kept 32 CPUs hopping at 40% average. I "moved in" right in the middle of all the front end Developers so I could hear them when they had a problem and offered my assistance. I picked the top 10 worst queries (which are almost never the longest running queries in such an environment) and show the Developers how they could be improved either using their front-end-centric code or the more sane way of using stored procedures. Once they saw the rather simple changes and how much improvement it made, they were the ones that began adopting the use of stored procedures and now brag about how much they improved code especially the legacy code the previous regime left us. It's kind of ironic that our front end Developers have become better database programmers than most database programmers that I know.
Today, most of the time, the code is consuming about 5-6% CPU and we have 200 times more data and 5 times (500) more connections than we had back then and things usually sing. I'd set up a fairly aggressive "I'll find the problems and together we'll fix them" "Continuous Improvement Prrogram", 100% peer reviews which doubled as mentoring sessions and, once they saw what we were doing, had full management support shortly after we started.
It was a great adventure and still is. It could, however, have been am absolute nightmare if management didn't buy in or the Developers simply gave me the bird. Perhaps me putting on my "Developers Hat" and attitude won the Developers over. Perhaps they were tired of the black eyes they were getting when the system screamed for help. Perhaps I just got bloody lucky. In any case, I think what happened is more rare than not especially with some of the Developers that "know it all". So, no... I don't think your reaction was off base in the least.
That's awesome. Sounds like you were able to step in and make all the right moves and things turned out far better than they otherwise could have.
My follow up question, then, would be: Were you brought on board because management knew they had a problem on their hands and they knew they needed someone of your caliber to get things back on track? or... Did you come on board and then discover that things were rotten in Denmark?
In other words, were they open to change because they already knew they had to change?
The reason I ask, while I was speaking with the group, they didn't give any indication that there were any problems that needed to be addressed. The current DBA is transitioning into a new BI role and they need someone to to fill that slot.
Given that 60% of the questions were centered around backups, I got the impression that the position mostly centered on babysitting backups and making sure the various DBs could be restored if need be....
I tried to dig a little deeper, by mentioning that it had been my experience that, outside of very simple queries, machine generated SQL tended to leave a lot to be desired in terms of plan optimization. The response: if a problem query is identified, kick it back to the application developer and have them improve the LINQ. Stored procs are used only as a weapon of last resort... I didn't get the impression that they had any current performance concerns nor did I get the impression that they were open to altering the current methodology. If anything, I got the impression that they were interested in exploring the newest, latest & greatest technologies and database side stored procs would a step backwards.
September 26, 2017 at 1:14 am
Jason A. Long - Monday, September 25, 2017 10:51 PMThe reason I ask, while I was speaking with the group, they didn't give any indication that there were any problems that needed to be addressed. The current DBA is transitioning into a new BI role and they need someone to to fill that slot.
Given that 60% of the questions were centered around backups, I got the impression that the position mostly centered on babysitting backups and making sure the various DBs could be restored if need be....
I tried to dig a little deeper, by mentioning that it had been my experience that, outside of very simple queries, machine generated SQL tended to leave a lot to be desired in terms of plan optimization. The response: if a problem query is identified, kick it back to the application developer and have them improve the LINQ. Stored procs are used only as a weapon of last resort... I didn't get the impression that they had any current performance concerns nor did I get the impression that they were open to altering the current methodology. If anything, I got the impression that they were interested in exploring the newest, latest & greatest technologies and database side stored procs would a step backwards.
Sounds more and more like your original instinct to get the heck out of there was spot on. They seem to equate the DBA function to babysitting backups, checking job results, and monitoring disk sizes. You (and everyone who frequents this thread) knows it should be more, so this is not the job for you.
The problem with badly tuned queries is that they tend to result in exponential scaling performance. And the problem with exponential scaling performance is that it can go unnoticed for a long time (unless you know what to look for, but those companies don't know and don't want to look). Then at one point it starts to feel a bit sluggish - and with a matter of weeks the application grinds to a halt. These are not issues that give you months or years to fix from first being visible, they give you time to fix when you look for them and detect them early but not when you wait until brown stuff starts hitting the fan.
You do not want to be around in a situation where you see the future problems and want to fix them, but nobody wants to listen. You might be the person they go to once their business grows enough that they start getting problem, and they start spiraling out of control. Whether or not you should step in at that time depends on how much you enjoy working in a crisis situation. (Most people like the end, where they emerge victoriuous and are praised as the hero that saved the day. But do not forget that that's the end of a period where you will face huge pressure from management, and often still a lot of backlash from the traditional development team).
September 26, 2017 at 2:51 am
Tom_Hogan - Monday, September 25, 2017 12:29 PMAccording to the asterisk, SQL Server 2017 will be available for download on October 2nd.
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
And now the fun of getting quotes and readying business proposals begins! Heh.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 59,971 through 59,985 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply