बचने योग्य SQL anti-patterns

• SQL anti-patterns क्वेरी और डेटा पाइपलाइन की maintainability को कठिन बना देते हैं, और अपेक्षा से धीमा performance पैदा करते हैं
• CASE WHEN का अत्यधिक उपयोग, indexed columns पर function apply करना, SELECT *, DISTINCT का अत्यधिक उपयोग, nested views और subqueries, गहरी dependency structure आदि इसके प्रतिनिधि उदाहरण हैं
• ज़्यादातर समस्याएँ speed और deadline pressure की वजह से अपनाए गए अस्थायी workaround से पैदा होती हैं, और लंबे समय में data reliability और development speed—दोनों को नुकसान पहुँचाती हैं
• समाधान के तौर पर स्पष्ट join definitions, dimension tables का उपयोग, अनावश्यक nesting हटाना, और periodic view cleanup की ज़रूरत होती है
• SQL को सिर्फ एक simple script नहीं बल्कि team level पर managed production code की तरह handle करना चाहिए, और readability को ध्यान में रखकर किया गया शुरुआती design rework को कम करता है

---

परिचय

• आज हम SQL anti-patterns के कुछ ऐसे उदाहरणों पर बात करेंगे जो अक्सर दिखाई देते हैं और जिनका प्रभाव बड़ा होता है
• ये समस्याएँ data reliability में गिरावट, query development speed में कमी जैसी बुरी श्रृंखला पैदा करती हैं
• नीचे दी गई सूची सभी मामलों को शामिल नहीं करती; अगर आप गहरी समझ चाहते हैं, तो Bill Karwin की पुस्तक की सिफारिश की जाती है

जरूरत से ज़्यादा जटिल CASE WHEN statements

• बड़े सिस्टम में state codes (जैसे: 1=out of stock) को इंसानों के पढ़ने लायक बनाने के लिए CASE WHEN statements का अक्सर उपयोग किया जाता है
• dashboard या report को जल्दी बनाने के लिए किसी एक View में ही उस CASE WHEN logic को जोड़ देना लंबे समय में एक anti-pattern बन जाता है
• इससे duplicate logic की copy/paste, interpretation mismatch जैसी समस्याएँ पैदा होती हैं और पूरा query environment बेतरतीब हो जाता है
• इसका समाधान यह है कि state code conversion के लिए अलग से dimension table या shared view बनाया जाए, ताकि reusability मिल सके

indexed columns पर function का उपयोग

• WHERE UPPER(name) = 'ABC' की तरह indexed column पर function apply करने से index की efficiency खत्म हो जाती है
  • SQL Server आदि में इसकी वजह से अनावश्यक full table scan हो सकता है
• इसका समाधान है function-applied column को अलग से index करना या input value को convert करके query condition को सरल बनाना

views में SELECT * का उपयोग

• view बनाते समय SELECT * सुविधाजनक लग सकता है, लेकिन structure (schema) बदलते ही view आसानी से टूट सकता है
• इसमें अनावश्यक columns भी शामिल हो जाते हैं, जिससे अनचाही dependencies और performance issues पैदा होते हैं; इसलिए columns को explicitly select करना चाहिए

DISTINCT का अत्यधिक उपयोग करके duplicate “हल” करना

• जब गलत join की वजह से duplicate results आते हैं, तो SELECT DISTINCT से अस्थायी समाधान करना data integrity problem को छिपा देता है
  • असली कारण join condition का अधूरापन या relationship definition (1:1, 1:N आदि) में गलती होता है
  • सही समाधान है join logic को मजबूत करके relationship definition को स्पष्ट करना, और aggregation या reporting से पहले relationship consistency सुनिश्चित करना

view nesting (Excessive View Layer Stacking)

• जब कई टीमें existing views को reuse करते हुए लगातार नए views जोड़ती रहती हैं, तो dependency chain जटिल हो जाती है और performance तेज़ी से गिरता है
  • debugging कठिन हो जाती है, और query को expand करना “पुरातात्विक खुदाई” जितना मुश्किल हो सकता है
• समय-समय पर transformation logic को flatten करना चाहिए, और complex operations को स्पष्ट base view या table में materialize करने की strategy अपनानी चाहिए

बहुत गहरी subqueries

