Using stored procedures for inserts/updates

Printable View

Show 40 post(s) from this thread on one page

12-20-2001, 04:23 PM
TimHall

Don't worry, I wasn't taking it personally (best call off the hitman :) ).

A couple of people mentioned performance. Take a look at the example below. I ran the two loops a few times and these results seem pretty consistent on my machine:

This example is worh looking at:

CREATE TABLE table1
(id NUMBER(10),
col1 VARCHAR2(50),
col2 VARCHAR2(50));

CREATE OR REPLACE PROCEDURE Table1_Insert
(p_id IN table1.id%TYPE,
p_col1 IN table1.col1%TYPE,
p_col2 IN table1.col2%TYPE) IS
BEGIN
INSERT INTO table1
(id, col1, col2)
VALUES
(p_id, p_col1, p_col2);
END;
/

SET TIMING ON

SQL> DECLARE
2 v_col1 table1.col1%TYPE := '1234567890';
3 v_col2 table1.col2%TYPE := '12345678901234567890';
4 BEGIN
5 FOR i IN 1 .. 10000 LOOP
6 Table1_Insert(i, v_col1, v_col2);
7 END LOOP;
8 END;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.02
SQL>
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.05
SQL>
SQL> DECLARE
2 v_col1 table1.col1%TYPE := '1234567890';
3 v_col2 table1.col2%TYPE := '12345678901234567890';
4 BEGIN
5 FOR i IN 1 .. 10000 LOOP
6 INSERT INTO table1
7 (id, col1, col2)
8 VALUES
9 (i, v_col1, v_col2);
10 END LOOP;
11 END;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.08
SQL>
SQL> ROLLBACK;

I know it's a crap test, but it's interesting.
12-21-2001, 10:50 AM
chrisrlong

Interesting test, but it simply highlights how easy it is to write ineffecient code in *any* language.

*If* one actually wanted to do what you are proposing, the most efficient solution would be:

Code:

PROCEDURE xTEST IS TYPE ID_T IS TABLE OF CRL_Table1.ID%TYPE INDEX BY BINARY_INTEGER; TYPE Col1_T IS TABLE OF CRL_Table1.Col1%TYPE INDEX BY BINARY_INTEGER; TYPE Col2_T IS TABLE OF CRL_Table1.Col2%TYPE INDEX BY BINARY_INTEGER; l_ID_T ID_T ; l_Col1_T Col1_T; l_Col2_T Col2_T; l_Col1 CRL_Table1.col1%TYPE := '1234567890'; l_Col2 CRL_Table1.col2%TYPE := '12345678901234567890'; BEGIN FOR i IN 1 .. 10000 LOOP l_ID_T (i) := i ; l_Col1_T (i) := l_Col1; l_Col2_T (i) := l_Col2; END LOOP; FORALL i IN 1..l_ID_T.COUNT INSERT INTO CRL_Table1 ( ID , Col1 , Col2 ) VALUES ( l_ID_T (i), l_Col1_T (i), l_Col2_T (i) ); END;

(As always, a 3-character tab makes everything line up beautifully:))

This executes in less than a second, at least on my server.

As I said, PL/SQL has the tools to greatly reduce, or in this case, eliminate the context switches.

- Chris
12-21-2001, 11:41 AM
TimHall

I think you've missed my point, or I've not made my point clear :).

I wasn't describing an ideal bulk load. It was just a way to show that 10,000 calls to a procedure doing the insert takes more processing than 10,000 calls using SQL alone. In this context the bulk load is not relevant.

A better example would have been 10,000 people requesting a single insert from a web page simultaneously. The procedure approach does take fractionally more time and resources. Not noticable on a single hit, but still real and affecting potential scalability???

That's not a reason for not using APIs. I agree there are a number of benefits but, there are certain overheads.
12-21-2001, 11:49 AM
TimHall

Quote:

[i]Originally posted by chrisrlong
(As always, a 3-character tab makes everything line up beautifully:))

- Chris [/B]

