Wednesday, October 18, 2006

Slow by Slow...

Was reading the "theDailyWTF" site - and saw Alex going on about slow by slow processing (in a sqlserver database no less).  Really hits home - I see it all of the time.

Why do people feel compelled to write code like:

for x in (select * from t1)
loop
insert into t2 (c1,...) values (x.c1,...);
end loop;


Instead of just "insert select".  I'll never ever understand it.  Just last week on asktom - a similar thread came up.  The "boss" wanted the developer to not use a single SQL statement to process data, but loops of loops (a do it yourself nested loop join!!!).


I was working with a customer who was seriously considering moving to another database because "Oracle was slow".  So, I asked for a chance to see if we couldn't speed things up.  I was given some of their code - it looked like this:

insert into t ( empno, effective_date, .... )
select empno, effective_date, ....
from t1, t2, t3, t4, ...
where ....;

loop
delete from t
where (empno, effective_date) in
(select empno, min(effective_date)
from t
group by empno
having count(1) > 1);
exit when sql%rowcount = 0;
end loop;

Now, do you see any problems with that?  This was a data warehouse - big table.  Basically the logic was to try to "keep the most recent employee record".  How did they do that?  They got ALL employee records (filling the table up) and then removed bit by bit the ones they did not want.  Assume every employee had at least two historical records - you would have a table that is half empty after the first pass (most had more than one!) and not only that, but you would constantly full scan the table T over and over until you found no records to remove (just one employee with 100 records - you loop 101 times).


Further, I explained that not only does it remove the "old" records - if two records existed for some employee on the newest date - both of them would disappear (no records for that employee).  They said "no way, that isn't what that code does" - had to prove it to them (just put in two records with the same empno/effective_date and run the loop - bamm - they both go away).


I rewrote the code as follows:

 insert into t ( empno, effective_date, .... )
select empno, effective_date, ......
from
( select empno, effective_date, .... ,
max(effective_date) OVER ( partition by empno ) max_edate
--count(effective_date) OVER ( partition by empno, effective_date ) cnt
from t1, t2, t3, t4, ....
where .... )
where effective_date = max_edate
--and cnt = 1;

We added the comments to the COUNT check after determining that it was a bug in their code.  This insert as select ran faster than their insert did (far far far fewer records to insert), resulted in a table with no "whitespace" (which was nice for their data warehouse and subsequent full scan queries!). And the delete loop - it was gone


Turns out Oracle wasn't slow, the hired consultants wrote slow code.  Anyone can write slow code :)


My mantra, that I'll be sticking with thank you very much, is:



  • You should do it in a single SQL statement if at all possible.

  • If you cannot do it in a single SQL Statement, then do it in PL/SQL.

  • If you cannot do it in PL/SQL, try a Java Stored Procedure.

  • If you cannot do it in Java, do it in a C external procedure.

  • If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

think in sets...


learn all there is to learn about SQL...


Analytics rock and roll...

POST A COMMENT

41 Comments:

Blogger Q u a d r o said....

Guess before they realised problem exists in their own code they did all sorts of hardware upgrades :)

Wed Oct 18, 08:19:00 PM EDT  

Anonymous Anonymous said....

Tom, IMHO the problem you described has an interesting dimension, all to human, sigh.

> The "boss" wanted the developer to
> not use a single SQL statement
> to process data...
Why on Earth would a boss tell their developer how exactly to process data? That only happened to me once in maybe 10-12 years. Usually, the clients tells me what they are planning to achieve and let me handle the details. Am I being too lucky, or just too inexperienced?

Hoping for an extensive comment. :-)

Wed Oct 18, 08:19:00 PM EDT  

Blogger Thomas Kyte said....

m I being too lucky, or just too inexperienced?

I think you've been luck.

This is why I believe there are two answers to any question:

a) WHY?????
b) it depends.

I don't like being told HOW to do something, I don't mind being asked how something should be DONE.

If the people posting on asktom are representative of people at large (and I believe they are based on my interaction with people elsewhere), "my manager made me do it this way" is a common refrain.

Wed Oct 18, 08:25:00 PM EDT  

Blogger Noons said....

Unfortunately, a lot of people still use the non-relational database mindset of "row-at-a-time".