• 3~4 स्तर से अधिक deeply nested subqueries readability को घटाती हैं और debugging को कठिन बनाती हैं
  • 5000 से अधिक पंक्तियों वाली subqueries के उदाहरण भी मौजूद हैं
• CTE(Common Table Expression) का उपयोग करने से logical stages को अलग करना आसान होता है और query की clarity(readability) बढ़ती है

निष्कर्ष

• SQL ऊपर से सरल दिख सकता है, लेकिन सिस्टम बड़ा होने पर इसकी complexity बढ़ती जाती है
• ज़्यादातर anti-patterns बुरी मंशा से नहीं, बल्कि ‘तेज़ परिणाम’ पाने के लिए किए गए समझौते (speed, deadline, workaround) की वजह से पैदा होते हैं
• SQL को code की तरह manage करना (version control, code review, स्पष्ट design) लंबे समय में productivity और reliability—दोनों को सुनिश्चित कर सकता है
• शुरुआत में कुछ मिनट लगाकर clarity और consistency पर ध्यान देने से आगे चलकर rework और confusion काफ़ी कम हो जाते हैं

[aer0700]

अभी समय नहीं है, इसलिए सोचा था कि फिलहाल बस तात्कालिक समस्या सुलझा लें और बाद में फिर से लिखेंगे, लेकिन ऐसी चीज़ें जमा होती-जाती हैं और आखिरकार भयानक query hell बन जाती हैं। मैंने भी ऐसी कई चीज़ें बनाई हैं। यह जानते हुए भी कि उन्हें फिर से लिखने वाला वह "बाद में" कभी नहीं आने वाला।

[firefoxsaiko123]

कह...

[ilikeall]

"ज़्यादातर समस्याएँ गति और deadline के दबाव की वजह से निकाले गए अस्थायी जुगाड़ू समाधानों से पैदा होती हैं"
हाय..

[GN⁺]

Hacker News की राय

• जब मैं किसी query में DISTINCT इस्तेमाल होते देखता हूँ, तो मुझे शक होता है कि लेखक data model या set theory को ठीक से नहीं समझता, या शायद दोनों को ही नहीं
  • कभी-कभी DISTINCT इस बात का संकेत भी हो सकता है कि schema को जरूरत से ज़्यादा normalize किया गया है। उदाहरण के लिए, सिर्फ़ duplicate city names रिकॉर्ड होने से रोकने के लिए अलग से addresses_cities जैसी table बनाना ज़रूरी नहीं लगता
  • मेरा अनुभव भी लगभग ऐसा ही है। लेकिन हाल में एक मामला था जहाँ सभी join सही होने के बावजूद CTE के अंदर DISTINCT डालने से performance काफ़ी बेहतर हो गई। लगता है कि जब record की uniqueness सुनिश्चित हो जाती है, तो query planner बेहतर optimization कर पाता है
  • मैंने भी एक query में LIMIT 1 इसलिए जोड़ा था क्योंकि मुझे लगा था कि ज़्यादा से ज़्यादा एक ही result आएगा, और इस पर भी इसी तरह का नकारात्मक feedback मिला। लेकिन बड़ी tables में (sqlite, mysql, postgresql सभी में) अक्सर ऐसा लगता है कि DB मनचाहा record मिल जाने के बाद भी पूरी table स्कैन करता रहता है
  • सवाल यह है कि SELECT x FROM t query में DISTINCT को सुरक्षित रूप से कब छोड़ा जा सकता है। मान लीजिए कि t के schema में x पर PRIMARY या UNIQUE constraint है, फिर भी कोई कभी भी UNIQUE constraint हटा सकता है। तब duplicate आने लगेंगे और आप कारण ढूँढते रह जाएँगे। SQL set भाषा नहीं बल्कि bag भाषा है। runtime पर rel t और attribute x मिलते ही वह बस values लौटाता है। duplicate आ सकते हैं, type भी बदल सकता है। अगर आपको Set चाहिए, तो DISTINCT साफ़-साफ़ लिखना चाहिए। query planner runtime पर UNIQUE या PRIMARY होने पर deduplication नहीं करेगा
  • Cypher में स्थिति उलटी है। neo4j में complex data के साथ काम करते समय duplicate nodes बहुत आसानी से result में आ जाते हैं, इसलिए DISTINCT ज़रूरी हो जाता है। खासकर variable-length relationships में DISTINCT न हो तो query धीमी भी हो जाती है और duplicates भी बहुत बढ़ जाते हैं
