BULK COLLECT এবং FORALL ব্যবহারে Performance Optimization

PL/SQL-এ BULK COLLECT এবং FORALL দুটি খুবই শক্তিশালী ফিচার যা কোডের পারফরম্যান্স অনেক উন্নত করতে সাহায্য করে, বিশেষ করে যখন আপনি অনেক ডেটা প্রসেস করছেন। এই দুটি ফিচার একসাথে ব্যবহার করলে বড় বড় ডেটা অপারেশনগুলো দ্রুত এবং কার্যকরীভাবে সম্পন্ন করা যায়।

BULK COLLECT

BULK COLLECT একটি PL/SQL অপারেটর যা ডেটাবেস থেকে একযোগে বড় পরিমাণের ডেটা সংগ্রহ করতে ব্যবহৃত হয়। এটি SELECT INTO স্টেটমেন্টের সাথে কাজ করে, কিন্তু সাধারণ SELECT INTO স্টেটমেন্টের তুলনায় এটি অনেক দ্রুত কারণ এটি একাধিক রেকর্ড একসাথে সংগ্রহ করতে সক্ষম। সাধারণভাবে, একটি SELECT INTO কেবল একটি একক রেকর্ড ফিরিয়ে আনে, তবে BULK COLLECT একাধিক রেকর্ড একই সময়ে ফিরিয়ে আনতে পারে।

BULK COLLECT এর সুবিধা:

• Performance Improvement: একাধিক রেকর্ড একসাথে ফেচ করার কারণে নেটওয়ার্ক কনজেশন এবং কমিউনিকেশন ওভারহেড কমে যায়।
• Memory Efficiency: এটি কম মেমরি ব্যবহার করে দ্রুত ডেটা প্রসেস করতে সক্ষম।

BULK COLLECT Syntax:

DECLARE
    TYPE emp_table IS TABLE OF employees%ROWTYPE;
    emp_data emp_table;
BEGIN
    SELECT employee_id, first_name, last_name
    BULK COLLECT INTO emp_data
    FROM employees
    WHERE department_id = 10;

    FOR i IN 1..emp_data.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_id || ' ' || emp_data(i).first_name);
    END LOOP;
END;

এখানে:

• BULK COLLECT INTO ব্যবহার করে সমস্ত ডেটা একসাথে সংগ্রহ করা হচ্ছে।
• emp_data হল একটি PL/SQL collection (এটি একটি associative array বা nested table হতে পারে) যা সমস্ত রেকর্ড ধারণ করে।

FORALL

FORALL একটি PL/SQL ডেটাবেস ফিচার যা INSERT, UPDATE, DELETE বা MERGE অপারেশনগুলোকে একসাথে একাধিক রেকর্ডে কার্যকর করতে ব্যবহৃত হয়। সাধারণভাবে, প্রতিটি রেকর্ডের জন্য আলাদা আলাদা SQL স্টেটমেন্ট চালানো হলে, এতে পারফরম্যান্স হ্রাস পায়, তবে FORALL ব্যবহার করে একাধিক রেকর্ডের ওপর একই SQL স্টেটমেন্ট একসাথে কার্যকর করা যায়।

FORALL এর সুবিধা:

• Performance Boost: একাধিক SQL স্টেটমেন্ট একসাথে চালানোর ফলে ডেটাবেসে কম round trips ঘটে, যার ফলে কার্যক্রম দ্রুত হয়।
• Reduced Context Switching: প্রতিটি SQL স্টেটমেন্টের জন্য context switching কম হয়, তাই ইকোনমিকাল হয়।

FORALL Syntax:

DECLARE
    TYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;
    emp_ids emp_id_array := emp_id_array(101, 102, 103);
BEGIN
    FORALL i IN 1..emp_ids.COUNT
        UPDATE employees
        SET salary = salary * 1.10
        WHERE employee_id = emp_ids(i);
    COMMIT;
END;

এখানে:

• FORALL ব্যবহার করা হয়েছে UPDATE স্টেটমেন্টের জন্য, যাতে একসাথে একাধিক employee_id এর জন্য salary আপডেট করা যায়।
• emp_ids হল একটি PL/SQL collection যা সমস্ত employee_id ধারণ করে।