Completely forgetting that the whole purpose of SQL is to process data "set-at-a-time".

Just this little bit of awareness would solve 90% of the perfomance problems out there...

Wed Oct 18, 08:52:00 PM EDT  

Anonymous Anonymous said....

I'd be inclined to add the word "static" (vs dynamic) to the if you can do it in a Single Sql Statement line.

We have a new "overly-engineered" system at my work where "EXECUTE IMMEDIATE" was the cool way to do things. For sooooome reason the production machine that this box runs on seems to be just a little cpu bound.

Rather than fix the code, a RAC solution was proposed (probably by HP).

Wed Oct 18, 09:35:00 PM EDT  

Blogger Q u a d r o said....

"Rather than fix the code, a RAC solution was proposed (probably by HP)."

So to make things WORSE (RAC isn't application fixing tool)?

Just saw system like that... horrible.

Wed Oct 18, 09:38:00 PM EDT  

Anonymous Anonymous said....

Correct, I understand that, but I just support dulllll legacy systems.

I kid you not, some of that code is so bad, it could be used in a public service announcement ala "Friends don't let Friends use dynamic sql" or "This is RAC, this is RAC on dynamic sql sizzle"

Seriously though, I did forward on a few blubs from asktom regarding the RAc solution to my boss. Hopefully we can stop the madness.

Wed Oct 18, 10:05:00 PM EDT  

Anonymous Anonymous said....

When I first started writing Pro C, I used to see lot of 'slow-by-slow' stuff and never understood why it was so, when a single SQL statement would have been just fine. I just figured I was the lazy guy around. (I still figure that in other ways, but that's a different thing ..;)

Thu Oct 19, 02:30:00 AM EDT  

Anonymous Anonymous said....

Tom, this one is a classic. Fixing the problem by changing the code. To me this is the sort of code that comes from a junior developer's mind (procedural vs set-oriented), and where I think a DBA in a friendly manner can show developers how to utilize the database better.

If you can't change the code, RAC might be a good thing. Bad code may not run faster, but if you can lock bad code running on one node only with its own SGA and TNS-alias the rest of the users may come better off.

Like when you have eBusiness running just fine until management decides they need a datawarehouse...:
"select * from apps.nightmare_view@OA..."

Thu Oct 19, 04:10:00 AM EDT  

Blogger Nur Hidayat said....

What a co incidence. It's just happened to me also.

I just rewrote a .net application which insert data from excel file row by row into database. The performance was very very poor, 4 minutes to insert 14000 rows.

Change the approach by loading into dataset and then update that dataset using DataAdapter. It's flying, only 2 seconds to insert the same amount of rows

Thu Oct 19, 04:25:00 AM EDT  

Blogger Phil said....

"wanted to not use a single SQL statement for processing"

Okay, but perhaps this is a question of incompletely stated requirements? My example below is probably *not* part of the data warehouse example above, but it is a reason why a 'boss' might make this a requirement.

"This process is expected to take 3 hours. In the event of a failure at the 2 hour 55 minute mark, our service level agreement would not permit us to truncate and start the statement again. Processing needs to resume at the point of failure."

This is rather difficult to achieve with a single SQL statement, but quite easily achievable with PL/SQL, flashback, and some not-too-difficult bookmarking logic.

Cases of the 'boss' telling 'the developer' how to code are IMHO frequently cases of legitimate business requirements not being understood by the 'boss'...

"When I was a developer that was the rule I was given and it was a good rule."

Understand the 'why'!!!

Thu Oct 19, 04:26:00 AM EDT  

Blogger KristofC said....

It could be worse: it could be written in ABAP (on SAP). I've seen the ABAP kernel destroy otherwise sane code by translating its own SQL dialect into Oracle SQL code.

If you know well SQL and PL/SQL, never program in ABAP, this is an exercise in frustration.

- A frustrated future ex-ABAP developer

Thu Oct 19, 04:36:00 AM EDT  

Blogger SeanMacGC said....

Analytics rock and roll... That is just so absolutely spot on!

The number of Separate-SQL-Code-Blocks & Peformance-Zapping-Hoops that I haven't had to jump through thanks to Analytics, as a result of unbelievably badly designed data structures, runs into the zillions (or maybe it only feels that way!).

Thu Oct 19, 05:16:00 AM EDT  

Blogger Connor McDonald said....

One thing I'd stick before

"You should do it in a single SQL statement if at all possible."

is

"You should do it intelligently"

I've recently had the opposite problem, where the blind faith in "one SQL is best" resulted in things like:

correct version (2 sqls):
insert into T1
select from T
where nice access path 1

insert into T2
select from T
where nice access path 2

was converted into:

insert all
when "predicate1"
then into T1
when "predicate2"
then into T2
select
* from T
(no predicate)

As they say in the classics ... "you can't fix stupid"

Thu Oct 19, 08:44:00 AM EDT  

Anonymous Anonymous said....

6) should be:
If you cannot do it in an external C routine and you don't know, what you are want to do, do it in Mysql.

Thu Oct 19, 10:06:00 AM EDT  

Blogger Stephen Booth said....

wanted to not use a single SQL statement for processing

I was recently shown a new database security standard (written by a consultant who has more letters, due to certification (you know the sort MCSE OCP CNA RHCE CCNA), after his name than in it) with a similar statement in. Thing is he meant not use SQL at all. He stated that SQL should not be used to access databases on the grounds that this opened up the possibility of SQL-Injection attacks.

I've asked what we should use instead but have yet to recieve an answer.

Thu Oct 19, 10:35:00 AM EDT  

Blogger SeanMacGC said....

Not so long ago, I did a stint for a major US investments house, with a base here in Ireland, where I wanted to speed up a particularly long-running procedure.

Simple really, just had to remove the cursor-loop within another cursor-loop within yet another cursor-loop within yet another cursor-loop… and convert to a single SQL block. Only ‘drawback’ – this necessitated an increase in the (relatively modestly sized) temporary tablespace, to accommodate the consequent sorts. I was informed, however, by the DBA people in the US that they could not increase the temporary tablespace size, because: ‘that would be too expensive’, and that I would have to ‘break the statement up’. Too expensive!! ‘How’, asked I, ‘Well’, said they, that the cost of the back-ups would increase proportionately, since there were now more data to back up!... Yep. They were backing up temporary data; and from what I could gather these individuals were fairly highly regarded for their technical nous.

Not a company with whom I should ever be inclined to invest.

Thu Oct 19, 11:11:00 AM EDT  

Anonymous Gabe said....

Why on Earth would a boss tell their developer how exactly to process data?

Maybe they find “programmers” easier to manage than “developers”?


"You should do it in a single SQL statement if at all possible."

Table with multiple hierarchies, each one identified by a “thing_type”:

for crs in (select thing
from src
start with parent_thing is null and thing_type = 1
connect by parent_thing = prior thing
)
loop
insert into tgt (c1) values (crs.thing);
end loop;

could be way faster than this one statement producing exactly the same result:

insert into tgt (c1)
select thing
from src
start with parent_thing is null where thing_type = 1
connect by parent_thing = prior thing

So yes, not just any SQL statement is better than an _equivalent_ PL/SQL.


[Ok, I admit that wasn’t a fair comparison :]

Thu Oct 19, 02:03:00 PM EDT  

Blogger Thomas Kyte said....

Gabe,

not only was it "not fair", I don't believe it to be true.

They both execute the same connect by. Why do you think slow by slow would best bulk processing.

Thu Oct 19, 02:07:00 PM EDT  

Anonymous Anonymous said....

I too have encountered this many times. The president of a small services company gave me the rest of the day off for fixing one of their performance problem with one SQL statement in a trigger. It took their former contractor 2 weeks to create the problem with 600 lines of VB code; and they could only run the program once a week.
The president said "how did you know how to fix it like that when it took the other guy two weeks?".
The answer: something to the effect of "Set processing vs. row at a time processing" ... as others have echoed.

Thu Oct 19, 02:35:00 PM EDT  

Anonymous Gabe said....

start with parent_thing is null AND thing_type = 1

start with parent_thing is null WHERE thing_type = 1

Deceivingly close ... I know :) … back to “not fair”,I guess?