• मैंने DISTINCT के बिना भी सही query structure डिज़ाइन करने के तरीक़े पर लगभग 9000 अक्षरों का दो-भाग वाला tutorial लिखा है
  https://kb.databasedesignbook.com/posts/systematic-design-of-join-queries/
  • अच्छा article है। bookmark कर लिया। और फिर पता चला कि यह वास्तव में एक किताब भी है
• जिन बातों का कम ज़िक्र होता है, उनमें एक है "जो मौजूद नहीं है" उसे ढूँढने वाली queries। उदाहरण के लिए != या NOT IN (...) का इस्तेमाल ज़्यादातर मामलों में inefficient होता है (हालाँकि जब दूसरी conditions result set को काफ़ी छोटा कर दें, तब ठीक हो सकता है)। और यह समझना ज़रूरी है कि DB null को कैसे handle करता है। null और empty string एक ही हैं या नहीं, null == null है या नहीं, यह DB के अनुसार बदल सकता है
  • null handling और indexing के संदर्भ में, जिन DBs का मैंने उपयोग किया है वे null values को index नहीं करते, इसलिए "WHERE col IS NULL" query, col पर index होने के बावजूद, inefficient चलती है। अगर सचमुच ज़रूरत हो, तो col के null होने को दिखाने वाला char(1) या bit column बनाकर उस field पर index करना बेहतर रहेगा
  • आपने कहा कि != या NOT IN (...) लगभग हमेशा inefficient होते हैं, तो क्यों? अगर दाहिनी ओर का मान constant हो, तो hash table lookup हो सकता है और सामान्यतः efficient होना चाहिए। क्या इसका कोई बेहतर विकल्प है?
• मुझे नहीं लगता कि बताए गए सभी 'anti-patterns' सचमुच anti-pattern हैं। query conditions का index से match न करना आख़िरकार index के सिद्धांत को ठीक से न समझने की समस्या है। और यहाँ बताई गई कई समस्याएँ SQL से अधिक database schema design से जुड़ी हुई हैं। अगर DISTINCT की ज़रूरत पड़ रही है, तो संभव है कि primary key design सही न हो। अगर बहुत अधिक views की परतें बन रही हैं, तो मूल tables का design ही ग़लत हो सकता है। अच्छी DB modeling पहले से बहुत सी समस्याओं को रोक देती है
• ये 'anti-patterns' असल में SQL language design की सीमाओं (या design की कमी) से पैदा हुए साधारण workarounds भर हैं। मैं एक नई भाषा बना रहा हूँ जो SQL databases पर चलती है, और चाहता हूँ कि ऐसी हर समस्या के लिए बेहतर विकल्प दे सकूँ। यह अभी अधूरी है और documentation भी कम है, लेकिन रुचि हो तो https://lutra-lang.org पर feedback चाहूँगा
  • SQL database कहना थोड़ा अस्पष्ट है। SQL सिर्फ़ relational ही नहीं, non-relational DBs में भी लागू हुआ है। विशेषज्ञ SQL की समस्याओं को बहुत पहले से पहचानते आए हैं, और Chris Date तथा Hugh Darwen का Tutorial D जैसे विकल्प भी मौजूद रहे हैं। फिर भी दशकों से जमा SQL code और tools की वजह से विकल्प टिक नहीं पाए। मुझे तो SQL की बदौलत दशकों तक job security और steady income मिली है, इसलिए बेहतर भाषा की ज़रूरत मानते हुए भी मैं इस स्थिति को कुछ सकारात्मक नज़र से देखता हूँ
  • project देखने में अच्छा लग रहा है। अगर यह और mature हुआ तो मैं ज़रूर इसे follow करूँगा
• सबसे बड़ा anti-pattern यह है कि SQL को सिर्फ़ query language समझा जाए, असली programming language नहीं। code style में consistent indentation रखें, और logically related हिस्सों को साथ रखें। subqueries को CTE में बदलने की सलाह दूँगा। प्रभावी commenting भी महत्वपूर्ण है। मेरी style देखें: https://bentilly.blogspot.com/2011/02/sql-formatting-style.html
  • मुझे लगता है कि ऐसे code style विवाद किसी ठीक-ठाक linter tool के बिना लगभग अर्थहीन हैं
