Logic in SQL

SimonTemplar

I’ve recently inherited a system from a former co-worker which I believe to be a poorly architected system. A majority of the system’s logic is performed within SQL statements. For example, if a source table contains raw data and the destination table will contain results of calculations performed on this data, then all of these calculations are performed during the INSERT INTO destination_table statement. It makes extensive use of the DECODE keyword for the logic etc…

My experience of altering other people’s code is limited, but I’ve never seen this methodology before. I would always separate logic and database manipulation (i.e. three tier)

Am I wrong? Is performing core and vital logic in SQL like this acceptable?

Creamy Goodness

not that it's acceptable but you need to weigh up is it worth getting your hands dirty. I mean what's there works are you willing to tinker and chance breaking it?

Procasinator

There is times where it is acceptable to have logic in the database. But it can cause maintenance headaches, especially when it is the majority of the system.

Are these done in stored procedures or inline SQL within the code?

kayos

Sound's like your talking ETL of a sort (going from table a to table b with some transformation) in that case yes its acceptable and preferred. Why pull 10K rows of data over the wire to an app server to manipulate and then send it back over the wire. RDBMS's are designed to deal with and manipulate large sets of data.

If you are talking more along the lines that your application pulls data from the DB to be edited by a user and then just calls a Stored Proc to tackle all the validation and business logic then no its not the best imho.

COYW

OP, is there developer documentation available for this system? Perhaps there was a reason why the original developer built the system in this way. His stored procedures, (I am guessing that the SQL is not inline), may be called by another application, for example, as they may be legacy stored procedures which cannot be removed as they are used by another system.

srsly78

DBAs love to put logic into SQL - coz then they have a reason to exist.

SimonTemplar

Thanks for the replies.

The SQL is inline unfortunately. I could understand including logic in SPs.

The logic performed isn’t transformation (which again I’d understand). It’s basically calculating values based on fairly complex formulae. I would have put these calculations into it their own routines in the programme code.

The application is dealing with only a few 100 records, so speed isn’t an issue.

I’ve only very high level documentation and it doesn’t mention anything about this. More or less all of the logic done via SQL. The application code is only concerned with user interface and output.

SimonTemplar

Creamy Goodness wrote: »

not that it's acceptable but you need to weigh up is it worth getting your hands dirty. I mean what's there works are you willing to tinker and chance breaking it?

This application is evolving and I will have requests to add/improve functionality, especially as our product line changes.

This application is also known for bugs, so I'll need to maintain it.

kayos

In that case hunt down the original developer and beat them with a big stick.

SimonTemplar

That's what I thought.

I should also mention that I've come across variables called x, y, z etc, and routine names that bear no resemblence to what the routine is actually doing.

The whole code is a mess for what is a core system. Crazy!

Sparks

It's the kind of thing that's okay when it's a complex SQL query for a specific and relatively uncommon case; but:

