Re: How do you like this SQL?
Date: Fri, 14 Dec 2012 09:30:33 -0800 (PST)
Message-ID: <f76839f1-066d-44b4-a733-b569aed9716c_at_m4g2000pbd.googlegroups.com>
On Dec 14, 3:15 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Fri, 14 Dec 2012 02:17:41 -0800, dombrooks wrote:
> > It's very hard to condemn a snippet of SQL.
>
> Well, not always.
>
>
>
> > But whether it is sensible/desirable to do these lookups as a forced
> > nested loop equivalent depends on the bigger picture.
>
> Dom, the comment about the unusual and cruel punishment was made because
> the model is obviously messed up. Whenever there is a very big table which
> needs counting records of certain type, there is a table missing.
> Second, one could use analytic functions in the subquery, which would
> probably be much faster.
>
> --http://mgogala.byethost5.com
Well, if the model is messed up, isn't it kind of useless to blame the developer? I run into this kind of stuff all the time, since I do maintenance and customization on MRP/EPR, written in a SQL generator. When I have to fix data, I have the choice of using SQL or the more abstracted 4GL, which can generate even worse SQL. So my first thought on seeing the OP was "shoot, that is way too much like what I've been working on." In my case, the model isn't really messed up, it merely suffers from necessary over-generalization to accommodate various business practices (I have to add rows with line numbers one greater than the max, based on a subset of the attributes). When I'm under time and correctness constraints to fix data more than coding a production quality program, I have no problem writing bizzarro code, slow-by-slow generated in a shell script from a report, or 3GL style 4GL code. You can't argue the data should be captured with correct constraints, because the requirements and definitions are changing. Sometimes this code gets productionized when people realize it's useful on an ongoing basis. So it goes.
I'll often use SQL just because it makes me think in sets and I think that's the right way, but when that gets too squirrelly, whatever the problem calls for that I'm most comfortable with.
I have not seen any proof that analytics will be faster, aside from obvious situations where it avoids multiple passes through the data.
jg
-- _at_home.com is bogus. "He who refuses to do arithmetic is doomed to talk nonsense." - John McCarthy http://www-formal.stanford.edu/jmc/progress/ Of course, thinking that arithmetic is sufficient seems to be the basic limiting factor of artificial intelligence.Received on Fri Dec 14 2012 - 18:30:33 CET