Design matters
November 7th, 2005 by Peter Scott
One of our customers has asked us to review the performance of one of their legacy data warehouses. The daily data load takes far too long. There seems to be a lot we can suggest here to improve things - for a start, we need move to an incremental load and not the complete reload of every table! Even the tables that won’t change are re-built, one example is the time dimension table, here we have 40 years of dates at day level denormalized to around 30 columns, the date range is hard-coded into the table create (and this goes 15 years into the future….) The other problem is that the original developers seem to have had little idea how Oracle works and what is in-built as functions; finding the date of the first of a month is coded as taking the month and year from a date (as two VARCHAR2 strings) and appending them to ‘01-’ to make a date string then converting back to a date - whatever happened to TRUNC(p_date,’MM’), simple, to the point and only one line of code? Likewise why code a hexadecimal to decimal conversion in 20 lines of if statements (and not even CASE or DECODE) when TO_NUMBER(p_hex_string, ‘XX’) does the job and I don’t have all those lines of code to maintain.
And as for the idea of writing a procedure to insert one row into a table and call it for each iteration of a cursor loop… just don’t go there.
November 7th, 2005 at 11:49 pm
…that trick Tom Kyte showed in his presentation on SQL techniques is very useful in ‘virtualising’ this TIMES/DATES table…I tried it earlier today and it’s neat…
November 8th, 2005 at 2:26 am
Looks like you got some fresh material for the WTF site…
It’s amazing how the same old errors just keep happening again and again. One day people will realize that ignoring prior knowledge is a sure fire recipe for failure.
November 8th, 2005 at 7:32 am
The other problem is that the original developers seem to have had little idea how Oracle works and what is in-built as functions; finding the date of the first of a month is coded as taking the month and year from a date (as two VARCHAR2 strings) and appending them to ‘01-’ to make a date string then converting back to a date - whatever happened to TRUNC(p_date,’MM’), simple, to the point and only one line of code?
Well, It turns out that the case (occurred to me a few weeks ago: I found this kind of TO_DATE(TO_CHAR(TO_DATE…)) in one of our apps) is more frequent than I could have thought.
Cheers.
Carlos.
November 15th, 2005 at 10:01 am
What’s more scary is the amount of times I find developers using:
to_date(sysdate)
to remove the time component. (ie, implicitly convert sysdate to char, convert back to date, and hope that the instance format mask doesn’t contain the time….)
Oh dear…