Pieces of SQL code stored in table

Question

Pieces of SQL code stored in table

RVO

SSCertifiable

Points: 5495
More actions
November 20, 2015 at 1:24 pm

#322422

I inherited this ETL process from guys who designed it 2 years ago.
It will be replaced by JAVA solution in 2016.
But I have to support the old ETL until they finish redesign.
The solution is based on SQL server 2005 SP_EXEC_WORKFLOW stor proc.
It is recursive procedure. Procedure is using nested CURSORS.
It finds steps from WORKFLOW_STEPS table,
loads them in CURSOR, loops through the steps.
SQL is stored in TRANSFORM_DML table
in columns like (WITH, SELECT, FROM, WHERE ).
SP_EXEC_WORKFLOW structure
------------------------------
Loop through workflow steps
---> Loop through Steps parameters
---> --->Call another SP_TRANSFORM procedure
--->--->--->Loop through TRANSFORM_DML columns
---> --->--->--->Build SQL statement from WITH, SELECT, FROM columns
---> --->--->--->--->Execute SQL
Personally I hate this design.
In my opinion it is an overkill, over engineering.
We are dealing with only 5 Source Systems.
There is no need to make it "super dynamic".
It's like somebody, not a real SQL programmer, like Java guy, for example,
decided to write SQL and he used all the techniques and methods he is using in his Java programming.
I wonder how popular this kind of solution is...
Did anybody come across something similar?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's popular when there's no DBA that can bring people to reason. Other than that, it's hard to see something so convoluted.

I remember a horror story about an implementation of a single table database which had data and metadata stored in the same place.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

RVO (11/20/2015)
I inherited this ETL process from guys who designed it 2 years ago.
It will be replaced by JAVA solution in 2016.

It'll be interesting to see how that works out. For those types of migrations that I've seen so far, they normally end up being a fair bit slower and a bit more difficult to maintain. Of course, considering the current code, even that will be a huge improvement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

I wonder how popular this kind of solution is...
Did anybody come across something similar?

I'm currently on a project migrating an Oracle-based data warehouse / Cognos Reporting solution over to a SSIS/SSRS/SSAS based solution. Perhaps it's not always like this but the version of Cognos I'm working with works very similarly where the "query builder" is GUI based, then Cognos "dynamically" creates one or more Oracle queries used for the report. It's slow and been absolutely painful (even for my colleagues who are Oracle gurus) to turn into SQL. It's been a painful experience. That's my assessment.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001