Don't talk to me about formatting. IE6 centers everything on this forum. Some dodgy HTML in the forum or code in IE6 :)
12-21-2001, 12:07 PM
TimHall

I found the quote I was looking for by Tom Kyte:

I actually list this in my book in order:

i) sql
ii) plsql
iii) java stored procedure
iv) c

sql = fastest, easiest, least amount of code to maintain
plsql = best integration with sql, fast, easy to code, best in the database
java = do all of the things plsql cannot do (eg: send an email, write a file)
c = that which java cannot do or java does too slowly.

To be fair, he does love the PL/SQL API programming model in a big way. See the "What you DO GAIN with this approach" section at:

http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:499097979114,%7BPL%2FSQL%7D%20and%20%7BSQL%7D

Cheers :)
12-21-2001, 01:02 PM
chrisrlong

Quote:

Originally posted by TimHall
A better example would have been 10,000 people requesting a single insert from a web page simultaneously. The procedure approach does take fractionally more time and resources. Not noticable on a single hit, but still real and affecting potential scalability???

So I was being somewhat deliberately thick in not addressing your core issue. I apologize. I wanted to show how PL/SQL can be used to solve the 'larger' performance issue of a database.

In this case, as you've pointed out, there *can* be performance impacts, albeit imperceptable at the individual call. So yes, it is conceivable that if everyone writes optimal SQL code, PL/SQL code and middle-tier code, you *can* get slightly better performance by calling the SQL directly.

However, let's step back and look at the larger perspective.

In most development teams, you unfortunately have a bunch of middle-tier code jockeys writing all 3 tiers. To be completely honest with you, most middle-tier programmers have no business writing SQL - they don't do it well enough. So, sooner or later there are performance problems, usually large ones. So, they call in somebody with a clue about SQL to 'fix' things. As such a person, would you like to look through tons of code in VB, C, java or some other middle-tier language? Or maybe all of them at the same time? Or would you rather go straight to the database with complete access to all the SQL and have completely free reign in actually fixing things?

If the SQL exists only in the database, it is much easier for 'real' SQL programmers to fix things, both the big and the small. The gains that can be made in SQL are 'order of magnitude' improvements. For example, the bulk-load I re-wrote above was over an order of magnitude improvement, most likely it was 2 orders of magnitude (<600 msec). *These* types of improvements *far* outweigh the doubling of time that your example showed.

So, in the larger context of database performance, I will go for the PL/SQL abstraction layer almost every single time. Then I will have the freedom to truly fix a broken database or even just to find and fix any and every bad SQL there is. Without an abstraction layer, my job becomes an order of magnitude more difficult, at least.

- Chris
12-21-2001, 01:35 PM
TimHall

I can't argue with that!
12-21-2001, 05:07 PM
jmodic

Quote:

Originally posted by TimHall
Don't talk to me about formatting. IE6 centers everything on this forum. Some dodgy HTML in the forum or code in IE6 :)

When I switched to IE6 I lost a couple of hours trying to find out why all of the sudden this forum has become almost unreadable because of the centered lines, and where the hell in the IE6 settings or in the registry I can changed that damned thing. Needless to say I haven't found that magical switch, argh...
12-21-2001, 05:15 PM
marist89

Quote:

Originally posted by jmodic

Quote:

Originally posted by TimHall
Don't talk to me about formatting. IE6 centers everything on this forum. Some dodgy HTML in the forum or code in IE6 :)

When I switched to IE6 I lost a couple of hours trying to find out why all of the sudden this forum has become almost unreadable because of the centered lines, and where the hell in the IE6 settings or in the registry I can changed that damned thing. Needless to say I haven't found that magical switch, argh...

After futzing with it for a couple hours myself, I found the perfect fix for IE6 and this forum. ( http://home.netscape.com/computing/d...html?cp=hophb2 ) http://www.theunholytrinity.org/crac...b/anym/win.gif
12-21-2001, 05:23 PM
jmodic

Yep, that patch fixes the mentioned bug...

Show 40 post(s) from this thread on one page