Thu Oct 19, 03:38:00 PM EDT  

Anonymous Anonymous said....

Tom,

When you go to see customers how long are you there? Do you do short term engagements or do you ever work with a client for an extended period of time?

in case we ever wanted to bring you in... could we get you for a few months?

Thu Oct 19, 03:49:00 PM EDT  

Anonymous Gabe said....

And it should have been ...

where thing_type = 1
start with parent_thing is null
connect by parent_thing = prior thing

Thu Oct 19, 03:59:00 PM EDT  

Anonymous Robert said....

Tom you should consider trademark on some of your phrases like this guy

Thu Oct 19, 04:06:00 PM EDT  

Blogger Thomas Kyte said....

Gabe -

Ok, I'll go with "just not fair" now :)

could we get you for a few months?

no, my very very short attention span would fry way before that time period expired :)

Days - for customers in my division, having a problem doing an implementation - that does happen.

Thu Oct 19, 05:33:00 PM EDT  

Blogger Gary Myers said....

"We have a new "overly-engineered" system at my work where "EXECUTE IMMEDIATE" was the cool way to do things. "

Probably Chinese Whispers from Oracle 8i where some of the sexy stuff like Analytics could only be done by dynamic sql.
So, using 8i, Fred tells Bill "We can do this faster with Analytics, but need to use Execute Immediate"
Bill tells Mike "We made it faster using Execute Immediate"
Mike tells Sam "Execute Immediate is faster"
....

