Re: AW: CTE with JOIN of two tables is much faster than a regular query

kpi6288(at)gmail(dot)com writes:

>> -----Ursprüngliche Nachricht-----
>> Von: Ravi Krishna <sravikrishna(at)aol(dot)com>
>> Gesendet: Samstag, 18. August 2018 18:25
>>
>> > What can I do to improve the performance of the regular query without
>> using a CTE?
>>
>> Why do you care ? When I find that I can write a SQL 3 different ways, I will
>> go for the most efficient one. So why not accept the CTE version of this SQL.
>> Just curious.
>
> We're using object mapping / entity frameworks (e.g. XPO, Entity Framework Core). These frameworks support regular queries out-of-the box; a CTEs require additional effort and are more difficult to maintain.
>

Ah, another reason to avoid object mapping/entity frameworks! I guess
really the same reason - loss of flexibility and expressive power.

Sorry, having a similar battle with some developers who are insisting on
using a particular framework because it makes maintenance easier as it
'automates' creation of controllers (MVC). However, they are frustrated
by performance and I'm frustrated as the framework also fails to pass
additional information, such as PGAPPNAME, which would make some
analysis easier. Part of the reason for the performance issues is
because the developers are doing things with result sets within the
client that would be far more efficient performed within the database.

One way I have resolved this in the past is to create database
procedures which present a 'mapped' view back to the framework layer
which hides the SQL from the framework. Works well, with the only main
downside being you now have SQL in a different (another) place, which
can make some people uncomfortable and can be a maintenance issue if all
your developers are just front-end devs who treat a database as just a
key/value repository. .

Tim
--
Tim Cross