|
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
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')
|
|
|
|
|
Wow, but isn't storing dates as strings below the lowest common denominator??
|
|
|
|
|
That's called error margin 
|
|
|
|
|
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.
|
|
|
|
|
Wow,
Just looked at a stored procedure here which takes 8 hours to run (nightly) and was kind of horrified to find that it runs to 17771 lines!
Just, wow.
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
Don't you know that stored procedures are the fastest possible way to do something on the database? Surely it doesn't matter how it is written. it still has to be faster than to do it in a more maintainable way 
|
|
|
|
|
I agree with your argument of being the fastest way to interact with the database data, but it also means the business logic stays in the database and is higly coupled with the db system.
In an evolutive world as the one we live in, this is not recommended, although this is probably a system from a big corp that was built on older paradigms. At this point, they probably have no other choice. 
|
|
|
|
|
I'd be hard pushed to describe any of this as logic!
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
Actually, the little bit I looked at today had rafts of queries of the form..
INSERT INTO TEMPTABLE (A, B, C, D, 'OBJ TYPE 1')
SELECT A, B, C, D
FROM MASTERTABLE
WHERE OBJTYPE = 1 ' OBJ TYPE 1
INSERT INTO TEMPTABLE (A, B, C, D, 'OBJ TYPE 2')
SELECT A, B, C, D
FROM MASTERTABLE
WHERE OBJTYPE = 2 ' OBJ TYPE 2
(without the comment. Theres absolutely zero comments)
Rinse and repeat for a further 13 object types, then apply a set of 15 update queries to the result based on other selections based on the same types. If I ever have to get my hands dirty, I'll begin by refactoring those out to other procedures, and use a parameter to specify the object type - that's if I can't do the same thing more efficiently by GROUP BY in some way. Hopefully after a while some wood will begin to emerge from the trees.
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
Put that BACK!
Nicholas Marty wrote: What?
The pish you just took! 
speramus in juniperus
|
|
|
|
|
I was further amazed to discover the guy who wrote it (used to work for a third party who used to do the development) now works for Microsoft!
I did joke that maybe his job is to push their query optimisation team to the limits. 
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
|
Does the stored procedure do the job it was intended to do?
Does it make business sense to decouple it and break it down into smaller pieces?
If it works, and doesn't need to be decoupled, then where is the issue?
|
|
|
|
|
Tim Carmichael wrote: then where is the issue?
it takes 8 hours to run...
|
|
|
|
|
Apparently, about 18 months ago it took a couple of hours. At this rate, we soon won't be able to run it overnight, as our database doesn't tend to get smaller over time!
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
Is the growth in execution time linear or exponential?
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
I don't know yet, I only have 2 data points.
(and one of them is fairly rough - I only know exactly for the latest few runs)
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
At present it kind of works. Our system deals with a number of types of object, if a new type is added, we will need to maintain it. Nobody has been brave enough to tackle the thing, and no one really understands how it works. But apart from that, all is hunky dory.
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
Just call it magic and back off slowly. And don't turn your back on it until you're out of sight!! 
|
|
|
|
|
Convert.ToInt32(Int32)[^]
Quote: Returns the specified 32-bit signed integer; no actual conversion is performed.
Most useful method evar....
modified 27-Nov-13 14:00pm.
|
|
|
|
|
What do you expect? Convert.ToInt32(Int32) does nothing, you are trying to point it out as a joke, but its actually quite useful especially when you don't know the type that you are passing into it (which is why Convert has so many ToXXX overloads). Convert.ToDouble(Double) is the same, as well as Convert.ToSingle(Single), etc.
|
|
|
|
|
Hmm... totally missed this usage case, you're right. More often than not though, I use the Convert class to convert objects where I do know what I'm passing in, so it just struck me as odd when I was browsing documentation. I feel like they could have mentioned this in the remarks rather than a 'nothing happened' remark.
|
|
|
|
|
There are even more usage scenarios than that, take for example the following code:
public void SomeDummyMethod(int myNumber)
{
double myDouble = Convert.ToDouble(myNumber);
return myDouble * 1000.1f;
}
Now, lets say later down the line you get somebody who says "wait, myNumber needs to be a signed byte!"...
Well now you only have to change one line of code:
public void SomeDummyMethod(SByte myNumber)
{
double myDouble = Convert.ToDouble(myNumber);
return myDouble * 1000.1f;
}
Then later somebody comes around and says, "no, it should be a double to begin with"...
public void SomeDummyMethod(double myNumber)
{
double myDouble = Convert.ToDouble(myNumber);
return myDouble * 1000.1f;
}
This is an overly simplified case obviously, but imagine if there were 100 or 200 lines of code in the function, if they didn't have Convert.ToDouble(double) the one change at the top would break unknown lines of code below.
Plus, the design strategy for the class was Convert should convert from any numeric type any other numeric type. Oddly enough that also means converting from something back to itself...
On top of all that, it really helps support the IConvertable [^]interface later on, and even says in the documentation:
"The common language runtime typically exposes the IConvertible interface through the Convert class. The common language runtime also uses the IConvertible interface internally, in explicit interface implementations, to simplify the code used to support conversions in the Convert class and basic common language runtime types."
So much more useful than you think 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