Its not enough to teach someone that "X" is better. They need to understand WHY

Fri Oct 20, 03:10:00 AM EDT  

Blogger Thomas Kyte said....

Its not enough to teach someone that "X" is better. They need to understand WHY

beautiful...

Fri Oct 20, 03:33:00 AM EDT  

Anonymous Carl said....

Re trademarking your phrases, I'd buy a 'Do it in SQL' t-shirt and a 'Bind Variables!' mug!

Fri Oct 20, 05:32:00 AM EDT  

Anonymous Sautéd In Wrong Sauce said....

I work at a company where slow-by-slow seems to be the de facto technique. Set-orientated approaches are not used.

VB is used on the application in question. Instead of using SQL that incorporates joins, a record set is first populated using a simple select against a single table. Next, each record within the record set is used as the predicate for another select against another table using SQL that is built on-the-fly in VB (using literal values: no bind variables!) to populate another record set. This can carry on to several layers deep.
What we have here is “joins outside the database” using a nested loop style. To make matters even worse, typically all columns are selected, even if a single column or subset of columns are actually required. Even worse, sometimes rows are selected and fetched to the client only to be filtered out by the application (i.e. a WHERE clause could have been used but wasn’t)

Putting the following aside: the inefficiencies of too much SQL being executed (hard-parsed to boot); the thousands of network round trips that are typically involved; and the unnecessary complexity of the code: the transaction isolation mode used is READ COMMITTED so there is a chance that the view of data at the client will be corrupt because it is built from selects that are not read consistent to a single point in time

I have tried to explain to the developers (or should that be programmers) responsible for the code that this is wrong on many levels: but to no avail.

To Tom - can you back me up here?
To others – can anyone top this lot for worst-case practise?

Fri Oct 20, 10:55:00 AM EDT  

Blogger Thomas Kyte said....

To Tom - can you back me up here?


any of my books - i go into this over and over again.

Effective Oracle by Design would probably be the most applicable, an entire book dealing with this very topic pretty much.

Fri Oct 20, 11:02:00 AM EDT  

Blogger SeanMacGC said....

To others – can anyone top this lot for worst-case practise?

Hmmm... external cursor-loops (effectively), with added network latencies... not easy to top! :o)

Fri Oct 20, 11:30:00 AM EDT  

Anonymous Leo Mannhart said....

To others – can anyone top this lot for worst-case practise?

... hmmm, let's see...
architecture standard prohibits the use of pl/sql for business logic and requires a web application to read through a view (ok, no problem here) and the view is accessed via a java data mapper (still no problem). now it could happen that sometimes the view should contain some group by's and the like and one wanted to limit it in a certain (time-)range. Would be easy with a cursor. But a cursor that would be pl/sql and that is evil so the application ends with selecting all the needed rows, transfering them to the applicaion server and counting and grouping them there... so it ends up as with the loop of loops
(needless to say that with this mapping tool it is easier to delete all records the user has accessed or changed in the page and insert all records new from scratch for this page ... and wondering why it takes 3 seconds to do this slow by slow for 100 records while it took only a few millisecs before, when they only updated the changed records. but hey, this compare thing is not that easy as just delete everything with this key and insert it all again ;-) )

