>> SQL Functions suck. Oh my, they suck and they are hard to fix and cumbersome to figure out where perf is bad.
I agree, don't use SQL functions in where clauses, joins or aggregates. It's ok to use if you have a small dataset but otherwise steer clear. Davy *Si hoc legere scis nimium eruditionis habes*. On Wed, Sep 21, 2016 at 2:22 PM, Corneliu I. Tusnea <corne...@acorns.com.au> wrote: > I'll jump in with my experience (just last year). > Using EF7 (EF Core 1.0 now?) > > I always disliked EF version but I liked the original Linq2Sql which was > quite lightweight compared to EF. > > 1. Database design and DTO design is very very strictly monitored and > mapping Table > Entity is very strict with only the exact fields required. > 2. I find EF perfect for most simple reads of data as long as I don't > have to join or simple very exact joins. Yes, I know Dapper and PetaPoco > but heck, I really don't want to maintain strings. > 3. Views rule. Whenever we need more complicated data we do it in a > view. We can review, optimize and tweak that as needed. > 4. SPs rock. Same as above, any work that can be moved in an SP is > done in an SP (very few scenarios in our case) > 5. SQL Functions suck. Oh my, they suck and they are hard to fix and > cumbersome to figure out where perf is bad. > 6. And most importantly. SQL is long-term-storage NOT the source of > truth. We use Akka and we (now) consider SQL as eventual storage of data. > Everything we do is in memory and whenever we have a chance we'll tell SQL > about it (mostly so after a restart we can start with the data from SQL). > You want to save some settings? Sure, it's in memory and hey sql, here is > an update. Want to read a setting? It's in memory no need to ask SQL about > it. > > I think EF7 (as I said previous versions were garbage), like every other > technology can be abused. > The problem is that it can be abused way to easily. > Teams use it and abuse it instead of understanding the synergy that needs > to exist and where the power EF offers should be used. > > My 2 cents, > Corneliu. > > > > > > > > On Tue, Sep 20, 2016 at 9:06 PM, Tony McGee <tmcgee...@gmail.com> wrote: > >> Oh boy, this is a technique I see way underutilised when using EF: >> >> >> *All objects from EF were transformed into new objects for use in the >> website *e.g. If I just want a high level list of the product categories >> a customer has purchased, it's far too easily get stuck in a rigid thought >> pattern due to the object model. It says I need a Customer that has an >> Orders collection each having a set of Line Items, dollar values, >> quantities, special delivery instructions, product names, descriptions, >> packaging dimensions, blah, blah, blah.... NO. >> Bringing the whole database across the wire and aggregating in >> application memory is inviting a world of pain. >> >> An EF query projection containing the customer id/name and product >> category name could avoid a huge complicated SELECT * across six different >> table joins that becomes impossible to index. >> >> >> >> >> On 20/09/2016 19:20, David Rhys Jones wrote: >> >> >> I've been working with EF now for a few years, here's a list of what >> went wrong / what went right. >> >> *Large public Website* >> >> *Good:* >> No complex queries in EF, anything more than a couple of tables and a >> stored procedure is called. >> All objects from EF were transformed into new objects for use in the >> website >> *Bad:* >> The context was shared between processes and thusly began to grow >> after an hour or two, causing a slowdown of EF. Regular flushing solved this >> Updates into the database set the FK property but did not attach the >> object, this resulted in data being correct for a moment, but then >> overwritten with the original values when the savechanges was called. >> >> >> *Large Multinational Bank - Bulk Processing* >> *Good:* >> Most processing was done without EF, >> The website used EF to query the same data. >> *Bad:* >> Framework implemented IEnumerable as each interface, thus >> service.GetClients().Count() resulted in the entire table being returned. >> Changing the interface to IQueryable allowed the DB to do a count(*) >> >> *Large Multinational, low use public website. * >> *Good:* >> EF context is queried and disposed of as soon as possible, leaving >> the website responsive >> *Bad:* >> Bad design of the database has resulted in needless queries bringing >> back data that is not used. All EF generated queries are complicated. >> A mixture of stored procedures and EF context is used within a >> process resulting in incorrect values. >> >> >> I quite like EF, it's efficient to write queries in if you know what is >> being generated at the database level. I always output the SQL query to the >> debug window so I know what is being passed to the DB. >> But if the query is not self-contained and requires a lot of tables, then >> a specific stored procedure should be used. However, do not update with a >> stored procedure if you are using Entity to read back the values. Do POCO >> updates and read the linked objects and attach them correctly. >> >> Davy. >> >> >> >> *Si hoc legere scis nimium eruditionis habes*. >> >> >> On Tue, Sep 20, 2016 at 10:03 AM, David Connors <da...@connors.com> >> wrote: >> >>> On Tue, 20 Sep 2016 at 13:59 Greg Low (罗格雷格博士) <g...@greglow.com> wrote: >>> >>>> I often get coy when I hear comparisons with Stack Overflow, Twitter, >>>> Facebook, Blog Engines, etc. though. >>>> >>>> Most of those platforms are happy to just throw away transactions when >>>> the going gets heavy. >>>> >>> Also, most of their workloads are read-only and so highly cacheable at >>> every layer of whatever architecture you choose. >>> >>> Once you throw consistency and transaction isolation under the bus shit >>> gets pretty easy pretty quick. >>> >>> David. >>> >>> -- >>> David Connors >>> da...@connors.com | @davidconnors | LinkedIn | +61 417 189 363 >>> >> >> >> >