BULK COLLECT এবং FORALL এর যৌথ ব্যবহার

যখন আপনি একসাথে BULK COLLECT এবং FORALL ব্যবহার করেন, তখন আপনি প্রথমে ডেটা একটি PL/SQL collection এ সংগ্রহ করেন এবং পরে সেই collection এর ওপর একাধিক SQL অপারেশন একসাথে কার্যকর করেন। এই দুইটি ফিচার একসাথে ব্যবহার করলে পারফরম্যান্স অনেক বৃদ্ধি পায়, বিশেষ করে বড় ডেটাসেটের ক্ষেত্রে।

BULK COLLECT এবং FORALL এর যৌথ ব্যবহার উদাহরণ:

DECLARE
    TYPE emp_table IS TABLE OF employees%ROWTYPE;
    emp_data emp_table;
BEGIN
    -- Bulk Collect data from database into PL/SQL collection
    SELECT employee_id, salary
    BULK COLLECT INTO emp_data
    FROM employees
    WHERE department_id = 10;

    -- Use FORALL to update salaries
    FORALL i IN 1..emp_data.COUNT
        UPDATE employees
        SET salary = emp_data(i).salary * 1.10
        WHERE employee_id = emp_data(i).employee_id;

    COMMIT;
END;

এখানে:

• প্রথমে BULK COLLECT INTO ব্যবহার করে ডেটাবেস থেকে সমস্ত employee_id এবং salary সংগ্রহ করা হয়েছে।
• তারপর FORALL ব্যবহার করে salary এর মান আপডেট করা হয়েছে।

Performance Optimization Tips with BULK COLLECT and FORALL

1. LIMIT Clause in BULK COLLECT: যদি বড় পরিমাণ ডেটা একসাথে সংগ্রহ করতে হয়, তবে আপনি LIMIT ক্লজ ব্যবহার করতে পারেন। এটি memory-এ ডেটার পরিমাণ সীমিত করে দেয়, যাতে মেমরি overflow না ঘটে।

   DECLARE
       TYPE emp_table IS TABLE OF employees%ROWTYPE;
       emp_data emp_table;
       CURSOR c_emp IS SELECT employee_id, salary FROM employees WHERE department_id = 10;
   BEGIN
       OPEN c_emp;
       LOOP
           FETCH c_emp BULK COLLECT INTO emp_data LIMIT 1000;
           EXIT WHEN emp_data.COUNT = 0;
           -- Process the collected data here
           FORALL i IN 1..emp_data.COUNT
               UPDATE employees
               SET salary = emp_data(i).salary * 1.10
               WHERE employee_id = emp_data(i).employee_id;
       END LOOP;
       CLOSE c_emp;
       COMMIT;
   END;
   

2. Avoid COMMIT inside FORALL: FORALL স্টেটমেন্টের মধ্যে COMMIT করার চেষ্টা করবেন না, কারণ এতে পারফরম্যান্স কমে যায়। সেগুলো একসাথে শেষ হলে একটিই COMMIT ব্যবহার করুন।
3. Use BULK COLLECT with WHERE Clause: ডেটার পরিমাণ কমানোর জন্য WHERE ক্লজ ব্যবহার করে প্রয়োজনীয় ডেটা সংগ্রহ করুন। এটি unnecessary ডেটার লোড কমাবে।
4. Indexing: যদি আপনি বড় পরিমাণে ডেটা আপডেট বা ইনসার্ট করছেন, তবে ইনডেক্স ব্যবহার করে কুয়েরি অপটিমাইজ করুন।

Conclusion

BULK COLLECT এবং FORALL ব্যবহার করলে PL/SQL-এ ডেটা প্রক্রিয়াকরণের গতি উল্লেখযোগ্যভাবে বৃদ্ধি পায়। যখন আপনি একাধিক রেকর্ডে একই ধরণের অপারেশন করতে চান, তখন এই দুটি ফিচার ব্যবহৃত হলে আপনি Memory Efficiency, Performance Boost, এবং Reduced I/O সুবিধা পাবেন।