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
Change is inevitable... Change for the better is not.
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"
August 17, 2012 at 1:28 pm
TravisDBA (8/17/2012)
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. 😀
At some point, even under SOX, you may still have a single person wearing multiple hats. If the team supporting the systems is too small for a complete separation of duties, it will still happen. The key is to have solid procedures and documentation in place to ensure that what SOX is meant to help prevent is properly dealt with in the organization.
Viewing 15 posts - 46 through 60 (of 143 total)
You must be logged in to reply to this topic. Login to reply