Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'll preface this question by saying that I am very new to professional software dev.

I work on a team that takes data in from other groups in my company and turns this data into reports usable by business execs.

In the process of transferring and parsing data we have some SQL statements that do a lot of processing of the data. Nearly every SELECT uses TRIM, SUBSTR, CAST etc extensively to reduce fields to the proper size and format. Additionally there are a lot of special cases that are accounted for by using CASE statements within SELECT's.

The Teradata server software that we use emits remarkably cryptic error messages. As a result we do a lot of guesswork about what data is breaking which SQL statement.

My question is: would it be a good idea to reduce these somewhat complex SQL statements to a less complex form that omits the processing and special case handling, and instead do this work in an external script or program? Does this make any sense?

share|improve this question

4 Answers 4

up vote 11 down vote accepted

A big advantage of moving the processing code out of your SQL is that your SQL becomes much simpler to manage.

A disadvantage is that if you ever want to use those queries in some other program, you now have to make your result-processing processes available to the other program. It could be as simple as copying a library file that contains the necessary classes, but it still means that any changes to the library have to propagated and all clients rebuilt with the new library.

Another option: Why not use a view (or multiple views if you need differently formatted results for different clients) to contain most of the formatting code? That way you can get the "raw" query results, or the nicely formatted one, depending on what you need.

share|improve this answer
3  
+1 for suggesting a a view which would allow them to separate the formatting SQL from the logic SQL. –  GlenH7 Jan 24 '13 at 18:49
2  
+1 for a view. Definitely the first solution I'd consider. –  Matt S Jan 24 '13 at 18:50

I agree with the suggestion already made about using a View for this logic. I would just like to add one more thing about the Case statements. Be aware that pulling the Case statements out of the SQL might result in a significant performance impact to the system. Those Case statements might be significantly reducing the amount of data returned. Running the Case filtering in the database layer through SQL statements is normally much more efficient than pulling all the data back and doing the filtering in an external script or program. If you are considering this, I highly recommend doing some data analysis and performance testing before moving forward with that solution.

share|improve this answer

Adding an external process usually just makes the system harder to debug, but it really depends on the situation. Use your judgment. Consider the time required to develop/maintain out-of-band projects.

Are you already using an ETL process? I don't have experience with Teradata, but separating your steps provides a much clearer view of what's going on. Here's a 2 second overview:

  1. Extract: Pull your data out of the source and place it into stage 1 temporary storage. Do not change the format of the data.
  2. Transform: Pull from stage 1, and do all of the case / trim / substr / cast / formatting etc... that you require here. Place it into stage 2 temporary storage.
  3. Load: Pull from stage 2, and put all of the data into the target storage.

This usually provides enough information to successfully manage this type of system.

share|improve this answer
2  
Ahh yes, ETL is precisely what we're doing. Except it appears to be something more like ETTTLTLTL with most of the Transform steps done in SQL. I think my goal is to write the transform steps in a more extensible language with better error handling than Teradata SQL, which is a disaster. –  Bryan Glazer Jan 24 '13 at 19:51

I'd be inclined to leave the CASE bits in place as these are related to the actual logic of producing the data for somone/thing to consume. So taking these out means you have to send a larger data-set back and the client has to do some processing on it - now you have split your report "logic" over two separate layers and this is not good.

But I'd drop like a hot brick any formatting from your code (unless it's specifically part of JOIN predicates, etc) because formatting it is the consumer's job...so whatever reporting tool they use, be it Excel, Crystal, etc, is good at formatting stuff in the correct locale and all that jazz. Let the client do whatit's good at (showing things in pretty colours) and let the server focus on what it does best - crunching data.

share|improve this answer
    
In some environments the application consuming the data may also be running on the server itself. Then the question becomes where is it more efficent to do formatting or other transforms. In some cases, especially where values repeat commonly, then it can be more efficient overall to let the server use a deterministic function once for each value encountered, and simply use the cached results, on subsequent occurrences of those values. Why have multiple applications all computing the same transform when the server can do it once for everyone. –  WarrenT Aug 5 '13 at 23:41
    
@WarrenT, that's a fair point BUT if these functions are deterministic then why bother even caching, just calculate and store as data is created in the tables...I took the OP's question to be more along the lines of formatting code, which is a bad idea to have in your database - you are assuming that all of these applications would want the data they display to their users to be in the same format. Which means that for e.g. everyone in your overseas office has to see report dates as dd/mm/yyyy just because the database is localised to British English. Surely you can agree that this is madness? –  Stephen Byrne Aug 9 '13 at 15:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.