TheHingineer

The Hingineer

Menu

The Hingineer

Menu

  • DBMS

  • DBMS Part-1

  • DBMS Part-2

  • DBMS Part-3

  • DBMS Part-4

  • DBMS Part-5

    •  Query Optimization Methods in DBMS 

    Jab hum SQL query likhte hain, toh us query ko chalane ke alag-alag tareeke (plans) ho sakte hain. DBMS ka kaam hota hai ki sabse fast aur sasta tareeka choose kare — is process ko Query Optimization kehte hain.

    Query ko optimize karne ke 2 main methods hote hain:

    1. Heuristic-Based Optimization

    2. Cost-Based Optimization

    3.  

     1. Heuristic-Based Optimization

     Yeh kya hota hai?

    “Heuristics” ka matlab hota hai rules ya shortcuts. Is method mein DBMS kuch fixed rules follow karta hai query ko optimize karne ke liye — bina actual cost calculate kiye.

     Goal:

    Intermediate results (beech ke data) ko kam karna aur query fast banana.

     Common Heuristic Rules 

    Rule No.Rule
    1SELECT ko jitni jaldi ho sake apply karo
    2PROJECT (columns choose karna) bhi early apply karo
    3Multiple SELECTs ko combine karo
    4SELECT + JOIN ko combine karo
    5JOIN ko Cartesian Product se pehle karo

     Example Query

    SELECT name
    FROM Students, Departments
    WHERE Students.dept_id = Departments.id AND Departments.name = 'CSE';
     

    Relational Algebra:

    π_name (σ_Students.dept_id = Departments.id AND Departments.name = 'CSE' (Students × Departments))
     

     Heuristics Apply karte hain

    Step by step SELECT ko upar le jaate hain:

    π_name (Students ⨝_Students.dept_id = Departments.id (σ_Departments.name = 'CSE' (Departments)))
     

    Pehle Departments me se sirf “CSE” wale select kiye, fir join kara — to size kam ho gaya.

     Benefits:

    • Simple aur fast

    • Query size chhoti ho jaati hai

    • Bina statistics ke kaam chalta hai

     Limitations:

    • Har baar best plan nahi milta

    • Cost (time, memory) ka estimate nahi karta

     Query Tree Diagram (Heuristics se pehle aur baad me)

     Pehle (without rules):

          π_name
            |
          σ_condition
            |
    Students × Departments
     

     Baad me (rules apply karke):

           π_name
             |
    Students ⨝ Departments
             |
    σ_Departments.name = 'CSE'
     

     2. Cost-Based Optimization

     Yeh kya hota hai?

    Is method me DBMS query ke multiple plans banata hai, aur har plan ka cost estimate karta hai (kitna data process hoga, kitni I/O lagegi, etc.). Fir jo sabse sasta (lowest cost) hota hai, usse choose karta hai.

     Goal:

    Jo plan sabse efficient aur fast ho, usko run karna.

     Example

    SELECT *
    FROM Orders, Customers
    WHERE Orders.customer_id = Customers.id AND Orders.amount > 100;
     

    DBMS ke paas 2 options ho sakte hain:

    PlanDescriptionEstimated Cost
    APehle Orders pe filter, fir join500
    BPehle join karo, fir filter lagao1000

    Yahan DBMS Plan A choose karega — kyunki cost kam hai.

     Kaunse Cheezein Cost Decide karti hain?

    • Table me kitni rows hain (cardinality)

    • Index hai ya nahi

    • Filter condition kitna narrow hai

    • Disk I/O lagega ya memory

    • Sorting lag rahi hai ya nahi

     Query Plan Diagrams

     Plan A (Best Plan):

    Filter Orders.amount > 100
            ↓
    Join with Customers
     

     Plan B:

    Orders ⨝ Customers
           ↓
    Filter amount > 100
     

    DBMS dono ka cost estimate karta hai, aur lowest cost wale plan ko choose karta hai.

     Benefits:

    • Accurate aur fast result milta hai

    • Real data ke stats ka use karta hai

    • Jo plan best hoga, wahi chalega

     Limitations:

    • Query optimization thoda slow ho sakta hai

    • Data statistics updated hone chahiye

    • System complex hota hai

     Heuristic vs Cost-Based Optimization: Comparison

    FeatureHeuristic-Based OptimizationCost-Based Optimization
    Kaise kaam karta haiRules ya shortcuts use karta haiReal data ka cost estimate karta hai
    SpeedFast (rules fix hain)Thoda slow (many plan comparisons)
    AccuracyMedium (kabhi best plan nahi milta)High (most efficient plan milta hai)
    Statistics zaroori?NahiHaan
    Complex QueriesNahi handle kartaAchhi tarah handle karta hai

     Conclusion

    Dono methods ka apna importance hai:

    • Heuristic-Based: Jab simple query ho aur jaldi result chahiye

    • Cost-Based: Jab query complex ho aur performance matter karta ho

    Modern DBMS dono ka combination use karte hain:

    • Pehle heuristic rules apply karte hain (basic clean-up)

    • Fir cost-based analysis se best plan choose karte hain

    < Previous
    Next >
    Scroll to Top