Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpolation

On May 4, 4:40 pm, Cimode <cim..._at_hotmail.com> wrote:
> > Cimode you rewrote some queries on that particular database and gained
> > a performance improvement, I'm sure that makes you feel good but you
> > cannot conclude a proof.
>
> This one added to the hundred others taking out cursors.
>

Cursors aren't really the issue, languages like T-SQL or PL/SQL are not general purpose languages and looping over millions of rows isn't their strength. They do somethings very well yet others quite poorly. It's just a case of knowing the limits and trade-offs but "favorite hammer" mentality seems to prevail.

Which one is faster:-

1) SQL and sets only,
2) procedures in PL/SQL or T-SQL
3) procedures in Java run on a DB's JVM,
4) low level DB calls (SQL*C).

None of the above, they all shine / stink at different times.

> > Your "proof" would have to be true across different queries, different
> > databases, different locking mechanisms, languages, developers,
> > schemas ...
>
> Fair enough.
>
> > I also have plenty of cases where a procedural approach yielded a 200
> > fold improvement but at other times a set based one did the same. I
> > cannot prove apples are better fruit than oranges.
>
> What performance was improved? I am curious.
>

I have so many examples where do I begin?

Recently I had a case where someone tried to loop over millions of rows in PL/SQL, it was very slow.

-I tried to rewrite the procedure to no avail,
-I tried to rewrite the SQL's again no good,
-I tried to pushing everything into one big SQL, much like you did,

-In the end I used two SQLs and a tiny bit of Java for the procedural aspect.

I've had hundreds of cases where something like Perl or awk out perform a DB vendor's "4GL" style language by orders of magnitude. Sure the latter might be bound closer to the DB and sure they integrate well with SQL but often they just don't crunch fast enough.

Nothing in the RM says those "4GL's" are "the solution" to all computational needs and nothing says the DML should be used for all algorithms and computations either.

Some algorithms don't lend themselves to set processing, especially if ordering is required. Set's are great for containing data but they aren't suppose to be the cornerstone of each and every algorithm and computation.

I fear I'm being too general but hope that answers your question?

Cheers. Received on Tue May 08 2007 - 03:47:50 CEST