August 30, 2016 at 12:56 am
ORMs and LINQ are theft.
The developers steal DBAs' time so that they may have an easier ride. They meet their deadlines more easily while making the DBA's job harder.
As someone who takes an active interest in the performance of DB-objects, SPs are important.
SQL Server collects all manner of usage data and other useful data on the objects within the DB.
If a DB or function is performing poorly, I, as DBA, can rectify it. Not so with LINQ code.
I know how often an SP has been executed since the last server restart. I can find out which SPs have used a certain index in the last while. I can determine the average number of logical reads, physical reads and so on. I have something concrete with which to base my corrections.
For us DBAs maintenance and continual improvement are two of our tasks. The developers work within the parameters of their sprints and this work is usually new development, not fixing the crap that they have already coded.
If a manager comes and says that the application is running more slowly than usual, and all of the DB calls are made via LINQ, then I can only tell so much before having to send him to the developers to alleviate the problem. The DBAs become less useful (because they can't fix the problem as easily as they once could) and the developers become more stressed because they have to interrupt their sprint-work. It is a lose-lose situation.
And for all those developers who say that there should be no business logic in the DB, well, the DB itself is business logic. It is (or, at least, should have been) the structural personification of the business logic within the data resides.
August 30, 2016 at 1:52 am
Whilst I think that David makes an important point in asking what is the state of play with ORMs today, I feel that the argument of the applicability when choosing between ORMs and SPs is not necessarily a technical one. Although peoples' opinion on the current state of play would be very interesting.
A great developer could produce some well behaving, easily maintainable code with either technology. An inadequate developer will likely cause problems regardless of the technology employed. For me, using SPs makes it easier to protect the data from a poorly written application. This is not because DBAs > Devs (as I am sure loads of us can provide evidence of that being both true and false) but because there are enough developers who do not understand databases. (I didn't even limit it to relational theory.)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 30, 2016 at 2:48 am
Steve said:
ORM tools are just that tools. Used well, they can perform admirably, but just as I don't use a hammer to drive a screw into wood, don't depend on your ORM handling everything database related for you in an efficient manner.
A bit of cross-cultural cross-industry humour: in Britain some engineers will refer to a "Brummagem screwdriver" which is a hammer being used to drive a screw into wood!:-)
"Brummagem" (not sure what the correct spelling is, if there even is one) is a slang term for "Birmingham" and using a "Brummagem screwdriver" is usually associated with doing cheap, low quality work, in a hurry. My apologies to my Birmingham friends by the way - I used to work there! 🙂
Steve's point is well made though. Use the tool appropriate to the job and the circumstances. Even using a using a "Brummagem screwdriver" may be justified sometimes, but you are likely to pay later (like when you try to dismantle and re-assemble whatever it is!).
Tom Gillies LinkedIn Profilewww.DuhallowGreyGeek.com[/url]
August 30, 2016 at 2:57 am
Tom Gillies (8/30/2016)
...you are likely to pay later (like when you try to dismantle and re-assemble whatever it is!).
Maintenance is the most expensive cost and in recent times the Agile movement has been used as an excuse by some to ignore this.
...and yes, it too is my understanding that there is no correct spelling for "Brummag'em". Most Brummies I have met have a decent sense of humour about it too. Jolly decent of them too given its importance to the industrial revolution. Which is, arguably, the forebear of the digital revolution.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 30, 2016 at 2:59 am
Sean Redmond (8/30/2016)
ORMs and LINQ are theft.The developers steal DBAs' time so that they may have an easier ride. They meet their deadlines more easily while making the DBA's job harder.
As someone who takes an active interest in the performance of DB-objects, SPs are important.
SQL Server collects all manner of usage data and other useful data on the objects within the DB.
If a DB or function is performing poorly, I, as DBA, can rectify it. Not so with LINQ code.
I know how often an SP has been executed since the last server restart. I can find out which SPs have used a certain index in the last while. I can determine the average number of logical reads, physical reads and so on. I have something concrete with which to base my corrections.
For us DBAs maintenance and continual improvement are two of our tasks. The developers work within the parameters of their sprints and this work is usually new development, not fixing the crap that they have already coded.
If a manager comes and says that the application is running more slowly than usual, and all of the DB calls are made via LINQ, then I can only tell so much before having to send him to the developers to alleviate the problem. The DBAs become less useful (because they can't fix the problem as easily as they once could) and the developers become more stressed because they have to interrupt their sprint-work. It is a lose-lose situation.
And for all those developers who say that there should be no business logic in the DB, well, the DB itself is business logic. It is (or, at least, should have been) the structural personification of the business logic within the data resides.
Hey, the DBAs should appreciate us devs giving them something to do besides reading the papers 😉
I've always found it possible to profile ORM calls well enough for most purposes - though it sounds like you have a great command of the proc lifecycle tools. In that case it is a fairly quick piece of work for a dev to drop a call out to a procedure instead where needed (i.e. when there is a non-commensurate slowdown), perhaps initially simply using the generated SQL, which could then be reformatted and tuned to perfection.
Business logic - it's easiest to follow when you can flow through it in context. It's just harder when you have to jump context. But then we tend to do maintenance ourselves and certainly don't have DBAs to pick up the pieces.
August 30, 2016 at 3:12 am
I thought a Birmingham Screwdriver was a lump hammer:hehe:
On the Agile front an awful lot of things get labelled as Agile when they are not. If it sounds like a filthy hack then it's a filthy hack. If it's unmaintainable code then don't let anyone tell you that it's Agile.
Agile is incredibly disciplined when done correctly. The whole ethos is continually improving quality and ways of working. If you are not finding it so then someone is using Agile as a way to miss-sell slap dash, ill thought out bodges.
Read James Shore's Art of Agile. the Phoenix Project is also well worth a look. It is explicit about the need for rapid feedback to prevent problems being passed down the line i.e. preventing the pig being chucked over the wall.
August 30, 2016 at 4:09 am
As with most arguments like this there isn't a one size fits all.
I work on a system which uses Entity Framework and was jointly developed with another company. They insisted on using EF/LINQ for all data access. It works very nicely for simple queries. The SQL generated is gobbledegook, but it is quick gobbledegook so it doesn't matter so much. Having all data access in stored procs would be daft and prevent the use of an elegant coding construct.
However, it falls down badly with some more complex queries which could be much better handled with well-written stored procedures. The 3rd party keep mucking about with the LINQ trying to make the performance better. They claim success when they get it under the 90 second timeout (we had to increase the default 30 second value...). Despite demonstrating SQL to my management that returns the data in a few seconds, our old pal politics means we stick with EF.....
The bottom line is they both have their place. You need to be prepared to step away from either if the other is best for a situation., The problem is that old-school SQL developers don't like LINQ and new developers think stored procedures are verboten and a hideous violation of the sacred "separation of concerns" philosophy.
In IT as well as other activities, strict conformance to a doctrine often doesnt give the best solution.
August 30, 2016 at 4:41 am
I thought a Birmingham Screwdriver was a lump hammer :Hehe:
Nah! lump hammers are only used for precision adjustments.:hehe: For a while I worked in places where a sledgehammer, oxy-propane cutting torch (propane, not acetylene) and 36 inch stilsons (sometimes extended with a length of scaffold pole) were considered standard tools, and sometimes used in a hurry. Really. I'm not kidding! Not as metaphors, as real physical things. After that kind of experience you view the problems of IT slightly differently. 😉
On the Agile front an awful lot of things get labelled as Agile when they are not. If it sounds like a filthy hack then it's a filthy hack. If it's unmaintainable code then don't let anyone tell you that it's Agile.
I agree. The best agile practitioners I've had the pleasure to work with were obsessive about automated testing and refactoring what they produced.
But then, we can only use the tools we know how to use, and sometimes there is a lot of pressure to "get the job done". One man's "filthy hack" is another man's "expedient solution". It doesn't make it any easier when you are trying to sort it out afterwards though.
There are some lessons I've taken away from this discussion:
"tools" I'm going to ask people more about the tools that are being used. I don't/can't know everything. And what I thought I knew may not be quite right any more.
"scale" what is appropriate at one scale may not be appropriate at another, and
"longevity/maintenance/whatever you call it" something which is only going to be used once can be achieved any old way, something which is going to be used and modified again and again deserves more effort.
The trouble is, tools (and fashions) change, things can grow bigger than they were originally envisaged (if scale was thought about at all) and short-term solutions can creep into unexpected places.
Tom Gillies LinkedIn Profilewww.DuhallowGreyGeek.com[/url]
August 30, 2016 at 5:03 am
The SQL generated is gobbledegook, but it is quick gobbledegook so it doesn't matter so much
By gobbledegook I take it you mean something of lesser standard. No ways! If that is how people develop then no wonder the DBA's are so critical of developers!:hehe::hehe::hehe::hehe::hehe:
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
August 30, 2016 at 5:44 am
Have you ever tried to read the SQL generated by a 6-join LINQ statement? It took me 30 minutes just to tease it apart.
Here is (if I remember correctly) a parametric search that took 15 seconds to execute. It took me considerably longer to make sense of this SQL. This is what is meant by gobbledigook. (I've replaced all of the user-defined DB-object names with letters)
SELECT TOP (100) [Project6].[C2] AS [C1], [Project6].[a] AS [a], [Project6]. AS , [Project6].[c] AS [c], [Project6].[d] AS [d], [Project6].[e] AS [e], [Project6].[f] AS [f], [Project6].[g] AS [g], [Project6].[h] AS [h], [Project6]. AS , [Project6].[j] AS [j], [Project6].[k] AS [k], [Project6].[l] AS [l], [Project6].[m] AS [m], [Project6].[n] AS [n], [Project6].[o] AS [o], [Project6].[p] AS [p], [Project6].
AS
, [Project6].[r] AS [r], [Project6]. AS , [Project6].[C3] AS [C2], [Project6].[C4] AS [C3], [Project6].[C5] AS [C4], [Project6].[C6] AS [C5], [Project6].[C7] AS [C6], [Project6].[C8] AS [C7], [Project6].[C9] AS [C8], [Project6].[C10] AS [C9], [Project6].[C11] AS [C10], [Project6].[C12] AS [C11] from ( SELECT [Project6].[C1] AS [C1], [Project6].[a] AS [a], [Project6]. AS , [Project6].[c] AS [c], [Project6].[d] AS [d], [Project6].[e] AS [e], [Project6].[f] AS [f], [Project6].[g] AS [g], [Project6].[h] AS [h], [Project6]. AS , [Project6].[j] AS [j], [Project6].[k] AS [k], [Project6].[l] AS [l], [Project6].[m] AS [m], [Project6].[n] AS [n], [Project6].[o] AS [o], [Project6].[p] AS [p], [Project6].
AS
, [Project6].[r] AS [r], [Project6]. AS , [Project6].[C2] AS [C2], [Project6].[C3] AS [C3], [Project6].[C4] AS [C4], [Project6].[C5] AS [C5], [Project6].[C6] AS [C6], [Project6].[C7] AS [C7], [Project6].[C8] AS [C8], [Project6].[C9] AS [C9], [Project6].[C10] AS [C10], [Project6].[C11] AS [C11], [Project6].[C12] AS [C12], row_number() OVER (ORDER BY [Project6].[C1] ASC) AS [row_number] from ( select 1 AS [C1], [Project4].[a] AS [a], [Project4]. AS , [Project4].[c] AS [c], [Project4].[d] AS [d], [Project4].[e] AS [e], [Project4].[f] AS [f], [Project4].[g] AS [g], [Project4].[h] AS [h], [Project4]. AS , [Project4].[j] AS [j], [Project4].[k] AS [k], [Project4].[l] AS [l], [Project4].[m] AS [m], [Project4].[n] AS [n], [Project4].[o] AS [o], [Project4].[p] AS [p], [Project4].
AS
, [Project4].[r] AS [r], [Project4]. AS , [Project4].[C1] AS [C2], [Project4].[C2] AS [C3], [Project4].[C3] AS [C4], [Project4].[C4] AS [C5], [Project4].[C5] AS [C6], [Project4].[C6] AS [C7], [Project4].[C7] AS [C8], [Project4].[C8] AS [C9], [Project4].[C9] AS [C10], [Project4].[C10] AS [C11], [Project4].[C11] AS [C12] from ( select [Extent1].[a] AS [a], [Extent1]. AS , [Extent1].[c] AS [c], [Extent1].[d] AS [d], [Extent1].[e] AS [e], [Extent1].[f] AS [f], [Extent1].[g] AS [g], [Extent1].[h] AS [h], [Extent1]. AS , [Extent1].[j] AS [j], [Extent1].[k] AS [k], [Extent1].[l] AS [l], [Extent1].[m] AS [m], [Extent1].[n] AS [n], [Extent1].[o] AS [o], [Extent1].[p] AS [p], [Extent1].
AS
, [Extent1].[r] AS [r], [Extent1]. AS , CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN '0X' WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN '0X0X0X' ELSE '0X0X1X' END AS [C1], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS varchar(1)) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[iii] ELSE [Project3].[iii] END AS [C2], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS varchar(1)) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[hhh] ELSE [Project3].[hhh] END AS [C3], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[www] ELSE [Project3].[www] END AS [C4], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[ggg] ELSE [Project3].[ggg] END AS [C5], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS bit) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[fff] ELSE [Project3].[fff] END AS [C6], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS varchar(1)) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[eee] ELSE [Project3].[eee] END AS [C7], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS varchar(1)) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[C2] END AS [C8], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[C3] END AS [C9], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS datetime2) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN CAST(NULL AS datetime2) ELSE [Project3].[C4] END AS [C10], CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS tinyint) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN CAST(NULL AS tinyint) ELSE [Project3].[C5] END AS [C11], [Extent5].[t] AS [t], [Extent6].[t] AS [Street1], [Extent7]. AS , [Extent10].[v] AS [v], [Extent13].[w] AS [w], [Extent16].[w] AS [x] from [mmm].[kkk] AS [Extent1] left OUTER JOIN (select [UnionAll1].[a] AS [C1], [UnionAll1].[lll] AS [C2], [UnionAll1].[ppp] AS [C3], [UnionAll1].[C1] AS [C4], [UnionAll1].[C2] AS [C5], [UnionAll1].[C3] AS [C6], [Extent4].[iii] AS [iii], [Extent4].[hhh] AS [hhh], [Extent4].[www] AS [www], [Extent4].[ggg] AS [ggg], [Extent4].[fff] AS [fff], [Extent4].[eee] AS [eee], cast(1 as bit) AS [C7] FROM (select [Extent2].[a] AS [a], [Extent2].[lll] AS [lll], [Extent2].[ppp] AS [ppp], CAST(NULL AS datetime2) AS [C1], CAST(NULL AS tinyint) AS [C2], cast(1 as bit) AS [C3] from [zzz].[ddd] AS [Extent2] union all select [Extent3].[a] AS [a], CAST(NULL AS varchar(1)) AS [C1], CAST(NULL AS int) AS [C2], [Extent3].[x] AS [x], [Extent3].[Gender] AS [Gender], cast(0 as bit) AS [C3] from [zzz].[yyy] AS [Extent3]) AS [UnionAll1] inner JOIN [zzz].[Persons_PE] AS [Extent4] ON [UnionAll1].[a] = [Extent4].[a] ) AS [Project3] ON [Extent1].[a] = [Project3].[C1] left OUTER JOIN [zzz].[xxx] AS [Extent5] ON (CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[www] ELSE [Project3].[www] END) = [Extent5].[aa] left OUTER JOIN [zzz].[xxx] AS [Extent6] ON (CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[www] ELSE [Project3].[www] END) = [Extent6].[aa] left OUTER JOIN [zzz].[xxx] AS [Extent7] ON (CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[www] ELSE [Project3].[www] END) = [Extent7].[aa] left OUTER JOIN [zzz].[xxx] AS [Extent8] ON (CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[www] ELSE [Project3].[www] END) = [Extent8].[aa] left OUTER JOIN [vvv].[uuu] AS [Extent9] ON [Extent8].[ttt] = [Extent9].[y] left OUTER JOIN [vvv].[sss] AS [Extent10] ON [Extent9].[rrr] = [Extent10].[ID_PC] left OUTER JOIN [zzz].[xxx] AS [Extent11] ON (CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[www] ELSE [Project3].[www] END) = [Extent11].[aa] left OUTER JOIN [vvv].[uuu] AS [Extent12] ON [Extent11].[ttt] = [Extent12].[y] left OUTER JOIN [vvv].[qqq] AS [Extent13] ON [Extent12].[z] = [Extent13].[ab] left OUTER JOIN [zzz].[xxx] AS [Extent14] ON (CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN CAST(NULL AS int) WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN [Project3].[www] ELSE [Project3].[www] END) = [Extent14].[aa] left OUTER JOIN [vvv].[uuu] AS [Extent15] ON [Extent14].[ttt] = [Extent15].[y] left OUTER JOIN [vvv].[qqq] AS [Extent16] ON [Extent15].[z] = [Extent16].[ab] where CASE WHEN ( NOT (([Project3].[C7] = 1) AND ([Project3].[C7] IS NOT NULL))) THEN '0X' WHEN (([Project3].[C6] = 1) AND ([Project3].[C6] IS NOT NULL)) THEN '0X0X0X' ELSE '0X0X1X' END LIKE '0X0X%') AS [Project4] where ((@p__linq__0 IS NULL) OR (@p__linq__1 = @p__linq__2) OR ((@p__linq__1 IS NULL) AND (@p__linq__2 IS NULL)) OR ([Project4]. = @p__linq__3)) AND ((@p__linq__4 IS NULL) OR (@p__linq__5 = @p__linq__6) OR ((@p__linq__5 IS NULL) AND (@p__linq__6 IS NULL)) OR ((@p__linq__7 = 1) AND (([Project4].[C2] = @p__linq__8) OR (([Project4].[C2] IS NULL) AND (@p__linq__8 IS NULL)))) OR ((@p__linq__9 <> cast(1 as bit)) AND ([Project4].[C2] LIKE @p__linq__10 ESCAPE N'~'))) AND ((@p__linq__11 IS NULL) OR (@p__linq__12 = @p__linq__13) OR ((@p__linq__12 IS NULL) AND (@p__linq__13 IS NULL)) OR ((@p__linq__14 = 1) AND (([Project4].[C3] = @p__linq__15) OR (([Project4].[C3] IS NULL) AND (@p__linq__15 IS NULL)))) OR ((@p__linq__16 <> cast(1 as bit)) AND ([Project4].[C3] LIKE @p__linq__17 ESCAPE N'~'))) AND ((@p__linq__18 IS NULL) OR (@p__linq__19 = @p__linq__20) OR ((@p__linq__19 IS NULL) AND (@p__linq__20 IS NULL)) OR ((@p__linq__21 = 1) AND (([Project4].[t] = @p__linq__22) OR (([Project4].[t] IS NULL) AND (@p__linq__22 IS NULL)))) OR ((@p__linq__23 <> cast(1 as bit)) AND ([Project4].[Street1] LIKE @p__linq__24 ESCAPE '~'))) AND ((@p__linq__25 IS NULL) OR (@p__linq__26 = @p__linq__27) OR ((@p__linq__26 IS NULL) AND (@p__linq__27 IS NULL)) OR ([Project4]. = @p__linq__28) OR (([Project4]. IS NULL) AND (@p__linq__28 IS NULL))) AND ((@p__linq__29 IS NULL) OR (@p__linq__30 = @p__linq__31) OR ((@p__linq__30 IS NULL) AND (@p__linq__31 IS NULL)) OR ([Project4].[v] = @p__linq__32) OR (([Project4].[v] IS NULL) AND (@p__linq__32 IS NULL))) AND ((@p__linq__33 IS NULL) OR (@p__linq__34 = @p__linq__35) OR ((@p__linq__34 IS NULL) AND (@p__linq__35 IS NULL)) OR ([Project4].[w] = @p__linq__36) OR (([Project4].[w] IS NULL) AND (@p__linq__36 IS NULL)) OR ((@p__linq__37 <> cast(1 as bit)) AND ([Project4].[x] LIKE @p__linq__38 ESCAPE '~'))) AND ((@p__linq__39 IS NULL) OR (@p__linq__40 = @p__linq__41) OR ((@p__linq__40 IS NULL) AND (@p__linq__41 IS NULL)) OR ( EXISTS (SELECT
1 AS [C1] from [mmm].[ccc] AS [Extent17] inner JOIN [mmm].[bbb] AS [Extent18] ON [Extent17].[ac] = [Extent18].[af] where ([Project4].[a] = [Extent17].[ad]) AND (([Extent18].[ae] = @p__linq__42) OR (1 = 0))))) AND ((@p__linq__43 IS NULL) OR (@p__linq__44 = @p__linq__45) OR ((@p__linq__44 IS NULL) AND (@p__linq__45 IS NULL)) OR ([Project4].[m] LIKE @p__linq__46 ESCAPE '~') OR ([Project4]. LIKE @p__linq__47 ESCAPE '~') OR ([Project4].[c] LIKE @p__linq__48 ESCAPE '~')) AND (((1 = @p__linq__49) AND ([Project4].[d] = 1)) OR ((2 = @p__linq__50) AND ([Project4].[d] <> cast(1 as bit))) OR ((3 = @p__linq__51) AND ([Project4].[C5] IS NOT NULL) AND ([Project4].[d] = 1)) OR (4 = @p__linq__52) OR ((5 = @p__linq__53) AND ([Project4].[d] = 1)))
) AS [Project6] ) AS [Project6] where [Project6].[row_number] > 0 order BY [Project6].[C1] ASC
August 30, 2016 at 6:11 am
manie (8/30/2016)
The SQL generated is gobbledegook, but it is quick gobbledegook so it doesn't matter so much
By gobbledegook I take it you mean something of lesser standard. No ways!...
Exactly. It doesn't mean that it is poorer executing code but it breaks down when you consider what I said earlier:
Gary Varga (8/30/2016)
...Maintenance is the most expensive cost...
However, as for the following quote:
manie (8/30/2016)
...If that is how people develop then no wonder the DBA's are so critical of developers!:hehe::hehe::hehe::hehe::hehe:
Another case of lazy devism by a DBA. (I am against DBAism too, for the record.)
(Possible loophole seeing as manie said "people") :Whistling:
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 30, 2016 at 6:35 am
It's amazing to me that even after years of programming for our company, some programmers still don't understand the data structure (though it is quite large). We write stored procedures. It helps with the understanding of the database and thus with the ability to thing logically about the application.
August 30, 2016 at 6:37 am
There are some lessons I've taken away from this discussion:
"tools" I'm going to ask people more about the tools that are being used. I don't/can't know everything. And what I thought I knew may not be quite right any more.
"scale" what is appropriate at one scale may not be appropriate at another, and
"longevity/maintenance/whatever you call it" something which is only going to be used once can be achieved any old way, something which is going to be used and modified again and again deserves more effort.
The trouble is, tools (and fashions) change, things can grow bigger than they were originally envisaged (if scale was thought about at all) and short-term solutions can creep into unexpected places.
When it comes to tools I'm conflicted. On one hand it is easy for an IT person to cost £1/minute so scrimping on tooling is often a false economy. On the other hand having a vast technical sprawl limits the ability to redeploy staff without a ramp up time to learn the tooling used by a particular team, never mind learn the business area that a particular team is trying to cater for.
Longevity. I hear what you are saying and in an ideal world I'd agree but ye Gods what a slippery slope. The instance you let one "tactical solution" under the wire then another 10 will line up by the hole in the fence. It inevitably turns a pipeline into a decision point and one problem I have found with Agile teams is too many people have a voice. You really don't want to have to fight every sodding time to get something done to a decent level of quality. It is also very hard to predict how long something is going to last. The only consistency I have found in this regard is everything lives a lot longer than you anticipate.
August 30, 2016 at 6:47 am
One of the issues I've found with ORM frameworks is that it trains one into a particular way of thinking. For example, I worked on a project that was very database-centric yet most of the stored procedures were written by .NET developers using a CRUD mentality. This would have been fine had the application been a UI oriented toward single record inserts, updates, and deletes but it wasn't. Therefore, stored procedures were written in a very procedural fashion with conditional logic and loops along with CRUD calls to stored procedures that turned around and called yet another stored procedure which called yet another to perform a single insert, update, or delete with only parameters.
Hello people! T-SQL is NOT the same as .NET!! Added to that, for some reason they used GUIDs as their primary key on all the tables! Then they wonder why performance sucked outside of development! >Doh!!<
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 30, 2016 at 6:59 am
I developed a C# application that matched a list of Medicaid patients from one state agency with the immunization records at the state agency I work for. To get the patient's client id took one of two database searches: 1) search one table on name and date of birth; 2) if no match on 1, search another table for Medicaid ID and date of birth.
The straight-forward manner would be to send the first query to the database, if no match was returned, send the second query to the database. I saw that as "expensive"; why not have the database do one of those two searches? Switching the patient lookup to a stored procedure increased the performance of that phase of the application by 25%.
Viewing 15 posts - 76 through 90 (of 142 total)
You must be logged in to reply to this topic. Login to reply