with SQL. Ten table joins (11 tables), a subquery and a having clause. Seems he’s allergic to procedural code. He wants replace my little 25 line procedure* with a two page monster SQL query. It currently takes two seconds *** to return a single row. It is supposed to end up embedded into a large-ish ProC program which I justed timed running in 0.130 seconds. That is, to connect to the database, load up a dozens of host arrays, process data from about fifty tables, insert, update commit and disconnect in a 200th of the time it takes this one query.
Anyone got some helpful boss stories?
*Oracle PL/SQL, for anyone who cares, a loop, two fetches and a date comparision, about 25 lines plus a few declarations. Running time immeasurably short.
Unfortunately, I have to be that boss sometimes. We don’t allow PL/SQL here. It is just straight Oracle SQL spanning a few thousand jobs. Believe it or not, we have 100% plain SQL queries that are over 5000 lines long with a hundred or more subqueries plus every other exotic feature you can think of. I had never seen anything like it before I started working here. Two pages would be pitifully short and I find those complaints quaint now although I understand your frustration. I have to send queries back to the developer often to make them add code after code to account for some obscure scenario and they just grow and grow.
Sounds like a good solution but only if you know the replacement will be better!
Have you asked the boss why he prefers the pure SQL solution? Have you explained the timing differences?
Depending on the requirements of the task, I guess there could be good reasons why pure SQL is a preferable option but it doesn’t sound like it in your case.
I feel you pain with the 2-page SQL (and Shagnasty? 5000 lines? Dayum!). I have enough trouble with some of the SQL we have on our project, which is generated “on the fly” based on parameters a user selects in a window. Sometimes I have to troubleshoot it to figure out why it’s not returning data… which means running the surrounding code with auditing turned on so it prints out the SQL, then trying to parse that into something human-readable so I can figure out which clause is dropping the ball.
He seems to want to do everything in SQL on principle, he just doesn’t seem to like procedural solutions and particularly dislikes cursor loops as control structures Yup, sometimes pure SQL is elegant, but for this particular piece of logic it is a very bad fit. All of the new query is in a NOT IN(…) statement, ugh.
This will be the killer, he may be my direct boss (line-manager whatever) but he’s not the design authority for this part of the product. I expect the chap who decides what goes into this bit of the application will override him.
Shagnasty Why no PL/SQL? Not even in tidy database packages or functions? And how the frak do you analyse a query 5000 line long?
Cleophus He’s a nice chap, so that would be a bit of a shame. He is also a good programmer (the query does work) just a bit dogmatic.
You sure you’ve got your indexing right? <- intended as humourous (etc.) How long do you queries take? Minutes? Days? Our databases are pretty modest, anything that takes more than a fraction of a second is probably broken.
The worst performing query* I’ve seen written by a (not good) programmer here took a couple of hours to do an update. The re-written version took seconds. We know that badly written SQL performs poorly, it’s just amazing how bad it can get.
And Kev (wherever you are) you can make things worst by adding indexes.
Nava If no-one else answers I’ll post something tomorrow.
*as in badly written as opposed to wrongly returning tens of millions of rows.
I work for a mega-corp and we have many teams of analysts that are semi-technical plus those of us that are technical. They get extensive SQL training when they get hired but obviously the company can’t just turn everyone into a full PL/SQL developer. My group is literally responsible for several thousand jobs representing companies such as Wal-Mart and American Airlines plus many more very large corporations. Everyone needs the jobs to be as standardized as possible and readable by lots of different people so we just stick to regular SQL. Plus, turnover is very high and we need to get people at all levels up to speed as fast as possible so we just do it one way although it is admittedly strange.
I have never seen SQL used like that and our new develoipers eyes tend to bulge out when I try to get them started. We have many jobs 1000 - 2000 lines long range because the goals are difficult and the databases that we use tend to have about 200 tables to join to plus data analysis on what are the real current records for the purpose at hand. Combine that with a huge number of necessary subselects and many UNION queries and you end up with a monster.
The 5000 line jobs I was referring to have significant legal exposure if they are incorrect at all and they combine every complicated Oracle SQL feature there is. Those take anywhere from 2 months to a year to test and there are only 2 people including me in a 60,000 person company that are qualified to be the lead on their development. However, I am making progress with 2 very bright developers and they should be ready to do authoritative work in a few months.
I should add that we have no debugging tools either - not a single one. A comma out of place somewhere in 5000 lines renders the whole thing useless. I thought I was good at SQL before I started there over 2 years ago. I was mistaken but I really am now. I can look at someones work and read it like a novel and almost as fast pointing out flaws.
I’m not sure if this applies since you would probably get caught, but I’ve always gone by the rule that you do things right, but you tell the boss you did it the way he/she said to do it. Yes, lie.
In SQL you issue requests or instructions to a database, usually…
SELECT FROM…
INSERT INTO…
UPDATE…
DELETE…
With conditions stated in a ‘where’ clause
WHERE EMPLOYEE_ID = ‘007’
There’s no looping, no if/else, no begin/end. So to implementing logical options in plain SQL can get convoluted. Some things are easier to do in a language that lets you use a simple if/else.
I had a boss (in 2004) who not only sat me down and taught me how to use Google (“You put your search terms in this box, and then you click “search,” and if you want, this button here will take you directly to the first hit…”), but he kept resetting my search preferences to the way he liked them, because he thought that I was actually using it the way he had taught me to.
Yup, sometimes pure SQL is elegant, but for this particular piece of logic it is a very bad fit. All of the new query is in a NOT IN(…) statement, ugh.
(etc.) How long do you queries take? Minutes? Days? Our databases are pretty modest, anything that takes more than a fraction of a second is probably broken.