August 15, 2012 at 9:25 am
call.copse (8/15/2012)
Ouch! Nice monstering GSquared! I'm sure I deserved it.By default if you query an object in EF / L2S you will get all (mapped) columns back. This is not done by a SELECT * but a generated statement retrieving all columns. That is why I termed it a straw man - neither ORM uses a * in any emitted SQL, sorry for any confusion. I have been there and am all too familiar with the problems caused (by a dba's work, no less!), fear not.
I have been on the other side of the fence I guess and seen awful nested views, monster great uncommented indigestible procs and so forth. Believe me the L2S / EF solutions I have designed are like lying in the sun on the beach in comparison. Perhaps we just need really good dbas. Imagine if we had good devs AND good dbas - nah, it'll never happen.
I am possibly a rarity in that while I am a web dev I have spent many years becoming at least adequate at data modelling, structuring and retrieval. I take a properly designed database as the starting point for any work I promise. I hope Dunning-Kruger does not apply to me but then I couldn't tell if it did - that is the beauty of slinging that one at me - chapeau!
In terms of reviewing the execution plans I tend to copy from the profiler where this is necessary.
Oh trust me, I've seen plenty of junk generated by DBAs. I once told one to step away from the server with his hands in plain view. I told another to take his brain out of the shrinkwrap and make sure it would work, before the warranty expired.
My predecessor on my current job was bad enough that one of his processes lost 16.7% of all incoming data from new customers coming to our websites. Another one used row numbers to tie two tables together, but had a flawed insert process for the second table, so there's seven years of customer-order data, where we can't actually count on the right customers being tied to the right orders. That's just the start of it. It actually gets worse from there.
But the problem there is really the same one. That guy should never have been put in charge of databases in the first place. He knew just enough to think he knew it well, when he actually was tremendously harmful to the data, and to the company.
And no, I'm not trying to vilify you or insult you. If I were, I'd be more direct about it. (See the above shrinkwrap comment. That's what it would look like if I were.) My point about Dunning-Kruger is that it applies to far too many people in the database world, most of them perfectly competent software developers, with no real education on the details of database work. I definitely don't know you well enough to judge whether it applies to you or not, and was not trying to imply that it does. If I did, I appologize, because that's rude of me.
Relational database engines, MS SQL Server or otherwise, are absurdly complicated beasts. Most of them have far too many "there may be one customer out there somewhere who finds this useful" features, included in them, mostly because marketing people can then hype the product more. Add to that the dearth of good educational standards and methods for learning these monstrosities well, and you have what we currenty see. Which can be summed up nicely to "devs end up hating databases, and they generally have good reason to do so". I tell the devs I work with that it's not fair for them to blame performance problems on the database without testing it, and that just because the database is the problem 99.9% of the time is no reason to assume it is this time. I'm joking, and they know it, but it's got that kernel of truth in that's just unescapable in this line of work.
I'll be the last to argue that you should avoid LINQ for direct-to-the-table CRUD, if you don't have access to a good database expert. But it's still a case of solving the wrong problem. In my estimation, it's like using band-aids and a stick for a broken arm, because you don't have a real doctor in town, or because the local shaman isn't so good at appeasing the broken arm spirits and keeps twisting the bones out of place when he tries to do it. Sure, a brace made of band-aids and a stick is better than nothing, and better than some moron who'll lose 1/6th of your incoming data, but you might be better off, long-term, sending the neighbor's kid off to medical school in the nearest city. You need to fix the immediate broken arm (use LINQ), but you also need the long-term solution.
My disagreement is primarily with the original article. It makes the broadly sweeping assertion that you should definitely avoid stored procedures unless you absolutely have to use them for something and have no other choice. That's wrong. In my experience, it creates more long-term problems than it solves. It solves some short-term issues with speed of development, at the cost of having to do a lot more work later, when the software is already in production and already suffering from scalability and refactoring issues. I encounter that attitude, and related ones, from devs on a routine basis. Some of them, it's very nearly a religious thing to hate relational databases (which is kind of like hating transmissions, really). And I've seen time-to-market used to justify a menagerie of crimes against scalable, reliable coding. All very penny-wise, dollar-foolish.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2012 at 9:55 am
GSquared (8/15/2012)
That guy should never have been put in charge of databases in the first place.
I see this far too often in the industry its not even funny. I do think it stems from the manager's basic philosphy of wanting to fill a DBA spot without having to pay for an experienced DBA. In the long run, you really do get what you end up paying for, or in other cases, what you don't end up paying for. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
August 15, 2012 at 10:15 am
All sounds fine to me GSquared. I would expect such a perspective from a DBA type. I would whole-heartedly agree with having one person in charge of the data model, all changes routed through them and so forth. I just don't think I could tear the power of ORMs from our dev's hands if I tried now. It's all about correct usage - for instance having dictionaries or lists (or other generic objects) of heavily used secondary entities cached to avoid the excessive db chattiness you can get from ORMs. I have been through several cycles of using all procs, dynamic SQL and other means in my career, including various home brews and commercial implementations, and my recent experience is that we get good results.
We do have dedicated dba skills in house but they do not tend to get involved at this sort of level, having more interesting things to work on, like ETL and other data oriented tasks. It would not work for us to be waiting on their input.
August 15, 2012 at 11:29 am
call.copse (8/15/2012)
All sounds fine to me GSquared. I would expect such a perspective from a DBA type. I would whole-heartedly agree with having one person in charge of the data model, all changes routed through them and so forth. I just don't think I could tear the power of ORMs from our dev's hands if I tried now. It's all about correct usage - for instance having dictionaries or lists (or other generic objects) of heavily used secondary entities cached to avoid the excessive db chattiness you can get from ORMs. I have been through several cycles of using all procs, dynamic SQL and other means in my career, including various home brews and commercial implementations, and my recent experience is that we get good results.We do have dedicated dba skills in house but they do not tend to get involved at this sort of level, having more interesting things to work on, like ETL and other data oriented tasks. It would not work for us to be waiting on their input.
If it's working well for you, it's working well for you. No arguing with that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2012 at 2:51 pm
venoym (8/14/2012)
For Mr. Moden, the main thing about LINQ to SQL is that if you are doing a straight comparison (per the Stack Overflow article) is that if you don't specify the "VARCHAR" type in your Mapping (M of ORM) then it will convert both sides to NVARCHAR. If you do specify, it will convert the resulting string to VARCHAR in most cases. The edge cases you have to worry about are when you do concatenation in your query (i.e. Column1 + 'A') it will convert everything over to NVARCHAR... I don't know much about the controversy in LINQ to SQL that you referenced, I do know that specifying the DB type in the mapping resolved about 90% of the issues I had run into previously.
That's exactly what I figured out. The key is that this company has written a whole lot of code in the last couple of years that doesn't have that type of mapping in the code and I see no magic way to overcome the automatic conversion without repairing and recompiling a shed load of code.
--Jeff Moden
August 15, 2012 at 4:18 pm
Frankly, a good developer writes code that writes code. For all those things most think are too simple to waste time doing, I've written a tool to accomplish the task in no time.
This includes a DB Object creation app that allows me to pull from a template of tables with variable id/key fields. Creates all indexes, foreign key relationships and even allows me to quickly add a few fields.
The Same app when it generates the table can choose from templates to build ID to Key Key to ID (I use uniqueidentifiers for user viewable keys), a simple update proc, a paged or unpaged list proc, an audit table of the same table, and an audit stored proc. Copy -> Paste into SSMS and execute.
The same goes for code generation on the front end, my favorite and one that's progressed through the years is an app that takes a stored proc out of a database and generates front end execution code for me... and a more advanced version that just refreshes an entire db class.
My final tool, one still buggy, generates classes in C# based off of recordsets to strongly type a resultset.
Hopefully, I'm not that odd of a cat. I hope most developers do this... god only knows the time invested has been returned to me time and time again.
August 15, 2012 at 4:37 pm
The trick Brian, is making sure databases, tables, fields, indexes,relationships and queries scale. ORM technology is probably here to stay, but just because something works on a developer's box with 2000 records doesn't mean it will perform at 100 million records.
Since our developers use an ORM and the first five or six years on this application were handled without any DBA involvement, we're throwing large amounts of money at the problem ( faster Netapp SAN, tons of memory, expensive sql boxes etc etc )
And maybe in some situations that money spent on hardware is less than what it would cost to hire staff to design a scalable application in the first place.
Once skilled DBAs are on staff though, it can be tough to re-engineer the system since the logic is all in C# which most DBAs either don't understand or don't have access to. So they're left waiting with hat-in-hand for the development team to make recommended changes.
August 16, 2012 at 4:33 am
Really an interesting topic to go through. These days i am seeing a lot of Front End developers who don't even know the basics of DB and will go for workarounds from the FE Code and make lot of performance breaches. Yes really ORMs' are helpful in certain ways when you take up specific design patterns / methodologies. But every developer needs to have a clear insight into what is happening in the database.
August 16, 2012 at 7:41 am
Indianrock (8/15/2012)
The trick Brian, is making sure databases, tables, fields, indexes,relationships and queries scale. ORM technology is probably here to stay, but just because something works on a developer's box with 2000 records doesn't mean it will perform at 100 million records.Since our developers use an ORM and the first five or six years on this application were handled without any DBA involvement, we're throwing large amounts of money at the problem ( faster Netapp SAN, tons of memory, expensive sql boxes etc etc )
And maybe in some situations that money spent on hardware is less than what it would cost to hire staff to design a scalable application in the first place.
Once skilled DBAs are on staff though, it can be tough to re-engineer the system since the logic is all in C# which most DBAs either don't understand or don't have access to. So they're left waiting with hat-in-hand for the development team to make recommended changes.
Oh! I didn't know we were working for the same company.
Where is your office located? 😀
-- Gianluca Sartori
August 16, 2012 at 11:03 am
Indianrock (8/15/2012)
The trick Brian, is making sure databases, tables, fields, indexes,relationships and queries scale. ORM technology is probably here to stay, but just because something works on a developer's box with 2000 records doesn't mean it will perform at 100 million records.Since our developers use an ORM and the first five or six years on this application were handled without any DBA involvement, we're throwing large amounts of money at the problem ( faster Netapp SAN, tons of memory, expensive sql boxes etc etc )
And maybe in some situations that money spent on hardware is less than what it would cost to hire staff to design a scalable application in the first place.
Once skilled DBAs are on staff though, it can be tough to re-engineer the system since the logic is all in C# which most DBAs either don't understand or don't have access to. So they're left waiting with hat-in-hand for the development team to make recommended changes.
Appreciate your sentiment. That's exactly why I do what I do, avoid ORM. My Code writes SQL to create the objects and stored procs inside the database setup in a way that allows performance tuning (tuning built into the templates). I hate ORM. I have never found it useful in that it replaces something so easily done with a little bit of work. Your arguments are valid and exactly why I've avoid ORM.
I would say, frankly, that I've rarely come into content with people who consider themselves DBA's who are great programmers. I've worked with half a dozen in my career all with tons of experience. I'd love to work with a competent one, but I've resolved to learning tuning myself (I started in C++) and writing my own code to write "their code." Frankly, it has been my experience over the past 15 years that the one hat-in-hand is the developer waiting on DBA's, I've eliminated pure DBA's from my projects. I work with developers who do both, because frankly, database design isn't rocket surgery. Tuning, too, isn't rocket surgery. However, I may have been a) unlucky to work with terrible DBA's through the years or b) lucky that all the developers I work with understand databases like breathing.
Anyway, my post was about writing complex code to output simple code. This applies to TSQL as well as C#, CSS, HTML, Javascript etc. While I agree with you about ORM, I disagree you need a team of DBA's to do the same work.
August 16, 2012 at 11:42 am
GSquared (8/14/2012)
You're not disagreeing with me. You're disagreeing with the original article we're discussing. All I did was quote/reference that. Please note the sentence I emphasized in the above citation.
I guess I'm not clear on what the actual difference is between "Select *" and "Select All". SQL does not have a "Select All" argument, unless "All" is one of your column names (which would be odd, but possible).
What I mean is exactly what the original article said: LINQ, by default, pulls all available columns from the data source(s) being queried, implicitly. That is exactly what Select * does.
Here's a sample proof:
USE ProofOfConcept;GO
IF OBJECT_ID(N'dbo.DropMe','U') IS NOT NULL
DROP TABLE dbo.DropMe;
GO
SELECT * -- Dev would expect view to return new column, but it doesn't, only the original column, based on metadata at time view was created
FROM dbo.DropMeView;
Based on what you are saying about "Select *" and "Select All" somehow being different, I suspect you didn't know how the above would work till just now. Try it.
Will LINQ automatically, somehow, correct for the fact that the table now has an extra column in it? Per your posts, I expect that it won't, but I have to admit that I would be guessing.
Or, will every (or even any) LINQ connection to that table now fail and require at least recompilation and redeployment? Even if LINQ will "auto-intellisense" the corrections, will they need to be done?
Properly built procs would be unaffected by that table change. Zero impact on existing systems. (The DBA might need to deal with some unexpected index fragmentation, some surprises in rebuilt execution plans, and so on, in the database admin/internals world. But that's something a good DBA can plan for and handle proactively.)
Is that the kind of thing you mean by "Select All" querying table metadata, but "Select *" not doing so? If so, then you're mistaken about how "Select *" works.
Your sample and comments prove my point. ORM's don't issue SELECT *, regardless of what the article said. You map the columns in the table or view over into the ORM at design time. If the table adds a column, the program knows nothing about it and will not return it, PERIOD. ORM's will select all mapped columns by name ("SELECT [Column1], [Column2]..", you do know what that is right?)... which is likely to be all columns at design time. If the author of the article says otherwise, he's flat wrong or lying to you.
Impact of a table change (i.e. add column) is none to a properly written stored procedure, and NONE to ORM (be honest, we're referring to LINQ-to-SQL). A poorly written SP or view will have an impact... an ORM won't.
Please, don't use the intellectually insulting style comments about lack of knowledge between my "SELECT ALL" concept and the "SELECT *" until you understand what the "SELECT ALL" is.
August 16, 2012 at 11:51 am
venoym (8/16/2012)
GSquared (8/14/2012)
You're not disagreeing with me. You're disagreeing with the original article we're discussing. All I did was quote/reference that. Please note the sentence I emphasized in the above citation.
I guess I'm not clear on what the actual difference is between "Select *" and "Select All". SQL does not have a "Select All" argument, unless "All" is one of your column names (which would be odd, but possible).
What I mean is exactly what the original article said: LINQ, by default, pulls all available columns from the data source(s) being queried, implicitly. That is exactly what Select * does.
Here's a sample proof:
USE ProofOfConcept;GO
IF OBJECT_ID(N'dbo.DropMe','U') IS NOT NULL
DROP TABLE dbo.DropMe;
GO
SELECT * -- Dev would expect view to return new column, but it doesn't, only the original column, based on metadata at time view was created
FROM dbo.DropMeView;
Based on what you are saying about "Select *" and "Select All" somehow being different, I suspect you didn't know how the above would work till just now. Try it.
Will LINQ automatically, somehow, correct for the fact that the table now has an extra column in it? Per your posts, I expect that it won't, but I have to admit that I would be guessing.
Or, will every (or even any) LINQ connection to that table now fail and require at least recompilation and redeployment? Even if LINQ will "auto-intellisense" the corrections, will they need to be done?
Properly built procs would be unaffected by that table change. Zero impact on existing systems. (The DBA might need to deal with some unexpected index fragmentation, some surprises in rebuilt execution plans, and so on, in the database admin/internals world. But that's something a good DBA can plan for and handle proactively.)
Is that the kind of thing you mean by "Select All" querying table metadata, but "Select *" not doing so? If so, then you're mistaken about how "Select *" works.
Your sample and comments prove my point. ORM's don't issue SELECT *, regardless of what the article said. You map the columns in the table or view over into the ORM at design time. If the table adds a column, the program knows nothing about it and will not return it, PERIOD. ORM's will select all mapped columns by name ("SELECT [Column1], [Column2]..", you do know what that is right?)... which is likely to be all columns at design time. If the author of the article says otherwise, he's flat wrong or lying to you.
Impact of a table change (i.e. add column) is none to a properly written stored procedure, and NONE to ORM (be honest, we're referring to LINQ-to-SQL). A poorly written SP or view will have an impact... an ORM won't.
Please, don't use the intellectually insulting style comments about lack of knowledge between my "SELECT ALL" concept and the "SELECT *" until you understand what the "SELECT ALL" is.
I am finding your comments just as insulting.
August 17, 2012 at 8:37 am
venoym (8/16/2012)
GSquared (8/14/2012)
You're not disagreeing with me. You're disagreeing with the original article we're discussing. All I did was quote/reference that. Please note the sentence I emphasized in the above citation.
I guess I'm not clear on what the actual difference is between "Select *" and "Select All". SQL does not have a "Select All" argument, unless "All" is one of your column names (which would be odd, but possible).
What I mean is exactly what the original article said: LINQ, by default, pulls all available columns from the data source(s) being queried, implicitly. That is exactly what Select * does.
Here's a sample proof:
USE ProofOfConcept;GO
IF OBJECT_ID(N'dbo.DropMe','U') IS NOT NULL
DROP TABLE dbo.DropMe;
GO
SELECT * -- Dev would expect view to return new column, but it doesn't, only the original column, based on metadata at time view was created
FROM dbo.DropMeView;
Based on what you are saying about "Select *" and "Select All" somehow being different, I suspect you didn't know how the above would work till just now. Try it.
Will LINQ automatically, somehow, correct for the fact that the table now has an extra column in it? Per your posts, I expect that it won't, but I have to admit that I would be guessing.
Or, will every (or even any) LINQ connection to that table now fail and require at least recompilation and redeployment? Even if LINQ will "auto-intellisense" the corrections, will they need to be done?
Properly built procs would be unaffected by that table change. Zero impact on existing systems. (The DBA might need to deal with some unexpected index fragmentation, some surprises in rebuilt execution plans, and so on, in the database admin/internals world. But that's something a good DBA can plan for and handle proactively.)
Is that the kind of thing you mean by "Select All" querying table metadata, but "Select *" not doing so? If so, then you're mistaken about how "Select *" works.
Your sample and comments prove my point. ORM's don't issue SELECT *, regardless of what the article said. You map the columns in the table or view over into the ORM at design time. If the table adds a column, the program knows nothing about it and will not return it, PERIOD. ORM's will select all mapped columns by name ("SELECT [Column1], [Column2]..", you do know what that is right?)... which is likely to be all columns at design time. If the author of the article says otherwise, he's flat wrong or lying to you.
Impact of a table change (i.e. add column) is none to a properly written stored procedure, and NONE to ORM (be honest, we're referring to LINQ-to-SQL). A poorly written SP or view will have an impact... an ORM won't.
Please, don't use the intellectually insulting style comments about lack of knowledge between my "SELECT ALL" concept and the "SELECT *" until you understand what the "SELECT ALL" is.
I'm not going to dignify you with further discussion. I asked about the difference between the two, if you actually read what I wrote, and you respond to it in this manner?
Edit: Part of what I wrote was inappropriate and I shouldn't have included it. To anyone who read it before this edit, I appologize for going off like that. Not professional, not appropriate, shouldn't have thought it much less wrote it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 17, 2012 at 8:48 am
Well I'm disappointed to see discussions here become cat fights. Anyway, I think there are reasons both developers and DBAs exist. At our company, security auditors are looking to see if anyone who has the term "developer" associated with their name has access to production data.
Certainly many people can do both roles admirably, but there is, in general, a different mind set when you work mostly on test systems. Even if we don't think so, auditors ( both internal and external ) seem to think so.
Also, quite often developers just don't need to think about mundane things like finite disk space and how a system will perform when it grows to millions of records.
I think ORM technology is here to stay -- but we do seem to need the involvement of those who think about scalability and efficiency as just as important as making life easy for programmers. And we need performance input up front, not after the application is mostly done.
I'm no performance guru, but understanding all the intracies of sql server performance boggles my mind. And as each new version of sql comes out it becomes more complex. There's a reason so many talented people are making money as sql server performance consultants.
August 17, 2012 at 12:40 pm
Indianrock (8/17/2012)
At our company, security auditors are looking to see if anyone who has the term "developer" associated with their name has access to production data.
That is called "Sarbanes-Oxley" (SarBox) and its standards basically involves separation of duties. More and more companies today are coming under its standards requirements, like it or not. The days of one "Jack-of-All-Trades" person wearing many hats at once are quickly approaching a sunset. In my shop, DBA's and developers are not put in the same group purposely. It is deemed a "conflict of interest", and I am not so sure I disagree with this concept either. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 15 posts - 46 through 60 (of 142 total)
You must be logged in to reply to this topic. Login to reply