August 14, 2012 at 4:34 am
call.copse (8/14/2012)
Point 3 Managing and using auto generated CRUD procs is definitely slower than using an ORM. I promise!
That's what I like ORMs for is basic CRUD. Unfortunately, I no longer trust them implicitly. As I said in a previous post above, the ORM we're using has the "hidden" problem of casting all character based criteria to NVARCHAR which causes table scans on VARCHAR indexes due to the high datatype precedence of NVARCHAR.
Yes, we've identified a work around for all new code but no magic bullet to fix the large volumes of older code that the company built before the problem was identified. And, yes... it really is a huge performance problem across the board. CRUD that should be measured in the low milliseconds is measured in seconds and the disk activity is off the charts compared to what it should be.
Just in case someone does know a magic bullet for this problem other than replacing the old code (I can wish, right?), the problem and a "rewrite part of the code" solution is expressed at the following URL. The ORM is "Linq to SQL" and, since I just recently found the problem, don't know which version the developers are using yet nor if the post RTM versions have actually been fixed like the post in the URL claims. I've got a bad feeling that it doesn't.
http://stackoverflow.com/questions/1699382/linq-to-sql-nvarchar-problem
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2012 at 5:27 am
Jeff Moden (8/14/2012)
call.copse (8/14/2012)
Point 3 Managing and using auto generated CRUD procs is definitely slower than using an ORM. I promise!That's what I like ORMs for is basic CRUD. Unfortunately, I no longer trust them implicitly. As I said in a previous post above, the ORM we're using has the "hidden" problem of casting all character based criteria to NVARCHAR which causes table scans on VARCHAR indexes due to the high datatype precedence of NVARCHAR.
Yes, we've identified a work around for all new code but no magic bullet to fix the large volumes of older code that the company built before the problem was identified. And, yes... it really is a huge performance problem across the board. CRUD that should be measured in the low milliseconds is measured in seconds and the disk activity is off the charts compared to what it should be.
Just in case someone does know a magic bullet for this problem other than replacing the old code (I can wish, right?), the problem and a "rewrite part of the code" solution is expressed at the following URL. The ORM is "Linq to SQL" and, since I just recently found the problem, don't know which version the developers are using yet nor if the post RTM versions have actually been fixed like the post in the URL claims. I've got a bad feeling that it doesn't.
http://stackoverflow.com/questions/1699382/linq-to-sql-nvarchar-problem
I have seen problems from that type of mismatch, although any recent design I have worked on has had to be internationalised so uses all n-types - I guess we have just been lucky on that one. I would have thought you might be able to achieve a fix subclassing the context code if you had used SQLMetal for generation, no?
I agree with not trusting an ORM or indeed anything implicitly. Prior to moving from an 'all procs' basis to our current model I set up a load of example queries tried and tested on sensible data sets. It was not a move liked by many factions. Now, I have a problem trying to get devs to work with procs at all - they dislike having to drop out to T-SQL though I encourage them to so do when needed. I'll be honest though, there are not many times at al where that happens now we have some decent experience (it's definitely not CRUD only!).
I accept with high usage developments (say over 500 page views per minute peak for a web site) suitable caution is called for. It's not hard to manage though.
August 14, 2012 at 5:47 am
call.copse (8/14/2012)
Jeff Moden (8/14/2012)
call.copse (8/14/2012)
Point 3 Managing and using auto generated CRUD procs is definitely slower than using an ORM. I promise!That's what I like ORMs for is basic CRUD. Unfortunately, I no longer trust them implicitly. As I said in a previous post above, the ORM we're using has the "hidden" problem of casting all character based criteria to NVARCHAR which causes table scans on VARCHAR indexes due to the high datatype precedence of NVARCHAR.
Yes, we've identified a work around for all new code but no magic bullet to fix the large volumes of older code that the company built before the problem was identified. And, yes... it really is a huge performance problem across the board. CRUD that should be measured in the low milliseconds is measured in seconds and the disk activity is off the charts compared to what it should be.
Just in case someone does know a magic bullet for this problem other than replacing the old code (I can wish, right?), the problem and a "rewrite part of the code" solution is expressed at the following URL. The ORM is "Linq to SQL" and, since I just recently found the problem, don't know which version the developers are using yet nor if the post RTM versions have actually been fixed like the post in the URL claims. I've got a bad feeling that it doesn't.
http://stackoverflow.com/questions/1699382/linq-to-sql-nvarchar-problem
I have seen problems from that type of mismatch, although any recent design I have worked on has had to be internationalised so uses all n-types - I guess we have just been lucky on that one. I would have thought you might be able to achieve a fix subclassing the context code if you had used SQLMetal for generation, no?
I agree with not trusting an ORM or indeed anything implicitly. Prior to moving from an 'all procs' basis to our current model I set up a load of example queries tried and tested on sensible data sets. It was not a move liked by many factions. Now, I have a problem trying to get devs to work with procs at all - they dislike having to drop out to T-SQL though I encourage them to so do when needed. I'll be honest though, there are not many times at al where that happens now we have some decent experience (it's definitely not CRUD only!).
I accept with high usage developments (say over 500 page views per minute peak for a web site) suitable caution is called for. It's not hard to manage though.
Apologies if this has already been posted - I've not had time to read all of the thread. Do any ORM's show you the execution plan of the generated queries? I don't know any SQL Server developers nowadays who don't inspect the plan sheet while writing all but the very simplest queries, and for anything complex it's essential. Without it, all you can do is hope that the execution time of the generated query is acceptable. I'd call that "job nearly done".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 14, 2012 at 8:08 am
call.copse (8/14/2012)
GSquared (8/13/2012)
And even then, on simple queries, it has to assume no underlying database object will be changed without also recompiling, redeploying, etc., the code that accesses it.
If a table has X columns, and the default for LINQ is to call all columns (equivalent to Select *), which it is, adding a column will require at least verifying every piece of code that accesses that table.
If all access to the table is done via procs, and they use explicit column naming, then NO code will have to be rewritten to accommodate an added column. Zero. Just whatever will take advantage of the new column, which may very well be new code with new features. This means zero work in legacy apps.
If the legacy apps use LINQ, and use the default Select * equivalent, you have to assume they'll all break. And suddenly that new column could require months of work to add without breaking them.
So, even for simple CRUD, I prefer procs.
And since it's dead easy to generate default CRUD statements in proc format for any given table, with a simple dynamic SQL statement that addresses sys.columns by object_id, I'm not even sure it really saves and dev time.
I have a script that will create default CRUD for all tables in a database, or for a specific object, just depending on whether an input parameter is provided or not (input param is an object ID). If a second parameter is provided, it will just to whichever type of CRUD is needed for the object specified or all objects. Takes almost zero time to create them all. And they'll all follow pre-established naming conventions, won't accidentally miss any columns, won't try to insert/update ID columns, and so on.
Of course, they can be customized afterwards, if desired. They don't yet follow prefered formatting conventions (they look horrible in an editor, till I use SQL Prompt on them, which again takes very little time, and has no functional need). But they can include auto-documentation, if desired.
Will do most databases in about 1 second. Is that so much slower than using LINQ for that kind of thing? I haven't found it to be so.
I say old chap, you are obviously a top dba but I would comment that your post is a just a tad 'What is this beatles band?' 🙂
On your first point how many times do you get procs breaking because of a renamed column or other refactored ? Of course you have the discipline to get that right and use suitable tooling to alter the procedures. LINQ or EF give you the broken stuff just attempting a recompile. This is very good for less experienced types.
Point 2 I don't know how you achieve a 'SELECT *' - it heas never been emitted from any ORM I have used (straw man).
Point 3 Managing and using auto generated CRUD procs is definitely slower than using an ORM. I promise!
I happily accept your obviously superior database expertise (I'm primarily a dev). I also think you need to fight your battles on the right front and this is one where you are content with your way of working. I do think though if I could show you how I have set things up you would be able to see many obvious advantages - I (and the team in our shop) like it plenty and think we avoid heinous database transgressions pretty well. I guess I should STFU and document my methodology in an article 😉
On portability (later post) I might add Fluent NHibernate is supposedly another brilliant way of working. I thought we were not worried about that except for COTS though?
Point by point:
Assuming your ad hominem was intended purely for humor, I'll chuckly and then ignore it. Thanks for the backhanded compliment, though.
Point 2, definitely not straw man. The editorial we are discussing links to an article, said article being the whole basis of the discussion. That article says, "By default most ORMs will return every column in the table(s) that you requested." That's "select *". So definitely not a straw man argument.
Point 3: Are you actually claiming you can write a full DAL in a new database environment in under 1 second? That seems to be the claim there, but I must be misreading it. Every dev team I've ever worked with has had a very easy time hooking in to the auto-CRUD that this script generates. Most modern IDEs, given a proc name and connection definition, will pretty much write the code for you.
This isn't about my comfort-zone. It's a ton more work to tune a database, or do any major refactor in it, if the CRUD is all inline, instead of procs. Takes projects that should last half an hour and makes them take days. I've spent half a day tuning a database, and then devs had to spend weeks fixing legacy code to accommodate that, and then deployment had to be off-hours because it caused downtime, and so on. If the APIs to the data had all been procs, it would have taken me a full day instead of half a day, and the devs would have spent zero time on it, and deployment would have included zero downtime. That was in a LINQ environment. Same shop switched over to LINQ calling procs in new work, and productivity went way up, and I took average query response time in their main production database down from 1.2 seconds to 6 milliseconds. Management loved it, customers loved it, devs loved it, I got paid for it.
It's not about what I'm comfortable with. It's about the advantages of specialized knowledge. I couldn't write a solid app to save my life, unless the timeline included teaching myself some level of coding competence. Get back to me in 6 months, I'll see what I can do for you sir. That kind of thing. But the reverse has been true with every dev I've ever worked with - they know just enough SQL and database design to cause all the newbie errors in the world, but not enough to know they're doing so. Dunning-Kruger applies.
I can't speak to your specific environment. All I can say is that, if it's true, then you probably have the only team in the world that it's true for. Call me cynical, but I suspect it's simply Dunning-Kruger in play there. That's not an insult of you or anyone else. It's just how the human mind works. Nothing personal, you and I are both human beings is all.
On table changes causing proc failures, that's a sign of not knowing how to refactor a database correctly. Only happens if you don't know the subject well enough. Newbie mistakes cause that kind of thing.
- 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 14, 2012 at 9:54 am
First, thanks for the nice responses to my reply.
Many browser based applications use a single proxy account to query the database, so this account must have enough rights to perform all the operations for users with the highest privilege, thereby severely limiting the ability to use database security facilities to protect the data. Some kind of user identification (account, name, security token) may be required as parameter to a stored procedure, but it is generally easy to call it with the parameter set to value that allows full access tot the underlying data. By the way, most ORM tools do not handle access violations gracefully, and programmers always build secure applications, so why would we need those additional security measures anyway?
😀
Back in the old days, an application was designed, a logical and physical database model was distilled from that design, and then the programmers had to use that database to implement the application. So programmers did put multiple comma separated values into a single column and used certain columns for different purposes depending on the value of other columns. They wondered why on earth some nasty DBA 'enhanced' their model with some referential integrity, because it was a pain in the *** and nobody ever really asked for it. But today it is far more common that a 'developer' builds an application defining its data model on the fly, assuming that anything he (or she) uses in memory can be easily persisted and retrieved later on. If that is not so easy, they blame the database and turn to a 'NoSQL' solution, not because the expected volume of their data will be so large but only because of the high number of expected changes in the data model during development. Don't blame those 'developers', it is all about time to market and cutting development costs.
Ever used XML within an SQL database? Though XML provides all the required flexibility, it never seems to combine very well with the relational data at hand. Furthermore most developers are complete strangers to XPath and will make all kind of assumptions about the stored XML that will break their code sooner or later. For the DBA, XML comes with some headaches too, because defining the proper XML indexes is a lot harder than defining indexes on ordinary table columns. And although XML provides us with a very flexible solution, most tools require some XSD schema that throws away all that flexibility just to provide some kind of validation.
OData allows any browser based appliaction to implement CRUD operations client side (in JavaScript). Some client side frameworks already come with these capabilities built in and ready to use based on client side model definitions. OData allows us to expose stored procedures through service operations, so the debate about stored procedures does not end with shifting CRUD to the client and using OData to retrieve and process the data. Microsoft already supports the creation of WCF Data Services on top of an EF data model, and access to this data through LINQ, but nobody knows wether this will stay for more than a few years. Navigation through links does not provide a real substitute for joins between tables, but you can always use row-by-row processing to circumvent this shortcoming. Referential integrity is optional and might change without notice, so it is useless to expect that links do actualy point to an entity, but still a lot of code is made with this assumption in mind.
I do not have the ultimate answer to the question how to bridge the current gap between application development and data persistence and retrieval. Relational database are degraded to just a bunch of tables, but objects are also degraded to just a bunch of properties. The tools at hand allow for rappid application development but deliver only throw away solutions that will not stand the challenge when faced with performance problems due to ever growing amounts of data, secured acces to sensitive data, version changes without downtime and other production level software requirements. While stored procedures might provide a far better solution, I don't see yet how they fit into environments where a lower time to market seems the most important target of any developer. Any ideas?
August 14, 2012 at 9:59 am
But the reverse has been true with every dev I've ever worked with - they know just enough SQL and database design to cause all the newbie errors in the world, but not enough to know they're doing so.
I have found this to be true also. Many times the developers know just enough about SQL to be really dangerous to the database or the database server itself. For this reason, I review all SQL code coming in from development. They don't always like it, but they don't have a choice either. I don't write it all either, for obvious reasons, I would never get anything else done.:-D
"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 14, 2012 at 10:08 am
I feel that there's a tradeoff in going either route. In my opinion, ORMs may be better suited for prototype apps and smaller and/or short lived database apps where performance may not be quite as much of an issue. I've never been sold on using ORMs in the development of larger or longer life apps. While ORMs will usually save a lot of time by generating the CRUD code, I've found that the code is generally not as maintainable as the Stored Procs code. I prefer Stored Procs because they are more centrally maintained and it gives me more direct control over code maintenance and performance tuning. To me, the while argument is kind of like points and plugs vs. electronic ignition on automibiles.
So I guess the "it depends" option may be a legitimate answer here also.
August 14, 2012 at 11:35 am
Mad Hacker (8/14/2012)
I feel that there's a tradeoff in going either route. In my opinion, ORMs may be better suited for prototype apps and smaller and/or short lived database apps where performance may not be quite as much of an issue. I've never been sold on using ORMs in the development of larger or longer life apps. While ORMs will usually save a lot of time by generating the CRUD code, I've found that the code is generally not as maintainable as the Stored Procs code. I prefer Stored Procs because they are more centrally maintained and it gives me more direct control over code maintenance and performance tuning. To me, the while argument is kind of like points and plugs vs. electronic ignition on automibiles.So I guess the "it depends" option may be a legitimate answer here also.
Very true. For applications with a realistic one-and-done lifecycle, ORM and speed-to-market is probably better. Scalability, performance, data integrity, business-rule-compliance, et al, are unlikely to matter as much either in certain highly competitive, short-attention-span markets.
For anything with more longevity expected, I'd hope for something that allows the database specialist to do his darn job, which means procs over ORM.
Alternatively, let the devs do whatever they want, but let them know they'll be flung off the roof by a catapult if they ever blame any problem in their software on the database. In Microsoft-stack shops, they can have a .NET to catch them, of course. (ouch, that pun even hurt me!)
- 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 14, 2012 at 12:01 pm
GSquared (8/14/2012)
call.copse (8/14/2012)
GSquared (8/13/2012)
And even then, on simple queries, it has to assume no underlying database object will be changed without also recompiling, redeploying, etc., the code that accesses it.
If a table has X columns, and the default for LINQ is to call all columns (equivalent to Select *), which it is, adding a column will require at least verifying every piece of code that accesses that table.
If all access to the table is done via procs, and they use explicit column naming, then NO code will have to be rewritten to accommodate an added column. Zero. Just whatever will take advantage of the new column, which may very well be new code with new features. This means zero work in legacy apps.
If the legacy apps use LINQ, and use the default Select * equivalent, you have to assume they'll all break. And suddenly that new column could require months of work to add without breaking them.
So, even for simple CRUD, I prefer procs.
And since it's dead easy to generate default CRUD statements in proc format for any given table, with a simple dynamic SQL statement that addresses sys.columns by object_id, I'm not even sure it really saves and dev time.
I have a script that will create default CRUD for all tables in a database, or for a specific object, just depending on whether an input parameter is provided or not (input param is an object ID). If a second parameter is provided, it will just to whichever type of CRUD is needed for the object specified or all objects. Takes almost zero time to create them all. And they'll all follow pre-established naming conventions, won't accidentally miss any columns, won't try to insert/update ID columns, and so on.
Of course, they can be customized afterwards, if desired. They don't yet follow prefered formatting conventions (they look horrible in an editor, till I use SQL Prompt on them, which again takes very little time, and has no functional need). But they can include auto-documentation, if desired.
Will do most databases in about 1 second. Is that so much slower than using LINQ for that kind of thing? I haven't found it to be so.
I say old chap, you are obviously a top dba but I would comment that your post is a just a tad 'What is this beatles band?' 🙂
On your first point how many times do you get procs breaking because of a renamed column or other refactored ? Of course you have the discipline to get that right and use suitable tooling to alter the procedures. LINQ or EF give you the broken stuff just attempting a recompile. This is very good for less experienced types.
Point 2 I don't know how you achieve a 'SELECT *' - it heas never been emitted from any ORM I have used (straw man).
Point 3 Managing and using auto generated CRUD procs is definitely slower than using an ORM. I promise!
I happily accept your obviously superior database expertise (I'm primarily a dev). I also think you need to fight your battles on the right front and this is one where you are content with your way of working. I do think though if I could show you how I have set things up you would be able to see many obvious advantages - I (and the team in our shop) like it plenty and think we avoid heinous database transgressions pretty well. I guess I should STFU and document my methodology in an article 😉
On portability (later post) I might add Fluent NHibernate is supposedly another brilliant way of working. I thought we were not worried about that except for COTS though?
Point by point:
Assuming your ad hominem was intended purely for humor, I'll chuckly and then ignore it. Thanks for the backhanded compliment, though.
Point 2, definitely not straw man. The editorial we are discussing links to an article, said article being the whole basis of the discussion. That article says, "By default most ORMs will return every column in the table(s) that you requested." That's "select *". So definitely not a straw man argument.
Point 3: Are you actually claiming you can write a full DAL in a new database environment in under 1 second? That seems to be the claim there, but I must be misreading it. Every dev team I've ever worked with has had a very easy time hooking in to the auto-CRUD that this script generates. Most modern IDEs, given a proc name and connection definition, will pretty much write the code for you.
This isn't about my comfort-zone. It's a ton more work to tune a database, or do any major refactor in it, if the CRUD is all inline, instead of procs. Takes projects that should last half an hour and makes them take days. I've spent half a day tuning a database, and then devs had to spend weeks fixing legacy code to accommodate that, and then deployment had to be off-hours because it caused downtime, and so on. If the APIs to the data had all been procs, it would have taken me a full day instead of half a day, and the devs would have spent zero time on it, and deployment would have included zero downtime. That was in a LINQ environment. Same shop switched over to LINQ calling procs in new work, and productivity went way up, and I took average query response time in their main production database down from 1.2 seconds to 6 milliseconds. Management loved it, customers loved it, devs loved it, I got paid for it.
It's not about what I'm comfortable with. It's about the advantages of specialized knowledge. I couldn't write a solid app to save my life, unless the timeline included teaching myself some level of coding competence. Get back to me in 6 months, I'll see what I can do for you sir. That kind of thing. But the reverse has been true with every dev I've ever worked with - they know just enough SQL and database design to cause all the newbie errors in the world, but not enough to know they're doing so. Dunning-Kruger applies.
I can't speak to your specific environment. All I can say is that, if it's true, then you probably have the only team in the world that it's true for. Call me cynical, but I suspect it's simply Dunning-Kruger in play there. That's not an insult of you or anyone else. It's just how the human mind works. Nothing personal, you and I are both human beings is all.
On table changes causing proc failures, that's a sign of not knowing how to refactor a database correctly. Only happens if you don't know the subject well enough. Newbie mistakes cause that kind of thing.
Mr. GSquared... I must disagree on your point 2. Most ORM's DON'T do SELECT *, they do a SELECT ALL. The difference is that the ORM has to know what columns it's mapping to, SELECT * doesn't have that. The mapping (the M of ORM) defines what columns will be queried. So if you add a column and don't update your application, the only thing that will happen is on CR(eate) it won't fill in that column and could result in DB level exceptions (depending on Nullability and Default Values). For a simple example I highly encourage you to delve into the world of LINQ to SQL for a quick/simple example of how it actually works.
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.
August 14, 2012 at 1:11 pm
venoym (8/14/2012)
GSquared (8/14/2012)
call.copse (8/14/2012)
GSquared (8/13/2012)
...
If a table has X columns, and the default for LINQ is to call all columns (equivalent to Select *), which it is, adding a column will require at least verifying every piece of code that accesses that table.
If all access to the table is done via procs, and they use explicit column naming, then NO code will have to be rewritten to accommodate an added column. Zero. Just whatever will take advantage of the new column, which may very well be new code with new features. This means zero work in legacy apps.
If the legacy apps use LINQ, and use the default Select * equivalent, you have to assume they'll all break. And suddenly that new column could require months of work to add without breaking them.
....
...Point 2 I don't know how you achieve a 'SELECT *' - it heas never been emitted from any ORM I have used (straw man)....
...
Point 2, definitely not straw man. The editorial we are discussing links to an article, said article being the whole basis of the discussion. That article says, "By default most ORMs will return every column in the table(s) that you requested." That's "select *". So definitely not a straw man argument. ...
Mr. GSquared... I must disagree on your point 2. Most ORM's DON'T do SELECT *, they do a SELECT ALL. The difference is that the ORM has to know what columns it's mapping to, SELECT * doesn't have that. The mapping (the M of ORM) defines what columns will be queried. So if you add a column and don't update your application, the only thing that will happen is on CR(eate) it won't fill in that column and could result in DB level exceptions (depending on Nullability and Default Values). For a simple example I highly encourage you to delve into the world of LINQ to SQL for a quick/simple example of how it actually works.
...
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
CREATE TABLE dbo.DropMe (
ID INT IDENTITY PRIMARY KEY);
GO
IF OBJECT_ID(N'dbo.DropMeView','V') IS NOT NULL
DROP VIEW dbo.DropMeView;
GO
CREATE VIEW dbo.DropMeView
AS
SELECT * -- Use of Select * from base table, should get all table columns, right?
FROM dbo.DropMe;
GO
INSERT INTO dbo.DropMe
DEFAULT VALUES;
GO
SELECT * -- Table has one column, ID, which the View returns, as per definition
FROM dbo.DropMeView;
GO
ALTER TABLE dbo.DropMe -- Add another column to base table. View definition unchanged.
ADD Col1 INT;
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.
- 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 14, 2012 at 1:52 pm
In my experience, I have yet to see ORMs deliver on their value proposition. I think there is a place for them, but it's a small, non-system-integrated place. I too have lived on both sides of the fence and I think Vliet's response was dead on. (BTW Vliet you wouldn't happen to be Prof. Van Vliet? )
August 14, 2012 at 2:41 pm
I think stored procedures have more going for them than against them:
Portability
The notion of procs not being portable I think goes back to most developers beliving its safest to stick with the ANSI 92 SQL standard
- which didn't include stored procedures. Now all major DBMSs support stored procedures(Sybase,Oracle, SQL Server, MY SQL)
and the syntax is more or less the same. I think ANSI 2003 included them.
So they are as portable as any other SQL construct. Note: even a simple select can differ between DBMSs.
I think the way to be as portable as you can is to use ORM - where you can point your system at a new DBMS and click generate scripts.
However most enterprise level apps, normally require quite a bit of hand crafted SQL as well as generated code. So ORM is no golden bullet
for portability.
Design
I like to use Stored Procs as an API to the database. It means that all my database technology code is encapsulated in procs and my (java) code
simply calls procedures - no nasty mixing of technologies. I can syntax check, debug, and develop efficient SQL code in my DBMS specific IDE, rather
than code badly formatted snippets of SQL in properties files or classes with SQL as constants, etc and cross my fingers when I run my Java.
- Or I could develop SQL in my DB IDE and copy/paste into my Java code - hmm that’s efficient and not prone to error isn’t it ?!
There is no really good IDE that will support good development of SQL and good development of Java (to my knowledge).
I cant understand why there is not more desire to use procs by software designers. If one Java App communicates with another
- you wouldn’t access the internals of one application in the other. You would use some sort of API - either code or web service.
So to embed another technologies internals in another seems to violate this.
Testing
I often hear you cant test stored procs. Well, in that case you cant test embedded SQL in Java. You can certainly test stored procs. There are
various frameworks that allow you to build stored proc tests into your continuous build cycles.
Maintainability
When things start going slow, I can hide performance tweaks such as horizontal or vertical table partitioning from Java by using stored procedures.
Java does not need to know whats going on and can remain unchanged.
If there is a requirement for a large change to the database, trawling procs + changing them - maybe hiding the implementation of the change from Java
via the proc is easier than having to go through lots of Java with embedded SQL.
It is easier for production support to identify the stored procedure that is called, open it on the server and look at the code or call it to see whats
happening (depending on what it does!). Digging through java code to find the SQL that may be running is more time consuming.
Performance
The original use of procs was for performance gains. Where a proc would have a pre-compiled query plan. With modern DBMS the queries themselves have their plans compiled and cached.
Therefore procs nowerdays are less of a performance gain.
I would only say here, that its quite nice to run a proc on the server and look at the query plan from a proc, rather than dig out the SQL from an application.
Some downsides...
You have another artefact to release. And I have never found a good release procedure/tool for SQL objects and Java together.
Procs allow you to build lots of complex logic in the database very easily. Logic that would better belong in Java code.
However, if you are a good developer, I am sure you will have self control.
Part of the adage that it is bad to put business logic in the database comes from a time where databases were not scalable but application servers were.
That is no longer the case, and again you should put logic where it is best for the design of your application,
sometimes that is the database and sometimes that is the application server. It gets messy when you have the same logic coded twice once in the database and once in the application.
August 15, 2012 at 7:47 am
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.
August 15, 2012 at 8:09 am
vliet (8/13/2012)
But DBA's are expensive and generally considered a hurdle in the development process,
That is the image that alot of managers have of DBA's today, but when I actually pin them down on that I have found some enlightening, if not downright scary answers to that image. Some examples are:
Manager A: "I don't want to hire a DBA, they are too expensive."
Me: "You think there might be a reason for that?"
Manager A: "Besides them just wanting to make a lot of money?"
Me: "No, I mean have you stopped for a minute and put a dollar amount on what your company data is worth to your yearly revenue?"
Manager A: "No, but I guess alot.."
Me: "Alot?, Alot in thousands, or in millions a year?"
Manager A: "In the millions, I'm sure."
Me: "So, let me understand you sir, you are saying that paying an experienced DBA a possible $100K a year is too expensive to maintain and protect the integrity of millions of dollars of revenue per year of production data ?"
Manager A: "Well, when you put it like that, I guess it isn't".
Me: "Just remember, because people tend to take it for granted, if you have not got your data, you don't have doodly squat..."
Manager B: "We give all of our developers production access here at this shop."
Interviewee Me: "Why is that if you don't mind me asking?"
Manager B: "Because we don't want the DBA being a bottleneck to our production deadlines."
Me: "How many developers do you have here?"
Manager B: "About 30..."
Me: "and they all have production access to your production data?"
Manager B: "Yes."
Me: "and there is no central process or check valve for them to go through?"
Manager B: "No."
Me: "So, let me get this right, your production pie, let's say, has a possible thirty thumbs going into and out of it at any one time day after day and there is no master chef or process, let's say, controlling that?"
Manager B: "That's right"
Me: "That sir, is a recipe for disaster. It's not if, its when that is going to happen to your production data. Thank you for your time. Good day sir."
There a good solid reasons why we DBA's get paid what we do, and also why there always has to be a gatekeeper to your production data. 😀
"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 9:21 am
Examples of ORM generated sql. Trust me, once this approach is entrenched its very difficult to change.
1. ORM generated query as application executes it with sp_executesql. This one involves a double-wildcard LIKE parameter
exec sp_executesql
N'SELECT FollowupEntity02.LEGAL_ENTITY_ID AS FollowupEntity02_LEGAL_ENTITY_ID6,
FollowupEntity02.LAST_MODIFIER AS FollowupEntity02_LAST_MODIFIER8,
FollowupEntity02.LAST_MOD_DATE_TIME AS FollowupEntity02_LAST_MOD_DATE_TIME10,
FollowupEntity02.TRANS_SEQ_NUM AS FollowupEntity02_TRANS_SEQ_NUM12,
FollowupEntity02.CONCRETE_TYPE AS FollowupEntity02_CONCRETE_TYPE0,
FollowupEntity02.ADDRESS_ID AS FollowupEntity02_ADDRESS_ID16,
FollowupEntity02.FULL_NAME AS FollowupEntity02_FULL_NAME3,
FollowupEntity02.CREATED_DATE_TIME AS FollowupEntity02_CREATED_DATE_TIME20,
FollowupEntity02.BUSINESS_TELEPHONE AS FollowupEntity02_BUSINESS_TELEPHONE22,
FollowupEntity02.BUSINESS_FAX AS FollowupEntity02_BUSINESS_FAX24,
FollowupEntity02.CLIENTORGANIZATION_ID AS FollowupEntity02_CLIENTORGANIZATION_ID1,
FollowupEntity02.NOTE AS FollowupEntity02_NOTE28,
FollowupEntity02.ENTITY_CODE AS FollowupEntity02_ENTITY_CODE30,
FollowupEntity02.ENTITY_TYPE AS FollowupEntity02_ENTITY_TYPE32,
FollowupEntity02.CONTACT_NAME AS FollowupEntity02_CONTACT_NAME34,
FollowupEntity02.EMAIL_ADDRESS AS FollowupEntity02_EMAIL_ADDRESS36,
FollowupEntity02.DEALERSHIP_TYPE AS FollowupEntity02_DEALERSHIP_TYPE38,
FollowupEntity02.ENTITY_STATUS AS FollowupEntity02_ENTITY_STATUS40,
FollowupEntity02.CONTACT_TELEPHONE AS FollowupEntity02_CONTACT_TELEPHONE42,
FollowupEntity02.XML_SERIALIZED_INSTANCE_ID AS FollowupEntity02_XML_SERIALIZED_INSTANCE_ID44,
FollowupEntity02.NORMALIZED_ENTITY_CODE AS FollowupEntity02_NORMALIZED_ENTITY_CODE46,
FollowupEntity02.FOLLOW_UP_LAST_LETTER_DATE AS FollowupEntity02_FOLLOW_UP_LAST_LETTER_DATE48,
FollowupEntity02.FOLLOW_UP_LAST_CALL_DATE AS FollowupEntity02_FOLLOW_UP_LAST_CALL_DATE50
FROM LEGAL_ENTITY AS FollowupEntity02
WHERE (FollowupEntity02.CLIENTORGANIZATION_ID = @DerivedTable01_CLIENTORGANIZATION_ID20 AND
FollowupEntity02.CONCRETE_TYPE IN ( @DerivedTable01_52) AND
FollowupEntity02.FULL_NAME LIKE @DerivedTable01_FULL_NAME41)',N'@DerivedTable01_CLIENTORGANIZATION_ID20 int,@DerivedTable01_FULL_NAME41 varchar(8000),@DerivedTable01_52 varchar(21)',
@DerivedTable01_CLIENTORGANIZATION_ID20 = 9538 ,@DerivedTable01_FULL_NAME41 = '%SMITH%' ,
@DerivedTable01_52 = 'Fdi.Po.FollowupEntity'
2.This one doesn't show the exec sp_executesql, it was captured with a query using these DMVs: FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as ph
SELECT ResolveAutomaticRequestError02.WORK_QUEUE_ITEM_ID AS Id,ResolveAutomaticRequestError02.SECURED_ORGANIZATIONORGANIZATION_ID AS ClientId,File18.CURRENT_DIRECTORY_PATH AS CurrentDirectoryPath,File18.FILE_NAME AS FileName,File18.FILE_FORMAT AS FileFormat,FileRecord17.RAW_CONTENT AS RawContent,AutomaticRequestError13.ERROR_MESSAGE AS ErrorMessage,ResolveAutomaticRequestError02.CREATED_DATE_TIME AS AddedDate,AccountOwnershipDocSummary110.STATUS AS Status,Account111.CATEGORY AS Category,Account111.SUB_CATEGORY AS SubCategory,AccountProperty112.VEHICLE_TYPE AS CollateralType,MessageCriteria15.REQUEST_DATE AS ActionDate,OperationCode113.CODE AS OperationCode,Jurisdiction115.SHORT_NAME AS TitlingState,AccountOwnershipDocSummary110.EXPECTED_TITLING_STATE_ABBR AS ExpectedTitlingState,ClientRequestMessage14.IS_EXPORTED AS PriorityFlag,MessageCriteria15.ACTUAL_PAYOFF_DATE AS PayoffDate FROM WORK_QUEUE_ITEM AS ResolveAutomaticRequestError02 INNER JOIN (ERROR AS AutomaticRequestError13 INNER JOIN (MESSAGE AS ClientRequestMessage14 INNER JOIN (MESSAGE_CRITERIA AS MessageCriteria15 LEFT OUTER JOIN OPERATION_CODE AS OperationCode113 ON MessageCriteria15.OPERATION_CODE_ID=OperationCode113.OPERATION_CODE_ID) ON ClientRequestMessage14.MESSAGE_ID=MessageCriteria15.MESSAGE_ID INNER JOIN (FILE_RECORD AS FileRecord17 INNER JOIN [FILE] AS File18 ON FileRecord17.FILE_ID=File18.FILE_ID) ON ClientRequestMessage14.MESSAGE_ID=FileRecord17.MESSAGE_ID LEFT OUTER JOIN (ACCOUNT AS Account19 LEFT OUTER JOIN (ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary110 LEFT OUTER JOIN (ACCOUNT AS Account111 LEFT OUTER JOIN PROPERTY AS AccountProperty112 ON Account111.ACCOUNT_ID=AccountProperty112.ACCOUNT_ID) ON AccountOwnershipDocSummary110.ACCOUNT_ID=Account111.ACCOUNT_ID LEFT OUTER JOIN (OWNERSHIP_DOC AS OwnershipDoc114 LEFT OUTER JOIN ORGANIZATION AS Jurisdiction115 ON OwnershipDoc114.ISSUING_JURISDICTION_ID=Jurisdiction115.ORGANIZATION_ID) ON AccountOwnershipDocSummary110.OWNERSHIP_DOC_ID=OwnershipDoc114.OWNERSHIP_DOC_ID) ON Account19.ACCOUNT_ID=AccountOwnershipDocSummary110.ACCOUNT_ID) ON ClientRequestMessage14.ACCOUNT_ID=Account19.ACCOUNT_ID) ON AutomaticRequestError13.MESSAGE_ID=ClientRequestMessage14.MESSAGE_ID) ON ResolveAutomaticRequestError02.ERROR_ID=AutomaticRequestError13.ERROR_ID WHERE ((ResolveAutomaticRequestError02.CONCRETE_TYPE = @DerivedTable01_CONCRETE_TYPE20 AND ResolveAutomaticRequestError02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS41 AND ResolveAutomaticRequestError02.SECURED_ORGANIZATIONORGANIZATION_ID = @DerivedTable01_SECURED_ORGANIZATIONORGANIZATION_ID62 AND (MessageCriteria15.MANUFACTURER_ID LIKE @DerivedTable01_MANUFACTURER_ID93) AND ((ResolveAutomaticRequestError02.SUPPRESSION_DATE IS NULL ) OR (ResolveAutomaticRequestError02.SUPPRESSION_DATE < @DerivedTable01_SUPPRESSION_DATE124))) AND ((ClientRequestMessage14.CONCRETE_TYPE IN ( @DerivedTable01_135, @DerivedTable01_146))))
Viewing 15 posts - 31 through 45 (of 142 total)
You must be logged in to reply to this topic. Login to reply