kpi6288@xxxxxxxxx writes: >> -----Ursprüngliche Nachricht----- >> Von: Ravi Krishna <sravikrishna@xxxxxxx> >> 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