no comes the next level:
java is so outstanding and pl/sql is so evil; can you guess how the batch programs for loading data are programmed? yes, the batch job is using the data mapper from the application server and inserting one row at a time slow by slow into the database ... and of course a migration is also done in this way (selecting and updating slow by slow) and then always this bloody ora-1555 ... ohh and this migration is a little bit more complex, should update 1.5 mill rows. but why the hell after 9 hours there are only 140'000 records finished? this would take days to finish and the maintenance window is only 6 hours ...

oh yes, a lot of very funny things are going on out there in the wild...

Fri Oct 20, 04:25:00 PM EDT  

Anonymous Sautéd In Wrong Sauce said....

Effective Oracle by Design would probably be the most applicable, an entire book dealing with this very topic pretty much.

The Software Team Leader has this title on his desk, on my recommendation. He claims to have read it but it still has no creases in its spine: unlike the myriad of other titles alongside it with titles starting with Agile..., Extreme... and Rapid...

I think that the problem is that SQL is, to him, not sexy and too 1970's, regardless of some of the obvious benefits over slow-by-slow techniques: e.g. superior performance and improved readability/maintainability.

Its very difficult to convert the unconvertable, but I guess it's not impossible. Maybe it's time to start a campaign against slow-by-slow. How about "Slow-by-Slow? No! No! No!" as a slogan?

Thanks for your great work, Tom.

Fri Oct 20, 06:36:00 PM EDT  

Anonymous Benjamin said....

I am a beginner.
Thanks for your mantra!

Sat Oct 21, 01:19:00 AM EDT  

Anonymous Matt said....

Apologies for the delayed comment. Regarding your mantra, one thing I've wondered is when it becomes too much to do in a single SQL statement.

For example, I just wrote a stored procedure that processes attachments on a document. It's a single insert statement which does much more than a direct "put this input data into that table". It consists of quite a bit of input parsing, record qualification, conditional decision making, etc. and then inserts the results of that.

The biggest problem seems to be that my developers choke on it, which I don't blame them. The second biggest problem is that the decision making is something that also applies elsewhere, so I think I want to widgetize it. I'm considering re-writing the query to be more procedural - still set based but in steps (bulk pre-qualify, bulk parse input, bulk separate into input "types", branch to a different bulk process routine per type, bulk extract results, etc.) Or even "slow-by-slow". I'm hoping either is more approachable.

So I'm wondering if you have any rules of thumb for your mantra that say, "Yes, you could do it at this level but it would be forcing it. Bump down to the next level." I never see any caveats posted alongside the mantra.

Tue Oct 31, 11:43:00 AM EST  

Blogger Thomas Kyte said....

do you have a detailed comment that describes precisely what the SQL is doing?

I'd choke on your big bit of procedural code AS WELL AS your big ol' sql query.

without a comment/documentation detailing what it does, how it does it and why.

So, unless the decision making is something really re-usable and general purpose, I'm a fan of stuffing it in there.

Approachable code exists in all languages, it comes down to documentation.

Look at this original blog entry - that "procedural" code was not even understood by the people that owned it - I had to explain to them what it was doing - and they said I got it wrong.

You can write nasty non-understandable stuff in every language.

But if you document it, it becomes understandable.

You can take some of my really really large queries and understand them. Proper use of indenting - using inline views to "layer" it - using subquery "WITH" factoring to modularize it.

coupled with copious explanation of how it works....

Tue Oct 31, 11:55:00 AM EST  

Anonymous Matt said....

By and large, I think we're pretty good about formatting and WITH-level modularization. I agree it does make a huge difference in communicating what's going on. Documentation sometimes gets the cut because it's often stuff like:

-- get all my documents
open pDocs for
select *
from documents
where user = pMyUserId;

That would normally get the axe. But we often wind up a bit lean on explanation, so yours is a fair point.

