We often rewrite the queries to use TVFs instead



Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com>

On Wed, Sep 21, 2016 at 10:35 PM +1000, "David Rhys Jones" 
<djones...@gmail.com<mailto:djones...@gmail.com>> wrote:

>> 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.


Si hoc legere scis nimium eruditionis habes.

On Wed, Sep 21, 2016 at 2:22 PM, Corneliu I. Tusnea 
<corne...@acorns.com.au<mailto: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,

On Tue, Sep 20, 2016 at 9:06 PM, Tony McGee 
<tmcgee...@gmail.com<mailto: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

    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
   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
       Most processing was done without EF,
      The website used EF to query the same data.
       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.
      EF context is queried and disposed of as soon as possible, leaving the 
website responsive
     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.


Si hoc legere scis nimium eruditionis habes.

On Tue, Sep 20, 2016 at 10:03 AM, David Connors 
<da...@connors.com<mailto:da...@connors.com>> wrote:
On Tue, 20 Sep 2016 at 13:59 Greg Low (罗格雷格博士) 
<g...@greglow.com<mailto: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 Connors
da...@connors.com | @davidconnors | LinkedIn | +61 417 189 363

Reply via email to