(ab)using SQL to write whole programs

[House213]

Hi, I have a good background in languages like Java/C#/Python/Matlab/VBA. From my experiences I can tell people do not always use languages/tools in the right way. For example: I had to write full working production software using Excel + VBA, a real nightmare I can tell you. C#/Python would do much better for these kind of jobs

Now I am assigned to a new project and as we work with a lot of data SQL comes in sight. For this project people write entire algorithms using the SQL language which go way beyond joins and aggregates. I see SQL scripts on my screen in the range of 100 - 1000 lines of code. As I am not an SQL expert I find it hard to value this. Do they use SQL in the right way or are they abusing SQL and would alternatives be much better, for example SQL for a first selection and Python + pandas for further analyzing?

I am highly aware of the speed and widespread use of SQL which make it a standard for database management. However I am not sure if I want to invest time in it. I feel a bit embarrassed as the foundation of SQL lies in set theory and relational algebra which should make it easy to understand for me as I have multiple degrees in (abstract) math. But I feel some kind of resistance to it, SQL just feels ugly. I highly prefer beautiful languages like Python over ugly languages/tools like PHP or VBA.

 

[Broheim]

well, it depends on what stored procedures/functions/triggers actually do. Business logic does not belong in the data layer, and the database should only concern itself with providing and storing data as efficiently as possible.

I see this problem a lot with old farts that are a little too comfortable in the bungled mess of a database they've cobbled together over many years of "eh, if it works..". I once worked on a project with an extremely complex set of business rules for when a product was available for delivery and how far ahead of time it had to be ordered for a particular expected delivery date (these were all customer specific). The client had created their own ecommerce platform, but because it was terrible, they hired us to make them a new one that didn't suck. One of the biggest issues was that they had so much of their business logic in stored procedures and functions, hidden behind very expensive calls to SQL server, this completely tanked performance.

They made use of a lot of clever algebra to get around lack of features in T-SQL and to optimize performance of the script, but in the end it could take them seconds to access their core business logic. Nevermind the fact the arcane scripts were damn near unreadable and i spent 2 days just understanding the logic in them.

Once i rewrote the logic in C# and a lot of optimization and refactoring was done, it was now a 1000 times faster, literally. This meant rather than doing clunky last minute checks in the checkout flow, these calculations could all be done every single time a product is displayed, significantly improving user experience.

 

[Merad]

This one of those topics where you will find that people that hold basically all of the different beliefs, usually based on their background and experience. IMO, business logic does *not* belong in the database, it should be in code - unless it's something that's intrinsically tied to the data. For example, a change tracking system might be much easier to implement with triggers and stored procedures rather than trying to handle it all in code. Ideally the logic and data access should be encapsulated by an API so that people or apps who interact with the data don't have to understand the logic or repeat it in their code.

 

[urvile]

The system I am currently working on has the business objects created in SQL which are dynamically built at runtime. The querys that are generated are crazy. It has lots of complex stored procedures too. Not to mention some gaping security holes. Fortunately it's not web facing. The system is ~10 years old and is now evolve ware. Which basically means it has no coherent design and is a big ball of mud but that's what happens. It isn't uncommon. There is a limited budget and many devs have worked on it over the years. Some good, some bad.

Personally I prefer a data layer using linq which then sits behind a service layer so we can hook other systems into it if we need too but sometimes you don't need too....so you can just use a separate dll. You could even go crazy and use a generic interface then inject the data layer at runtime. Then you can inject different datalayers that implement that interface. (linq, web service etc. kind of like an adaptor) Although in practice that is harder than it sounds.

Also no one likes VBA or PHP.