Most of what they're choking on, however, are simply features they've never seen before. The query uses analytics, multi-table insert, regexps, and case-when. The comments I got were "if this was all withs-and-selects, I could figure it out". While I'm OK with commenting on why we're using a feature, I'm admittedly resistant to explain how (say) analytics process rows right in code comments. Maybe the proper approach to "documenting" this would be to find/write some project-pertinent tutorials for my team with referenecs to the documentation.

As for my question generally, it sounds like the single SQL statement mantra is very much about reducing the number of times the data is handled, which means handling all the data and (as it makes sense) multiple operations together, even if it means very long SQL. I'm OK with that principle. I realize I usually thought of my SQL as essentially "a statement" rather than "a collection of operations" or a function itself.

Tue Oct 31, 01:10:00 PM EST  

Blogger Thomas Kyte said....

if your developers are afraid of analytics.... they are missing the coolest thing to happen to sql since the keyword select

if your developers are afraid of a case statement, then they are quite simply not developers (how is that scary???)

if your developers are afraid to exploit SQL in the same fashion they do java, plsql, C, VB, whatever - well - I haven't much respect for them.

I've never understand the willingness to cut SQL off at the knees - but not any other language.

Tue Oct 31, 01:39:00 PM EST  

Anonymous Anonymous said....

Tom,

Your order:

You should do it in a single SQL statement if at all possible.
If you cannot do it in a single SQL Statement, then do it in PL/SQL.
If you cannot do it in PL/SQL, try a Java Stored Procedure.
If you cannot do it in Java, do it in a C external procedure.
...

I wonder why 'C' is not before 'Java' in the list! Any specific reason? Which is most appropriate to use when?

UM

Mon Nov 13, 11:34:00 AM EST  

Anonymous Anonymous said....

>I wonder why 'C' is not before 'Java' in the list! Any specific reason? Which is most appropriate to use when?
>UM


I think because the C code maybe runs over SQL*NET (client assumed) and requires more processing/resources than the inherent extra overhead of JAVA. But the JAVA has the advantage of being internalized to the DB and having a faster development cycle.


Now speaking as one of those Hardware RAC suppliers, we tend to sell to sites that have full time DBA(s). We do NOT try to second guess the DBA and check their schema/code for being optimized.
Our teams work on the assumption that it is the Hardware/OS that is unable to keep up with client growth. And that the resident DBA has done due diligence.

re: RAC solutions to slow code
1. Yes it is what the vendor sells.
2. Rarely does a site (customer) pay a hardware vendor Oracle Team for a time and materials review of their entire production environment.
3. I like Oracle RAC and most production Oracle sites really do need a HA setup. 24/7 is the rule nowadays.

Therel Moore
Austin, TX

Tue Nov 28, 08:57:00 AM EST  

Anonymous Loïc said....

To others – can anyone top this lot for worst-case practise?

I work on a project which claims that "if you use massively parallelized processing, your work will be done faster".

Since this sentence has been said 6 years ago on the project, every fundamental flows (batches, file loading and processing...) have been developed to work like this. Hence, the system works in OLTP mode all the time.

In production since two years know with only 5% of the transactions, the system works fine.

Now, we must handle 100% of the transactions until end of the year so we have worked since one year on tuning queries, adding indexes, modifying initrans, freelists and freelist groups, configuring second level cache (Hibernate)...

And our last performance test campaign ended 2 monthes ago.

The result in not only loading a file slow-by-slow but massively (=multi-threaded) slow-by-slow using an "event" table (sort of home made Advanced Queuing but with polling on one table containing a Blob with serialized Java) is astonishing:

Given a file of 40,000 swift-like messages, loading the file into business tables handling duplicate messages, and generating a lot of other things required:
- 7,000,000+ queries (insert/select/update/delete)
- 9Gb of data transfered into the database (which is 7Gb data + indexes)
- 6.5Gb of data resent to the application servers
- 10Gb of redo logs then archive logs
- 3+ hours to dequeue the events generated during the file loading

And you know what, the file is only 40Mb!

Currently we are investigating bulk loading + external table but we are facing The sentence again: we must use multi-threading everywhere!

Does anybody know internet benchmarks comparing slow-by-slow (multi-threaded or not) versus external table + SQL on set of data loading?

Hope this helps ^_^;

Wed May 30, 04:25:00 PM EDT  

POST A COMMENT

<< Home