1. It's low-hanging fruit for optimisation; stick that logic into an SP and you can usually increase your performance nonlinearly well (unless you had a really large query cache or some other caching setup);
2. It *does* have the advantage that because the logic is in the SQL query, it's in the source code and therefore is under the VCS's aegis; whereas SPs often aren't for various bad reasons; (but there are better ways to do this)
3. It *isn't* automatically going to be cross-DB (or even cross-DB-version) compatible, though I've seen this cited as a reason to do this (usually by managers who haven't done this themselves)
4. It's usually a bad idea to do any logic in SQL anyway; SQL is a set-based language, not a sequential logic based language, and usually the first rewrite of an SQL query is to go from an if...then type of logic to a set-based type of logic (see this for a reasonable intro to this).

TL;DR? Don't do it that way. Just... don't.

Procasinator

Well, if you want to make the system more manageable without a huge amount of work and disruption I would refactor the queries into stored procedures (or code if the case obviously calls for it), testing each change commenting the procedure and documenting what each piece does as you go along.

From here, hopefully you'll have a good grasp on all the moving parts and decide if it is worth (or needed) to make architectural changes to aid in the long run.

ChRoMe

Procasinator wrote: »

Well, if you want to make the system more manageable without a huge amount of work and disruption I would refactor the queries into stored procedures (or code if the case obviously calls for it), testing each change commenting the procedure and documenting what each piece does as you go along.

From here, hopefully you'll have a good grasp on all the moving parts and decide if it is worth (or needed) to make architectural changes to aid in the long run.

Yup, while I'm no DBA, breaking this into sprocs seems like the natural solution.

Sparks

ChRoMe wrote: »

Yup, while I'm no DBA, breaking this into sprocs seems like the natural solution.

...of course, this does assume the SPs get compiled or have some other optimisation done by the DB engine. Most DBs worth the name do this... but do check first

Procasinator

Sparks wrote: »

...of course, this does assume the SPs get compiled or have some other optimisation done by the DB engine. Most DBs worth the name do this... but do check first

DECODE is an PL/SQL function I believe, so his probably using Oracle.

In the very least OP, you should aim for prepared statements/parameterised queries if they are not already.

BostonB

Can you not ask the business users what this does? Or even the ex CoWorker? Is there much code in this logic. If not it would seem easier to move any requests for new functionality or updates, into somewhere where you understand it best, and its easier to maintain.

SimonTemplar

Thanks for the advice.

I fully intend to refactor this application for multiple reasons:
- eliminate the many bugs this application is known for
- for my own sanity while maintaining it
- to ease the handover to someone else when I eventually move on from this

I'm not going to attempt to develop any further changes using the SQL / Logic methodology. Although it is vital, the application it actually quite small and deals with very little data relative to our other systems, so refactoring it should not take very long. I'm basically going to get a proper spec from the business users and go by that.

Yes, I'm using Oracle. All the SQL in the application is created using string concatenation. SP are nowhere to be seen.

ChRoMe

Sparks wrote: »

...of course, this does assume the SPs get compiled or have some other optimisation done by the DB engine. Most DBs worth the name do this... but do check first

I've yet to come across a DB that doesn't do it, regardless sprocs will make this thing readable, that alone is worth it!

kayos

SimonTemplar wrote: »

All the SQL in the application is created using string concatenation. SP are nowhere to be seen.

Please say its at least preventing injection attacks....

Sparks

ChRoMe wrote: »

I've yet to come across a DB that doesn't do it, regardless sprocs will make this thing readable, that alone is worth it!

MySQL doesn't (or at least, doesn't do it the way you'd think it would, with one cache of precompiled SPs per db - instead you have one cache per connection). So your SP in MySQL is going to be slower than dynamic SQL queries almost all the time with the first query, and always on subsequent queries (assuming the query is identical, obviously) -- because MySQL stored procedures (at least in the early 5.x versions, it may have changed in the last year or two) don't use the query cache at all.

Mind you, MySQL didn't even have SPs until 5.0. And to be fair, it's somewhat out of MySQL's original design spec to support them, it's only slowly drifted into the "real database" role over the years instead of being aimed at it from the get-go like Postgres.

SimonTemplar

kayos wrote: »

Please say its at least preventing injection attacks....

I haven't seen any code that suggests this. The application is for internal use only (and is only used by about 4 users) so I don't think the developer even thought of this. The application doesn't even have a login system or an error log. It is painful!

ChRoMe

Sparks wrote: »

MySQL doesn't (or at least, doesn't do it the way you'd think it would, with one cache of precompiled SPs per db - instead you have one cache per connection). So your SP in MySQL is going to be slower than dynamic SQL queries almost all the time with the first query, and always on subsequent queries (assuming the query is identical, obviously) -- because MySQL stored procedures (at least in the early 5.x versions, it may have changed in the last year or two) don't use the query cache at all.

Mind you, MySQL didn't even have SPs until 5.0. And to be fair, it's somewhat out of MySQL's original design spec to support them, it's only slowly drifted into the "real database" role over the years instead of being aimed at it from the get-go like Postgres.

That article operates on the assumption that you are not using connection pooling and in an application server environment you aren't generally going to be dropping or creating sprocs on the fly.

That said as a developer I treat a DB pretty much as a bit bucket for the vast majority of the time

neil_hosey

how many stored procs are there? if there are less than 200 or so, ur best bet would be to rewrite it with OO in mind.

I hate that shít

Sparks

neil_hosey wrote: »

how many stored procs are there? if there are less than 200 or so, ur best bet would be to rewrite it with OO in mind.

Ah now here, leave it out.
OO is a fantastic tool, and makes certain tasks far easier and makes other tasks actually possible; but it's not a silver bullet and a blanket recommendation to rewrite an entire database to fit it based on an arbitrary numerical limit... well, that's not really well reasoned out.
So expand a little on your post
Why OO? What's wrong with straightforward procedural for this task? Or functional approches? Or any other kind of approach?
And why 200? And do you mean 200 of any length and complexity or did you have a more overall idea in mind of the total amount of code to go into the SPs?

DublinWriter

SimonTemplar wrote: »

Yes, I'm using Oracle. All the SQL in the application is created using string concatenation. SP are nowhere to be seen.

Have you checked for Functions/Packages too?

Are you talking about 'regular' Oracle SQL or Oracle PL/SQL?

philologos

SimonTemplar wrote: »

Thanks for the advice.

I fully intend to refactor this application for multiple reasons:
- eliminate the many bugs this application is known for
- for my own sanity while maintaining it
- to ease the handover to someone else when I eventually move on from this

I'm not going to attempt to develop any further changes using the SQL / Logic methodology. Although it is vital, the application it actually quite small and deals with very little data relative to our other systems, so refactoring it should not take very long. I'm basically going to get a proper spec from the business users and go by that.

Yes, I'm using Oracle. All the SQL in the application is created using string concatenation. SP are nowhere to be seen.

I'm surprised that no SP's were considered in the original DB design. Although I suspect it depends on what type if project it is. Our project at work involves a lot of data warehousing for report generation so SP's are pretty vital.