• मेरी query speed बढ़ाने और server resources कम खर्च करने का सबसे बड़ा रहस्य यह रहा है कि queries को और अधिक sargable बनाया जाए
  https://en.wikipedia.org/wiki/Sargable
  https://www.brentozar.com/blitzcache/non-sargable-predicates/
  • मैं जानना चाहता हूँ कि sargable शब्द वास्तव में किन communities में इस्तेमाल होता है। 20 साल से ज़्यादा SQL के साथ काम किया है, लेकिन manuals, Stack Overflow, या HN में इसे शायद ही कभी देखा है। क्या यह किसी खास DB, कंपनी, या open source community में ज़्यादा प्रचलित है?
  • sargable की उत्पत्ति खोजते समय यह StackOverflow जवाब मददगार लगा https://dba.stackexchange.com/a/217983
    sargable शब्द Search ARGument ABLE का portmanteau है
• CASE WHEN के ज़रूरत से ज़्यादा इस्तेमाल की समस्या को UDF (User Defined Function) में logic को एक जगह समेटकर काफी हद तक हल किया जा सकता है
  indexed column पर function लगाना इस बात का संकेत है कि query sargable नहीं है
  DISTINCT के अति-उपयोग की जगह, join से निकले fanout में table grain के हिसाब से de-dupe करने के लिए

  ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1
  

  जैसी तकनीक उपयोगी होती है। कुछ DBs में QUALIFY का support होता है, जिससे query काफ़ी साफ़ हो जाती है
  sargable की व्याख्या
  Redshift में QUALIFY
  • non-sargable problems को expression index से आसानी से हल किया जा सकता है। कम से कम sqlite में तो मेरा यही मानना है
• nested views का उपयोग कई बार सचमुच ज़रूरी भी होता है। हमारे POS software में transaction को एक ही नज़र में साफ़-सुथरे तरीके से दिखाने के लिए backbone view बनाने में हम nested views का बहुत उपयोग करते हैं। नहीं तो हर table के लिए अलग-अलग where clauses लिखने पड़ते, और void/return/cancel जैसी तमाम conditions हर जगह संभालनी पड़तीं। फिर कोई बदलाव आए तो दर्जनों views/procedures बदलने पड़ते। हमारे use case में nested views कहीं ज़्यादा व्यावहारिक हैं
• indexed column पर function लगाने वाली समस्या को और स्पष्ट समझाने की ज़रूरत है। index लगी हुई column पर function लगते ही index की efficiency ख़त्म हो जाती है, और व्यवहार में full scan होने लगता है, इसलिए यह धीमा पड़ता है। मैंने यह बात खुद झेलकर सीखी है
  • इस पर एक प्रसिद्ध reference है https://use-the-index-luke.com/sql/where-clause/obfuscation
  • सुझाया गया solution (जैसे UPPER(name) column पर index जोड़ना) कम से कम MS SQL Server में सबसे अच्छा तरीका नहीं है। दूसरे DBs में support कैसा है, यह नहीं जानता, लेकिन बेहतर समाधान यह है कि सीधे COLLATE के साथ case-insensitive computed column बनाया जाए

    ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;
    

    (अपनी पसंद के अनुसार समायोजित करें)
  • संबंधित blog में एक typo है। पहली पंक्ति uppercase में होनी चाहिए। अगर index पहले से function-applied data पर बना हो, तो query के समय full scan नहीं होगा। हालाँकि इस उदाहरण में शुरू से ही case-insensitive collation इस्तेमाल करना बेहतर है, लेकिन सामान्य रूप से मूल विचार सही है
  • मैंने यह खुद झेलकर सीखा है शायद SQL developers का motto ही है। जैसे भी हो, SQL लंबे समय से काफ़ी स्थिर रूप से विकसित हुआ है, इसलिए ऐसे traps पहले से जान लेना बहुत लंबे समय तक काम आता है

[ahwjdekf]

सबसे अहम चीज़ छूट गई है।

• ORM का इस्तेमाल करना