loading...

re: Why I use SQL Common Table Expressions (CTEs) VIEW POST

FULL DISCUSSION
 

Other drawbacks:

  • You are transferring an application logic from an app server to DB server. If your DB server has 1-2 CPUs do not expect improvement in real world scenarios.
  • Scaling application servers (by deploying another staleless node behind a load balancer) is waaaay much easier than scaling a DB horizontally in case you experience performance issue due to all calculations happening on the DB server. In case of scaled DB service you would need to synchronize the data and make sure a transaction does not finish unless all nodes in a cluster have written data to disk or at least a transaction log) causing unnecessary latency for all DB queries, even those not related to your CTEs.
  • You may actually cause a transfer of much more data than if would do the operation in a memory of an application server. If you pull 2 tables, 1000 rows each and do the join on the app server your data transfer equals roughly 2000 * avg row size. Do that on the server and the result may be in the worst case a cartesian product of all rows for each JOIN operation, that might be 10 000 rows for one JOIN.
 

I think those are valid concerns for application development when users will be hitting your database with this query 100's or 1000's of times. In that case, you probably have the right to ask for another table or some other optimization.

The case that @helenanders26 is talking about here is more for an analyst that may not have to write privileges to the database and is not likely hammering it with the power of every user of the application. The main point is that it greatly improves readability.

Both are great points but from different perspectives.

 

Sure, this would be of concern if you need to implement reporting on top of a live database performing OLTP workloads. If you have the benefit of a dedicated database/server these points, except the data transfer amplification, should not have any negative impact.

code of conduct - report abuse