November 7, 2018 at 3:53 am
TheSQLGuru - Friday, September 29, 2017 12:39 PMcall.copse - Tuesday, May 17, 2016 3:22 AMI'm going to say there is absolutely nothing wrong with the query in point 3. It's a little ungainly looking, sure, but that is here nor there regarding performance.That is absolutely incorrect. That parameterized queries will check the value sent in on the first execution, get a good plan for that, and STORE THAT PLAN. So say you have 10 years worth of data and you pass in a parameter that is YESTERDAY. BAM - index seek, lookup, 10000 rows out of 50M and you have a VERY fast query. The very next execution passes in 17530101, so you hit ALL 10 YEARS WORTH OF DATA - with an index seek, lookup plan. You can go buy and eat lunch while that thing is spinning a bajillion logical reads.
The opposite direction kills you too, where you scan the table for all data then use the same plan to bring back 1 day of data (or an hour or minute ...).
So, the lesson of the day is that many/most queries ever written that have such a date comparison predicate (or the ubiquitous @StartDate/@EndDate pairs used in reporting queries) should have OPTION (RECOMPILE) on them. Trade CPU ticks (3-5 BILLION COMPUTATIONS PER SECOND PER CORE) to avoid DISASTROUSLY bad plans.
Thus endeth the lesson. 😎
I accept your point, but that's a different thing, it's wrong in a particular context, that it definitely wouldn't be suitable for.
June 5, 2020 at 2:15 pm
My biggest gripe about ORMs, especially old Linq To SQL (but not limited to that one), is datatype mismatches where the passed variables are NVARCHAR() as criteria to be played against VARCHAR() columns. I've also seen it pass the following total nonsense as criteria when it was looking for the letter "A" in a single byte status column...
WHERE ASCII(@p1) = ASCII(SomeChar1Column)
--Jeff Moden
June 5, 2020 at 3:18 pm
[...]
Entity Framework is a bit fat wrapper around ADO and it took Microsoft about seven releases to get something that was relatively stable. It is essentially another version of Access as it is ok for simple databases with less than 10 tables. The real world is much more than 10 tables. [...]
...hmc
Sorry but this is exaggerated and no real argument.
EF is used in many many app out there, with way more tables etc .... I can tell from personal experience too! If you use it right, EF is fine.
June 5, 2020 at 3:23 pm
This was removed by the editor as SPAM
June 5, 2020 at 3:25 pm
WayneS wrote:In your article, you have:
var results = db.Customer.Where(c => c.State == "Ohio");Which is changed to this to limit the query to only the necessary columns:
var results = db.Customer.Select(x => new
{
x.State,
x.Name,
x.Address,
x.Email
});
Now, I'm not an application developer, but I can see that these two queries are not equal - there isn't a predicate on the second one, so it will get all of the rows in the table. For completeness sake, how would this second query be written to implement this where clause? Is it db.Customer.Where().Select()? Or db.Customer.Select().Where()? or something else? If I'm going to be telling the application developers that they need to change, I need to give them a valid example of how the change should be.
First, you would do the "where", then the "select"
var results = db.Customer.Where(c => c.State == "Ohio")
.Select(x => new
{
x.State,
x.Name,
x.Address,
x.Email
});
It should mentioned here that the returned object type is a dynamic type, and not the entity type 'Customer'.
This needs to be obeyed as dynamic types are accessible ("known") only within the current function (scope).
If the developers don't already know how to do this then you've got bigger problems.
June 5, 2020 at 3:34 pm
I have been using EF for four years now. What we have done in our environment is the following:
We use EF and only call stored procedures. Join them to functions and custom entity types when needed. This allows us to use POCO classes for our objects in C# that we share in our model layer with other applications when needed.
That's what a repository in your data layer should do (or could do).
In a clean abstraction, the EF entities shouldn't be known/used by any other parts/components than their repository classes.
June 5, 2020 at 5:21 pm
hamishdmccreight wrote:[...]
Entity Framework is a bit fat wrapper around ADO and it took Microsoft about seven releases to get something that was relatively stable. It is essentially another version of Access as it is ok for simple databases with less than 10 tables. The real world is much more than 10 tables. [...]
...hmc
Sorry but this is exaggerated and no real argument.
EF is used in many many app out there, with way more tables etc .... I can tell from personal experience too! If you use it right, EF is fine.
Heh... I'll disagree with your disagreement. It was a real PITA for quite a while. I agree that maybe it isn't as bad as an Access front end but it killed us by defaulting to connections using M.A.R.S. rather than defaulting to not using M.A.R.S. as advertised during one of its renditions.
--Jeff Moden
June 5, 2020 at 5:31 pm
If you use it right, EF is fine.
Now THAT I'll mostly agree with. The key is getting people to "use it right". For example, at one company I do work for, some develop0ers thought it wass a good idea to invoke sp_getapplock to "avoid concurrency issues". It had 36 of 48 CPUU threads blocked. We fixed it by having the developer write better code.
--Jeff Moden
June 12, 2020 at 4:10 pm
Good article. My only disagreement is with your functions being in the Controller - to my mind they should be in a service layer.
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply