There are only 3 ways to Database Performance Tuning.

Database Performance Tuning: Database Design करने के अलावा DBA को एक काम और करना होता है और वह काम होता है Database Performance की Tuning करने का। Database की Performance को ठीक तरह से Tune ना करने पर Database के काम करने की Speed काफी कम हो जाती है। Database की Speed को Tune करने के लिए हमें Database के Design में भी Modification करना पडता है।

लगभग हमेंशा एक DBMS ही User के Commands के आधार पर Database में Data को Store करने या Database से Data को Retrieve करने का काम करता है। जिस तरीके का प्रयोग करके एक DBMS Software किसी User Request को पूरा करने के सभी Data Manipulation Operations को DBMS का Query Optimizer ही Perform करता है। Query Optimizer, DBMS Software का एक एसा हिस्सा होता है, जो किसी Query को Perform करने के लिए Relational Algebra Operation के सबसे Efficient Sequence को तय करने का काम करता है।

हालांकि Query Optimizer के काम करने के तरीके को एक Database Designer किसी भी तरह से Directly Handle नहीं कर सकता है, लेकिन Database के Design में कुछ व्यवस्थाएं करके हम Database की Performance को कुछ हद तक Increase कर सकते हैं।

Indexing

Indexing एक एसा तरीका होता है, जो किसी  Primary Column व  Composite Columns के Data को Access करने का Fast तरीका प्रदान करता है।  क्योकि एक Database Application Use करने वाला User जितने भी Records किसी Table में Add करता जाता है, वे सभी Records Table के अन्त में Random Order में जुडते जाते हैं। जैसे-जैसे किसी Table के Records की संखया बढती जाती है, वैसे-वैसे Table से Sequential Search द्वारा किसी Record के Search होने की Process धीमी होती जाती है। बिना किसी एक अच्छे तरीके को Use किए हुए DBMS किसी Value को Search करने के लिए हमेंशा Sequential तरीके का प्रयोग करता है, जिसमें DBMS किसी Column को Top से Bottom की तरफ Scan करता है। इसलिए Table में Records की संख्या जितनी ज्यादा होती है, Sequential Search की Speed उतनी ही कम होती जाती है। Indexing के Conceptual Operation Diagram को निम्न चित्र में दर्शाया गया हैः

इस चित्र में हम Orders Table के विभिन्न Records का Relation एक Index Table के साथ देख रहे हैं। ये Index हमेंशा Sorted Form में रहता है, इसलिए इस पर विभिन्न प्रकार के अन्य Operations Perform करके एक Database किसी Record को ज्यादा Fastly Search कर सकता है। Index में हर Record के Keys की एक Ordered List होती है, जिसके साथ Order Table का हर Record Associated रहता है। हालांकि Order Table के सभी Records Random Order में हैं, लेकिन Index Table में सभी Records Sorted Order में होने की वजह से Records को Fastly Search किया जा सकता है।

जब एक बार हम Index Create कर देते हैं, उसके बाद जब भी जरूरत होती है, DBMS का Query Optimizer इस Index का प्रयोग करके ही किसी Record को Search करता है। हमें इस Index को दुबारा Access करने की तब तक कोई जरूरत नहीं होती है, जब तक कि हम इस Index को Delete करना नहीं चाहते हैं।

जब हम किसी Table में कोई  Primary Key Create करते हैं, DBMS इस  Primary Key या Foreign Key के Columns के आधार स्वयं ही एक Index Create कर लेता है। जब भी हम किसी Table में कोई नया Record Insert करते हैं, उस Record के Primary Key के मान को Uniqueness के लिए DBMS द्वारा Check किया जाता है। इस Uniqueness के लिए Directly Base Table के Primary Key को Check करने के बजाय DBMS उस Index को Check करता है और चूंकि Index एक Ordered Form में होता है, इसलिए ये Verification काफी तेजी से हो जाता है।

एसा जरूरी नहीं होता है कि DBMS हमेंशा हमारे Primary Key के आधार पर ही Index Create करेगा। वास्तव में हम स्वयं भी हमारी Table के किसी भी Column या Group Of Columns के आधार पर Index Create कर सकते हैं। लेकिन Indexing के साथ कुछ Trade-Offs भी हैं, जो निम्नानुसार हैं:

Indexes Database में Extra Space लेते हैं। चूंकि आज Disk Space ज्यादा महंगा नहीं है, इसलिए Indexes के सम्बंध में आज ये कोई बडी समस्या नहीं है।

जब हम किसी Indexed Column के Record में किसी Data को Insert या Modify करते हैं, या उस Record को Delete करते हैं, DBMS Base Table के साथ ही उस Index को भी Update करता है। इस प्रक्रिया के कारण Data Modification की प्रक्रिया धीमी हो जाती है, विशेष रूप से तब जब Table में Records की संखया काफी ज्यादा होती है।

फिर भी Indexes Data के Access को निश्चित रूप से Increase करते हैं।

सामान्यतया Update Speed व Retrieval Speed के बीच Trade-Off होता है। Indexing के लिए एक उचित नियम ये है कि Indexing के लिए उन Columns को Choose करना चाहिए, जो SQL Query में ज्यादा Use होते हैं और Indexing सामान्यतया Foreign Key Columns  की करनी चाहिए। यदि किसी Indexing को Apply करने पर हमें लगता है कि Update Speed ज्यादा प्रभावित हो रही है, तो हमने जिन Indexes को Create किया है, उनमें से कुछ को या सभी को जरूरत के आधार पर Delete कर सकते हैं।

Clustering

Disk पर Data को Write करना या Disk से Data को Read करना DBMS का सबसे Slowest काम होता है। यदि हम Data के Disk पर Store होने व Disk से Data के Retrieve होने की संख्या को कम कर सकें, तो हम DBMS की Performance को बढा सकते हैं। Computer में सभी Records Disk Page के रूप में Store होते हैं। जब भी हम किसी Record को प्राप्त करने की Request करते हैं, Database उस Record के पूरे एक Page को Retrieve करता है, जिसमें वह Record होता है।

Page की Size अलग-अलग Operating Systems के आधार पर बदलती रहती है। Page की Size 512 Bytes से लेकर 4 KBytes तक होती है। हमें Disk से भले एक ही Record की जरूरत क्यों ना हो, हमेंशा Disk से सम्बंधित Record का पूरा एक Page ही Access होता है। इसलिए यदि हम उन Data को Access कर रहे हैं, जो समान Disk Page पर Stored हैं या जो नजदीकी Page में Stored हैं, तो हम Data Access की Speed को बढा सकते हैं। इस Process को Clustering कहते हैं और इसकी सुविधा Oracle जैसे DBMS में उपलब्ध है।

Cluster को Primary व Foreign Keys के Matching से बनने वाले Records को Hold करने के लिए Design किया जाता है। Cluster को Define करने के लिए हमें उन Tables के Column या Columns के Group को Specify करना होता है, जिनके आधार पर DBMS Cluster Create करता है और उन Tables को Cluster में Include करता है। फिर जिन Column या Composite Columns के आधार पर Clusters Create किया गया है, उन Column या Composite Columns के Same Values को Share करने वाले Records को Disk पर Physically Store किया जाता है। इन Records को जितना सम्भव होता है उतना नजदीक पर Store किया जाता है।

परिणामस्वरूप किसी Table के विभिन्न Records कई Disk Pages के रूप में बिखरे हुए रहते हैं, लेकिन Matching Primary Keys व Foreign Keys के Records अक्सर Same Page पर ही Store होते हैं। Clustering से सामान्यतया Join Performance की Speed बढ जाती है। फिर भी Indexes की तरह ही Clusters Create करने से सम्बंधित भी कुछ Trade-Offs हैं, जो निम्नानुसार हैं:

चूंकि Clustering Data के किसी File में Physically Store होने से सम्बंधित होता है, इसलिए एक Table को केवल एक Column या एक Composite Column के आधार पर ही Clustered किया जा सकता है।

जब पूरी Table के Records को Scan करने की जरूरत होती है, तब Clustering की वजह से Scanning की Speed कम हो जाती है, क्योंकि Clustering के कारण एक ही Table के विभिन्न Records Disk पर विभिन्न Disk Pages में Spread होकर Store होते हैं। Clustering से Data Insertion की Speed में भी कमी आती है। Cluster  जिस Column या Composite Column पर आधारित होता है, उन Columns का Modification करने से Speed कम हो जाती है।

Partitioning

Clustering की Reverse प्रक्रिया को Partitioning कहा जाता है। ये किसी बडी Table को कई छोटी Tables में pide कर देता है, ताकि DBMS बहुत सारे Data को एक साथ Retrieve ना कर सके। उदाहरण के लिए यदि हम Music Store Application के Database को लें, तो जैसे-जैसे Customers के Orders की संखया बढती जाती है, विशेष रूप से Order Lines Table के Records की संखया काफी बढ जाती है। यदि इन दोनों Tables में Records की संखया काफी ज्यादा हो जाए, तो इनसे किसी Record को Retrieve करने की Speed काफी कम हो जाएगी।

किसी Table का Horizontally व Vertically दो तरीकों से Partition किया जा सकता है। Horizontal Partitioning में एक Table के विभिन्न Rows या Records को Identical Structure में दो या दो से अधिक Tables में Split कर दिया जाता है। जबकि Vertical Partitioning में किसी Table के Columns को आपस में एक Primary Key द्वारा Linked रखते हुए Split कर दिया जाता है। दोनों ही Partition Process के अपने कुछ फायदे व कुछ नुकसान हैं।

Horizontal Partition में एक Table को Records के आधार पर दो या दो से अधिक Tables में Split किया जाता है, जबकि दोनों ही Tables का Structure समान रखा जाता है। Music Store Database में हम इस तकनीक को Use कर सकते हैं। उदाहरण के लिए Orders व Line Items Table को यदि Horizontal Partitioning के आधार पर एक से ज्यादा Tables में pide करना हो, तो हम इस काम को निम्नानुसार कर सकते हैं:

OpenOrders (OrderID, CustID, OrderDate)
OpenOrderLines (OrderID, ItemID, Quantity, Shipped?)
FilledOrders (OrderID, CustID, OrderDate)
FilledOrdersLines (OrderID, ItemID, Quantity, Shipped?)

जब भी OpenOrders Table के सभी Items को Ship कर दिया जाता है, एक Application Program OpenOrders Table व OpenOrderLines Table के सभी Records को Delete कर देता है और इन Records को FilledOrders व FilledOrdersLines Table में Fill कर देता है। इस प्रक्रिया के कारण OpenOrders व OpenOrderLines Table दोनों में ही Records की संखया कम ही रहती है जिससे Data के Modification व Retrieval की Performance बढ जाती है।

हालांकि FilledOrders व FilledOrdersLines Table से Data के Retrieval की Speed काफी धीमी होती है, लेकिन Music Store इन Tables को बहुत कम बार Access करता है। इस तरीके के साथ तब परेशानी आती है जब Music Store को Orders Table या OrderLines Tables के सभी Records को एक साथ Access करने की जरूरत पडती है।

इस तरीके को Use करने पर जब हम किसी Query में इन दोनों Tables के Data को Access करना चाहते हैं, तब हमें UNION Operator का प्रयोग करते हुए दो Queries को Mix करके Data को Access करना पडता है। यदि हम जो Application Create कर रहे हैं, उसमें दोनों Tables को बहुत कम बार एक साथ Access करने की जरूरत पडती है, तो हम इस तरीके को Performance बढाने के लिए Use कर सकते हैं।

Horizontal Partitioning से हमारे Database की Performance बढेगी या नहीं, इस बात का पता लगाने का केवल एक ही तरीका है, कि हम ये जानने की कोशिश करें कि हमारा Application इस तरह के Data को किस तरह से Access करने वाला है। यदि कुछ Records का एक एसा Group हो जिसे बार-बार Access किए जाने की जरूरत पडती हो, तो हम इस तरह की Partitioning को अपने Database पर Apply कर सकते हैं।

Vertical Partitioning में एक ही Table के विभिन्न Columns को एक से ज्यादा Tables में Divide कर लिया जाता है और दोनों ही Tables की Primary Key को समान रखा जाता है। एसा करने पर सभी Tables आपस में One To One की Relationship से Lined रहती हैं। उदाहरण के लिए यदि Music Store के Database में Titles व Prices की Information को काफी ज्यादा बार Use करने की जरूरत पडती है, तो हम Vertical Partitioning को Table पर Apply करके उसे निम्नानुसार दो भागों में बांट सकते हैं:

ItemTitles (ItemID, Title, Price)
ItemDetails (ItemID, Distributor, ReleaseDate, . . . )

इस Design का फायदा ये है कि ItemTitles Table के Records Physically काफी Close होते हैं। छोटी Tables कम Disk Pages में Store होते हैं, इसलिए इस प्रकार के Tables की Performance काफी अच्छी होती है। जब दोनों ही Tables के Data की जरूरत होती है, तब दोनों ही Tables को ItemID के आधार पर Join किया जाता है। अन्य Join Operation की तरह ही इस Join Operation की Speed भी कम होती है। (Database Performance Tuning)