Optimasi menggunakan FOR ALL di ORACLE

Seorang programmer harus pintar-pintar mengakali cara mana yang akan digunakan untuk mencapai suatu tujuan. Misalkan , ada 4 cara yaitu cara A, B , C , atau D. Namun dari ke empat cara tersebut makakah yang paling optimize, makakah yang paling sesuai dengan kebutuhan untuk aplikasi yang akan/sedang kita bangun.

Banyak faktor yang mempengaruhi kecepatan dari sebuah program. Dari segi hardware, network, database (query yang kurang efisien), code yang memang tidak efisien, dll.

Disini kita bisa tarik sebuah contoh sederhana, yaitu bagaimana untuk mengupload sejumlah data dengan menggunakan sebuah aplkasi berbasis web dengan bahasa.Net (C# ) dan oracle.

1. Looping on code

try
{
using (System.IO.StreamReader srd = new System.IO.StreamReader(filePath + FileUploads.FileName))
{
// for connection open
oraConnect.Open();
while (srd.Peek() >= 0) // end of file -1
{
strData = srd.ReadLine();
if (strData != string.Empty)
{
//Call stored procedure to insert value one by one
count_row++;
}
}
// for connection close
oraConnect.Close();
srd.Close();
}
catch (Exception ex)
{
lblerror.Text = "error on uploadData() : " + ex.Message;
}

2. Call stored procedure and do looping on query use statement FOR


CREATE OR REPLACE PROCEDURE P_UPLOAD_FOR
(P_TOTNOREK IN INTEGER, P_LISTNOREK IN VARCHAR, P_CREATED_BY IN INTEGER)
IS
TYPE TAB_NOREK IS TABLE OF T_TEMP.NOREK%TYPE INDEX BY PLS_INTEGER;
NOREK             TAB_NOREK;
startTime       INTEGER;
endTime         INTEGER;
BEGIN
startTime := dbms_utility.get_time;
FOR j IN 1..P_TOTNOREK LOOP -- load index-by tables
NOREK(j) := F_split_char(P_LISTNOREK, J,',');
END LOOP;
FOR i IN 1..P_TOTNOREK LOOP -- use FOR loop
INSERT INTO T_TEMP VALUES (NOREK(i), sysdate, P_CREATED_BY);
END LOOP;
endTime := dbms_utility.get_time;
dbms_output.put_line('Execution Time (secs): ' || TO_CHAR((endTime - startTime)/100));
COMMIT;
END P_UPLOAD_FOR;
/

3. Call stored procedure and do looping on query use statement FOR ALL

CREATE OR REPLACE PROCEDURE P_UPLOAD_FORALL
(P_TOTNOREK IN INTEGER, P_LISTNOREK IN VARCHAR, P_CREATED_BY IN INTEGER)
IS
TYPE TAB_NOREK IS TABLE OF T_TEMP.NOREK%TYPE INDEX BY PLS_INTEGER;
NOREK             TAB_NOREK;
startTime       INTEGER;
endTime         INTEGER;
BEGIN
startTime := <strong><em>dbms_utility.get_time</em></strong>;
FOR j IN 1..P_TOTNOREK LOOP -- load index-by tables
NOREK(j) := F_split_char(P_LISTNOREK, J,',');
END LOOP;
FORALL i IN 1 .. P_TOTNOREK -- use FORALL statement
INSERT INTO T_TEMP VALUES (NOREK(i), sysdate, P_CREATED_BY);
endTime := dbms_utility.get_time;
dbms_output.put_line('Execution Time (secs): ' || TO_CHAR((endTime - startTime)/100));
COMMIT;
END P_UPLOAD_FORALL;
/

4. Call stored procedure and do looping on query use string builder for bulk of statement.

CREATE OR REPLACE PROCEDURE P_INSERT_UPLOAD_STRBLDR(P_TOTNOREK IN INTEGER, P_LISTNOREK IN VARCHAR, P_CREATED_BY IN INTEGER)
IS
tmpVar NUMBER;
a string(10000);
b string(10000);
c int(4);
d string (100);
e varchar2(4000);
f int :=10;
startTime int;
endTime int;
BEGIN
tmpVar := 0;
startTime := dbms_utility.get_time;
b:=P_LISTNOREK;
e :='insert all';
for i in 1..P_TOTNOREK loop
select REGEXP_SUBSTR(b,'[^,]+') into a from dual;
select instr(b,',',1,1)+1 into c from dual;
select substr(b,c) into d from dual;
e :=e||' into T_TEMP( NOREK ,CREATE_DATE,CREATE_BY) values('||a||',sysdate,'||f||')';
b:=d;
end loop;
e :=e||' select * from dual ';
dbms_output.put_line(e);
EXECUTE IMMEDIATE e;
COMMIT;
endTime := dbms_utility.get_time;
dbms_output.put_line('Execution Time (secs): ' || TO_CHAR((endTime - startTime)/100));
END P_INSERT_UPLOAD_STRBLDR;
/

Dan dapat dibuktikan mana yang lebih cepat. Dengan menggunakan cara yang ke 3. Saya upload data 20ribu data tidak sampai 1 detik. :D. Tapi kalau ternyata ada cara yang lebih cepat atau lebih simple atau lebih optimize, monggo di share. Maklum saya juga newbie 🙂

NB: Optimasi disini adalah bernilai relative. Tergantung dari jenis aplikasi dan fungsi dari masing-masing modul yang akan dibuat. Jika berbicara optimasi, tidak berarti kita menitikberatkan pemrograman di database. Karena jika dititikberatkan seluruhnya ke sisi database, dengan tujuan proses akan cepat di UI client tapi sever yang bekerja keras. Impactnya akan membuat proses-proses yang mengakses ke database akan menjadi lama, bahkan sangat lama. Intinya sesuaikan dengan kebutuhan  anda 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s