More Stored Procedure Nonsense
-
The post below reminded me of an instance a few years ago. Existing database, mainframe system, all working well. I was a short term contractor. Sales chap from "a reporting tool" company had been in and shown the MD and sales manager the wonderful real-time data their product could present them - lovely graphs and charts etc. Of course, he showed them on his local database on his laptop. Having bought the software, they employed me to implement the changes necessary to their database in order to use this new, magic, tool. Performance-wise it wasn't possible for the tool to query the existing database, so a form of data warehouse was necessary. It was decided that an overnight process would update the data warehouse, rather than real-time updating, as the transaction volume was high enough that benchmarking showed a potential significant decrease in overall performance. So no real-time reporting - but the MD was happy that yesterday's data would be up-to-date enough. So (with guidance from the reporting company) I developed a bunch of stored procedures to run overnight to collate the day's transactions into a format acceptable by the tool. And let me tell you, the tool was very fussy about its data types; e.g. if you wanted to summarise by date, then the date column had to be a date column in a specific format - preferably text in yyyymmdd format - with no time component. Any codes to be summarised on also had to be specific formats (I don't recall exactly, but from memory, numerics needed to be in text fields, right justified with leading spaces) So I spent a few weeks writing and tweaking stored procs. First live run on a day's transactions to about 25 hours to run. Not so good. Optimisation and more tweaking - fastest we got it down to was about 15 hours - which meant if we kicked it off bang on 5pm it might be ready for the MD in the morning. Unfortunately, when we pointed the reporting tool at it, it just sat there. We never saw any results from the full data warehouse - the tool just appeared to hang. In the end, we had to create a summary database and a 'this week' database just for the tool to be able to display anything, and the MD had to print stuff out so he could compare week by week.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
The post below reminded me of an instance a few years ago. Existing database, mainframe system, all working well. I was a short term contractor. Sales chap from "a reporting tool" company had been in and shown the MD and sales manager the wonderful real-time data their product could present them - lovely graphs and charts etc. Of course, he showed them on his local database on his laptop. Having bought the software, they employed me to implement the changes necessary to their database in order to use this new, magic, tool. Performance-wise it wasn't possible for the tool to query the existing database, so a form of data warehouse was necessary. It was decided that an overnight process would update the data warehouse, rather than real-time updating, as the transaction volume was high enough that benchmarking showed a potential significant decrease in overall performance. So no real-time reporting - but the MD was happy that yesterday's data would be up-to-date enough. So (with guidance from the reporting company) I developed a bunch of stored procedures to run overnight to collate the day's transactions into a format acceptable by the tool. And let me tell you, the tool was very fussy about its data types; e.g. if you wanted to summarise by date, then the date column had to be a date column in a specific format - preferably text in yyyymmdd format - with no time component. Any codes to be summarised on also had to be specific formats (I don't recall exactly, but from memory, numerics needed to be in text fields, right justified with leading spaces) So I spent a few weeks writing and tweaking stored procs. First live run on a day's transactions to about 25 hours to run. Not so good. Optimisation and more tweaking - fastest we got it down to was about 15 hours - which meant if we kicked it off bang on 5pm it might be ready for the MD in the morning. Unfortunately, when we pointed the reporting tool at it, it just sat there. We never saw any results from the full data warehouse - the tool just appeared to hang. In the end, we had to create a summary database and a 'this week' database just for the tool to be able to display anything, and the MD had to print stuff out so he could compare week by week.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
_Maxxx_ wrote:
date column in a specific format - preferably text in yyyymmdd format
:wtf: Was the reporting tool built as a school assignment?
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller
-
The post below reminded me of an instance a few years ago. Existing database, mainframe system, all working well. I was a short term contractor. Sales chap from "a reporting tool" company had been in and shown the MD and sales manager the wonderful real-time data their product could present them - lovely graphs and charts etc. Of course, he showed them on his local database on his laptop. Having bought the software, they employed me to implement the changes necessary to their database in order to use this new, magic, tool. Performance-wise it wasn't possible for the tool to query the existing database, so a form of data warehouse was necessary. It was decided that an overnight process would update the data warehouse, rather than real-time updating, as the transaction volume was high enough that benchmarking showed a potential significant decrease in overall performance. So no real-time reporting - but the MD was happy that yesterday's data would be up-to-date enough. So (with guidance from the reporting company) I developed a bunch of stored procedures to run overnight to collate the day's transactions into a format acceptable by the tool. And let me tell you, the tool was very fussy about its data types; e.g. if you wanted to summarise by date, then the date column had to be a date column in a specific format - preferably text in yyyymmdd format - with no time component. Any codes to be summarised on also had to be specific formats (I don't recall exactly, but from memory, numerics needed to be in text fields, right justified with leading spaces) So I spent a few weeks writing and tweaking stored procs. First live run on a day's transactions to about 25 hours to run. Not so good. Optimisation and more tweaking - fastest we got it down to was about 15 hours - which meant if we kicked it off bang on 5pm it might be ready for the MD in the morning. Unfortunately, when we pointed the reporting tool at it, it just sat there. We never saw any results from the full data warehouse - the tool just appeared to hang. In the end, we had to create a summary database and a 'this week' database just for the tool to be able to display anything, and the MD had to print stuff out so he could compare week by week.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Good god, that all sounds so eerily familiar.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
_Maxxx_ wrote:
date column in a specific format - preferably text in yyyymmdd format
:wtf: Was the reporting tool built as a school assignment?
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller
Actually no, but it wasn't very clever at accessing different databases - so went for the lowest common denominator!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
Actually no, but it wasn't very clever at accessing different databases - so went for the lowest common denominator!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Wow, but isn't storing dates as strings below the lowest common denominator??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
Wow, but isn't storing dates as strings below the lowest common denominator??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak