SQLite की परफ़ॉर्मेंस के लिए सबसे सही तरीके

Android, SQLite के लिए पहले से मौजूद सहायता उपलब्ध कराता है. यह एक असरदार एसक्यूएल डेटाबेस है. अपने ऐप्लिकेशन की परफ़ॉर्मेंस को ऑप्टिमाइज़ करने के लिए, इन सबसे सही तरीकों का इस्तेमाल करें. इससे यह पक्का किया जा सकेगा कि आपका ऐप्लिकेशन तेज़ गति से काम करता रहे और डेटा बढ़ने पर भी इसकी परफ़ॉर्मेंस में कोई बदलाव न हो. इन सबसे सही तरीकों का इस्तेमाल करके, परफ़ॉर्मेंस से जुड़ी उन समस्याओं को भी कम किया जा सकता है जिन्हें दोहराना और हल करना मुश्किल होता है.

बेहतर परफ़ॉर्मेंस के लिए, परफ़ॉर्मेंस के इन सिद्धांतों का पालन करें:

  • कम पंक्तियां और कॉलम पढ़ें: सिर्फ़ ज़रूरी डेटा पाने के लिए, अपनी क्वेरी को ऑप्टिमाइज़ करें. डेटाबेस से कम से कम डेटा पढ़ें, क्योंकि ज़्यादा डेटा पाने से परफ़ॉर्मेंस पर असर पड़ सकता है.

  • SQLite इंजन को काम सौंपना: एसक्यूएल क्वेरी में ही कैलकुलेशन, फ़िल्टर करने, और क्रम से लगाने के ऑपरेशन करें. SQLite के क्वेरी इंजन का इस्तेमाल करने से, परफ़ॉर्मेंस को बेहतर बनाया जा सकता है.

  • डेटाबेस स्कीमा में बदलाव करना: अपने डेटाबेस स्कीमा को इस तरह से डिज़ाइन करें कि SQLite, क्वेरी प्लान और डेटा को बेहतर तरीके से दिखा सके. टेबल को सही तरीके से इंडेक्स करें और परफ़ॉर्मेंस को बेहतर बनाने के लिए टेबल स्ट्रक्चर को ऑप्टिमाइज़ करें.

इसके अलावा, समस्या हल करने के लिए उपलब्ध टूल का इस्तेमाल करके, अपने SQLite डेटाबेस की परफ़ॉर्मेंस का आकलन किया जा सकता है. इससे उन क्षेत्रों की पहचान करने में मदद मिलती है जिनमें ऑप्टिमाइज़ेशन की ज़रूरत होती है.

हमारा सुझाव है कि आप Jetpack Room लाइब्रेरी का इस्तेमाल करें.

परफ़ॉर्मेंस के लिए डेटाबेस कॉन्फ़िगर करना

SQLite में बेहतर परफ़ॉर्मेंस के लिए, अपने डेटाबेस को कॉन्फ़िगर करने के लिए इस सेक्शन में दिया गया तरीका अपनाएं.

राइट-अहेड लॉगिंग चालू करना

SQLite, म्यूटेशन को लॉग में जोड़कर लागू करता है. यह लॉग को कभी-कभी डेटाबेस में कंप्रेस करता है. इसे राइट-अहेड लॉगिंग (डब्ल्यूएएल) कहा जाता है.

अगर ATTACH DATABASE का इस्तेमाल नहीं किया जा रहा है, तो WAL चालू करें.

सिंक करने के मोड को कम करना

WAL का इस्तेमाल करते समय, डिफ़ॉल्ट रूप से हर कमिट एक fsync जारी करता है, ताकि यह पक्का किया जा सके कि डेटा डिस्क तक पहुंच गया है. इससे डेटा को सुरक्षित रखने में मदद मिलती है, लेकिन कमिट करने की प्रोसेस धीमी हो जाती है.

SQLite में, सिंक्रोनस मोड को कंट्रोल करने का विकल्प होता है. अगर आपने WAL चालू किया है, तो सिंक्रोनस मोड को NORMAL पर सेट करें:

Kotlin

db.execSQL("PRAGMA synchronous = NORMAL")

Java

db.execSQL("PRAGMA synchronous = NORMAL");

इस सेटिंग में, डेटा को डिस्क में सेव करने से पहले ही कमिट किया जा सकता है. अगर डिवाइस बंद हो जाता है, जैसे कि बिजली चले जाने या कर्नल पैनिक की वजह से, तो हो सकता है कि कमिट किया गया डेटा मिट जाए. हालांकि, लॉगिंग की वजह से आपका डेटाबेस खराब नहीं होता.

अगर सिर्फ़ आपका ऐप्लिकेशन क्रैश होता है, तो भी आपका डेटा डिस्क तक पहुंच जाता है. ज़्यादातर ऐप्लिकेशन के लिए, इस सेटिंग से परफ़ॉर्मेंस बेहतर होती है. इसके लिए, कोई शुल्क नहीं लिया जाता.

टेबल स्कीमा को बेहतर तरीके से तय करना

परफ़ॉर्मेंस को ऑप्टिमाइज़ करने और डेटा की खपत को कम करने के लिए, टेबल का बेहतर स्कीमा तय करें. SQLite, क्वेरी प्लान और डेटा को असरदार तरीके से व्यवस्थित करता है. इससे डेटा को तेज़ी से वापस पाने में मदद मिलती है. इस सेक्शन में, टेबल स्कीमा बनाने के सबसे सही तरीके बताए गए हैं.

इस चीज़ को ध्यान में रखते हुए सुझाव: INTEGER PRIMARY KEY

इस उदाहरण के लिए, टेबल को इस तरह से तय करें और उसमें डेटा भरें:

CREATE TABLE Customers(
  id INTEGER,
  name TEXT,
  city TEXT
);
INSERT INTO Customers Values(456, 'John Lennon', 'Liverpool, England');
INSERT INTO Customers Values(123, 'Michael Jackson', 'Gary, IN');
INSERT INTO Customers Values(789, 'Dolly Parton', 'Sevier County, TN');

टेबल का आउटपुट यहां दिया गया है:

rowid id नाम शहर
1 456 जॉन लेनन लिवरपूल, इंग्लैंड
2 123 माइकल जैक्सन गैरी, इंडियाना
3 789 डॉली पार्टन सेवियर काउंटी, टेनेसी

कॉलम rowid एक इंडेक्स है, जो इंसर्शन ऑर्डर को बनाए रखता है. rowid के हिसाब से फ़िल्टर करने वाली क्वेरी को तेज़ी से बी-ट्री सर्च के तौर पर लागू किया जाता है. हालांकि, id के हिसाब से फ़िल्टर करने वाली क्वेरी को टेबल स्कैन के तौर पर लागू किया जाता है, जिसमें ज़्यादा समय लगता है.

अगर आपको id के हिसाब से लुकअप करने हैं, तो स्टोरेज में कम डेटा और डेटाबेस को ज़्यादा तेज़ी से ऐक्सेस करने के लिए, id कॉलम को सेव न करें:rowid

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT
);

अब आपकी टेबल इस तरह दिखेगी:

id नाम शहर
123 माइकल जैक्सन गैरी, इंडियाना
456 जॉन लेनन लिवरपूल, इंग्लैंड
789 डॉली पार्टन सेवियर काउंटी, टेनेसी

आपको rowid कॉलम को सेव करने की ज़रूरत नहीं होती. इसलिए, id क्वेरी तेज़ी से होती हैं. ध्यान दें कि अब टेबल को इंसर्शन ऑर्डर के बजाय id के हिसाब से क्रम में लगाया गया है.

इंडेक्स की मदद से क्वेरी को तेज़ी से प्रोसेस करना

SQLite, क्वेरी को तेज़ी से प्रोसेस करने के लिए इंडेक्स का इस्तेमाल करता है. किसी कॉलम को फ़िल्टर (WHERE), क्रम से लगाने (ORDER BY) या एग्रीगेट (GROUP BY) करने पर, अगर टेबल में कॉलम के लिए इंडेक्स मौजूद है, तो क्वेरी तेज़ी से प्रोसेस होती है.

पिछले उदाहरण में, city के हिसाब से फ़िल्टर करने के लिए, पूरी टेबल को स्कैन करना ज़रूरी है:

SELECT id, name
WHERE city = 'London, England';

अगर किसी ऐप्लिकेशन के लिए शहर के बारे में कई क्वेरी की जाती हैं, तो इंडेक्स का इस्तेमाल करके उन क्वेरी को तेज़ी से पूरा किया जा सकता है:

CREATE INDEX city_index ON Customers(city);

इंडेक्स को एक अतिरिक्त टेबल के तौर पर लागू किया जाता है. इसे इंडेक्स कॉलम के हिसाब से क्रम में लगाया जाता है और rowid पर मैप किया जाता है:

शहर rowid
गैरी, इंडियाना 2
लिवरपूल, इंग्लैंड 1
सेवियर काउंटी, टेनेसी 3

ध्यान दें कि city कॉलम के स्टोरेज की लागत अब दोगुनी हो गई है, क्योंकि यह अब मूल टेबल और इंडेक्स, दोनों में मौजूद है. इंडेक्स का इस्तेमाल करने पर, स्टोरेज के लिए अतिरिक्त शुल्क देना होगा. हालांकि, इससे क्वेरी तेज़ी से प्रोसेस होंगी. हालांकि, ऐसे इंडेक्स को बनाए न रखें जिसका इस्तेमाल नहीं किया जा रहा है, ताकि क्वेरी की परफ़ॉर्मेंस में कोई सुधार न होने पर भी स्टोरेज का शुल्क न देना पड़े.

कई कॉलम वाले इंडेक्स बनाना

अगर आपकी क्वेरी में कई कॉलम शामिल हैं, तो क्वेरी को पूरी तरह से तेज़ करने के लिए, एक से ज़्यादा कॉलम वाले इंडेक्स बनाए जा सकते हैं. बाहरी कॉलम पर इंडेक्स का इस्तेमाल भी किया जा सकता है. साथ ही, अंदरूनी खोज को लीनियर स्कैन के तौर पर किया जा सकता है.

उदाहरण के लिए, इस क्वेरी पर विचार करें:

SELECT id, name
WHERE city = 'London, England'
ORDER BY city, name

क्वेरी में बताए गए क्रम में मल्टी-कॉलम इंडेक्स का इस्तेमाल करके, क्वेरी को तेज़ी से पूरा किया जा सकता है:

CREATE INDEX city_name_index ON Customers(city, name);

हालांकि, अगर आपके पास सिर्फ़ city पर इंडेक्स है, तो बाहर से ऑर्डर करने की प्रोसेस अब भी तेज़ होगी. वहीं, अंदर से ऑर्डर करने के लिए लीनियर स्कैन की ज़रूरत होगी.

यह सुविधा, प्रीफ़िक्स के साथ की गई क्वेरी के लिए भी काम करती है. उदाहरण के लिए, इंडेक्स ON Customers (city, name) की मदद से, city के हिसाब से फ़िल्टर करने, क्रम से लगाने, और ग्रुप करने की प्रोसेस को भी तेज़ किया जा सकता है. इसकी वजह यह है कि एक से ज़्यादा कॉलम वाले इंडेक्स के लिए इंडेक्स टेबल को दिए गए इंडेक्स के हिसाब से क्रम में लगाया जाता है.

इस चीज़ को ध्यान में रखते हुए सुझाव: WITHOUT ROWID

डिफ़ॉल्ट रूप से, SQLite आपकी टेबल के लिए एक rowid कॉलम बनाता है. इसमें rowid एक इंप्लिसिट INTEGER PRIMARY KEY AUTOINCREMENT होता है. अगर आपके पास पहले से ही INTEGER PRIMARY KEY कॉलम है, तो यह कॉलम rowid का उपनाम बन जाता है.

जिन टेबल में INTEGER के अलावा कोई और प्राइमरी कुंजी है या कॉलम का कंपोज़िट है उनके लिए, WITHOUT ROWID का इस्तेमाल करें.

छोटे डेटा को BLOB के तौर पर और बड़े डेटा को फ़ाइल के तौर पर सेव करना

अगर आपको किसी लाइन में ज़्यादा डेटा जोड़ना है, जैसे कि किसी इमेज का थंबनेल या किसी संपर्क के लिए फ़ोटो, तो डेटा को BLOB कॉलम या किसी फ़ाइल में सेव किया जा सकता है. इसके बाद, फ़ाइल के पाथ को कॉलम में सेव किया जा सकता है.

फ़ाइलों का साइज़ आम तौर पर 4 केबी के हिसाब से बढ़ता है. बहुत छोटी फ़ाइलों के लिए, जहां राउंडिंग की गड़बड़ी काफ़ी ज़्यादा होती है, उन्हें डेटाबेस में BLOB के तौर पर सेव करना ज़्यादा सही होता है. SQLite, फ़ाइल सिस्टम के कॉल को कम करता है. साथ ही, कुछ मामलों में यह फ़ाइल सिस्टम से ज़्यादा तेज़ होता है.

क्वेरी की परफ़ॉर्मेंस को बेहतर बनाना

SQLite में क्वेरी की परफ़ॉर्मेंस को बेहतर बनाने के लिए, इन सबसे सही तरीकों को अपनाएं. इससे रिस्पॉन्स टाइम को कम करने और प्रोसेसिंग की क्षमता को बढ़ाने में मदद मिलेगी.

सिर्फ़ उन लाइनों को पढ़ें जिनकी आपको ज़रूरत है

फ़िल्टर की मदद से, कुछ शर्तें तय करके नतीजों को सीमित किया जा सकता है. जैसे, तारीख की सीमा, जगह या नाम. सीमाएं तय करके, यह कंट्रोल किया जा सकता है कि आपको कितने नतीजे दिखें:

Kotlin

db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  while (cursor.moveToNext()) {
    ...
  }
}

सिर्फ़ उन कॉलम को पढ़ें जिनकी आपको ज़रूरत है

ज़रूरत न होने पर कॉलम न चुनें. इससे आपकी क्वेरी धीमी हो सकती हैं और संसाधनों का इस्तेमाल ज़्यादा हो सकता है. इसके बजाय, सिर्फ़ उन कॉलम को चुनें जिनका इस्तेमाल किया जाता है.

यहां दिए गए उदाहरण में, id, name, और phone को चुना गया है:

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT id, name, phone
    FROM customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(1)
        // ...
    }
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name, phone
    FROM customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(1);
    ...
  }
}

हालांकि, आपको सिर्फ़ name कॉलम की ज़रूरत है:

Kotlin

db.rawQuery("""
    SELECT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(0)
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(0);
    ...
  }
}

क्वेरी में पैरामीटर जोड़ना

आपकी क्वेरी स्ट्रिंग में ऐसा पैरामीटर शामिल हो सकता है जिसके बारे में सिर्फ़ रनटाइम पर पता चलता है. जैसे, यहां दिया गया पैरामीटर:

Kotlin

fun getNameById(id: Long): String? 
    db.rawQuery(
        "SELECT name FROM customers WHERE id=$id", null
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

Java

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery(
      "SELECT name FROM customers WHERE id=" + id, null)) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

ऊपर दिए गए कोड में, हर क्वेरी एक अलग स्ट्रिंग बनाती है. इसलिए, इसे स्टेटमेंट कैश से कोई फ़ायदा नहीं मिलता. हर कॉल को एक्ज़ीक्यूट करने से पहले, SQLite को उसे कंपाइल करना होता है. इसके बजाय, id आर्ग्युमेंट को पैरामीटर से बदला जा सकता है. साथ ही, वैल्यू को selectionArgs से बाइंड किया जा सकता है:

Kotlin

fun getNameById(id: Long): String? {
    db.rawQuery(
        """
          SELECT name
          FROM customers
          WHERE id=?
        """.trimIndent(), arrayOf(id.toString())
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

Java

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery("""
          SELECT name
          FROM customers
          WHERE id=?
      """, new String[] {String.valueOf(id)})) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

अब क्वेरी को एक बार कंपाइल किया जा सकता है और कैश मेमोरी में सेव किया जा सकता है. कंपाइल की गई क्वेरी का फिर से इस्तेमाल किया जाता है. ऐसा getNameById(long) के अलग-अलग इनवोकेशन के बीच किया जाता है.

कोड में नहीं, बल्कि एसक्यूएल में बदलाव करें

एक ऐसी क्वेरी का इस्तेमाल करें जो टारगेट किए गए सभी नतीजे दिखाए. इसके बजाय, प्रोग्राम के हिसाब से SQL क्वेरी को दोहराने वाले लूप का इस्तेमाल न करें, ताकि अलग-अलग नतीजे दिखाए जा सकें. प्रोग्राम के हिसाब से बनाए गए लूप, एक एसक्यूएल क्वेरी की तुलना में करीब 1,000 गुना ज़्यादा समय लेते हैं.

यूनीक वैल्यू के लिए DISTINCT का इस्तेमाल करना

DISTINCT कीवर्ड का इस्तेमाल करने से, प्रोसेस किए जाने वाले डेटा की मात्रा कम हो जाती है. इससे आपकी क्वेरी की परफ़ॉर्मेंस बेहतर हो सकती है. उदाहरण के लिए, अगर आपको किसी कॉलम से सिर्फ़ यूनीक वैल्यू दिखानी हैं, तो DISTINCT का इस्तेमाल करें:

Kotlin

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    // Only iterate over distinct names in Java
    ...
  }
}

जब भी हो सके, एग्रीगेट फ़ंक्शन का इस्तेमाल करें

लाइन के डेटा के बिना, एग्रीगेट किए गए नतीजों के लिए एग्रीगेट फ़ंक्शन का इस्तेमाल करें. उदाहरण के लिए, यहां दिया गया कोड यह जांच करता है कि कम से कम एक मैचिंग लाइन मौजूद है या नहीं:

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """, null)) {
  if (cursor.moveToFirst()) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

सिर्फ़ पहली लाइन फ़ेच करने के लिए, EXISTS() का इस्तेमाल किया जा सकता है. इससे, मैच करने वाली कोई लाइन न होने पर 0 और एक या उससे ज़्यादा लाइनें मैच होने पर 1 दिखता है:

Kotlin

db.rawQuery("""
    SELECT EXISTS (
        SELECT null
        FROM Customers
        WHERE city = 'Paris';
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM Customers
      WHERE city = 'Paris'
    );
    """, null)) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

अपने ऐप्लिकेशन कोड में, SQLite एग्रीगेट फ़ंक्शन का इस्तेमाल करें:

  • COUNT: यह फ़ंक्शन, किसी कॉलम में मौजूद पंक्तियों की संख्या गिनता है.
  • SUM: किसी कॉलम में मौजूद सभी संख्यात्मक वैल्यू जोड़ता है.
  • MIN या MAX: इससे सबसे कम या सबसे ज़्यादा वैल्यू का पता चलता है. यह संख्या वाली कॉलम, DATE टाइप, और टेक्स्ट टाइप के लिए काम करता है.
  • AVG: इससे संख्या वाली वैल्यू का औसत पता चलता है.
  • GROUP_CONCAT: यह फ़ंक्शन, स्ट्रिंग को एक साथ जोड़ता है. इसमें सेपरेटर का इस्तेमाल करना ज़रूरी नहीं है.

Cursor.getCount() के बजाय COUNT() का इस्तेमाल करें

इस उदाहरण में, Cursor.getCount() फ़ंक्शन, डेटाबेस की सभी लाइनों को पढ़ता है और लाइन की सभी वैल्यू दिखाता है:

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    val count = cursor.getCount()
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id
    FROM Customers;
    """, null)) {
  int count = cursor.getCount();
  ...
}

हालांकि, COUNT() का इस्तेमाल करने पर, डेटाबेस सिर्फ़ गिनती दिखाता है:

Kotlin

db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    cursor.moveToFirst()
    val count = cursor.getInt(0)
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """, null)) {
  cursor.moveToFirst();
  int count = cursor.getInt(0);
  ...
}

कोड के बजाय नेस्ट की गई क्वेरी

SQL को कंपोज़ किया जा सकता है. साथ ही, यह सबक्वेरी, जॉइन, और फ़ॉरेन की की पाबंदियों के साथ काम करता है. ऐप्लिकेशन कोड का इस्तेमाल किए बिना, एक क्वेरी के नतीजे का इस्तेमाल दूसरी क्वेरी में किया जा सकता है. इससे SQLite से डेटा कॉपी करने की ज़रूरत कम हो जाती है. साथ ही, डेटाबेस इंजन को आपकी क्वेरी को ऑप्टिमाइज़ करने की अनुमति मिल जाती है.

यहां दिए गए उदाहरण में, यह पता लगाने के लिए क्वेरी चलाई जा सकती है कि किस शहर में सबसे ज़्यादा ग्राहक हैं. इसके बाद, उस शहर के सभी ग्राहकों का पता लगाने के लिए, क्वेरी में मिले नतीजे का इस्तेमाल किया जा सकता है:

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        val topCity = cursor.getString(0)
        db.rawQuery("""
            SELECT name, city
            FROM Customers
            WHERE city = ?;
        """.trimIndent(),
        arrayOf(topCity)).use { innerCursor ->
            while (innerCursor.moveToNext()) {
                ...
            }
        }
    }
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """, null)) {
  if (cursor.moveToFirst()) {
    String topCity = cursor.getString(0);
    try (Cursor innerCursor = db.rawQuery("""
        SELECT name, city
        FROM Customers
        WHERE city = ?;
        """, new String[] {topCity})) {
        while (innerCursor.moveToNext()) {
          ...
        }
    }
  }
}

पिछले उदाहरण के मुकाबले आधे समय में नतीजा पाने के लिए, नेस्ट किए गए स्टेटमेंट के साथ एक SQL क्वेरी का इस्तेमाल करें:

Kotlin

db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
        SELECT city
        FROM Customers
        GROUP BY city
        ORDER BY COUNT (*) DESC
        LIMIT 1;
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToNext()) {
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
      SELECT city
      FROM Customers
      GROUP BY city
      ORDER BY COUNT(*) DESC
      LIMIT 1
    );
    """, null)) {
  while(cursor.moveToNext()) {
    ...
  }
}

एसक्यूएल में यूनीक वैल्यू की जांच करना

अगर किसी लाइन को तब तक नहीं डाला जाना चाहिए, जब तक कि टेबल में किसी कॉलम की वैल्यू यूनीक न हो, तो उस यूनीक वैल्यू को कॉलम की शर्त के तौर पर लागू करना ज़्यादा असरदार हो सकता है.

यहां दिए गए उदाहरण में, एक क्वेरी का इस्तेमाल उस लाइन की पुष्टि करने के लिए किया गया है जिसे डालना है. वहीं, दूसरी क्वेरी का इस्तेमाल लाइन को डालने के लिए किया गया है:

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT EXISTS (
        SELECT null
        FROM customers
        WHERE username = ?
    );
    """.trimIndent(),
    arrayOf(customer.username)
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        throw AddCustomerException(customer)
    }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    arrayOf(
        customer.id.toString(),
        customer.name,
        customer.username
    )
)

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM customers
      WHERE username = ?
    );
    """, new String[] { customer.username })) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    throw new AddCustomerException(customer);
  }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    new String[] {
      String.valueOf(customer.id),
      customer.name,
      customer.username,
    });

Kotlin या Java में यूनीक कंस्ट्रेंट की जांच करने के बजाय, टेबल को तय करते समय SQL में इसकी जांच की जा सकती है:

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  username TEXT UNIQUE
);

SQLite, नीचे दिए गए कोड के जैसा ही काम करता है:

CREATE TABLE Customers(...);
CREATE UNIQUE INDEX CustomersUsername ON Customers(username);

अब एक लाइन डाली जा सकती है और SQLite को यह जांच करने दिया जा सकता है कि कंस्ट्रेंट सही है या नहीं:

Kotlin

try {
    db.execSql(
        "INSERT INTO Customers VALUES (?, ?, ?)",
        arrayOf(customer.id.toString(), customer.name, customer.username)
    )
} catch(e: SQLiteConstraintException) {
    throw AddCustomerException(customer, e)
}

Java

try {
  db.execSQL(
      "INSERT INTO Customers VALUES (?, ?, ?)",
      new String[] {
        String.valueOf(customer.id),
        customer.name,
        customer.username,
      });
} catch (SQLiteConstraintException e) {
  throw new AddCustomerException(customer, e);
}

SQLite में, एक से ज़्यादा कॉलम वाले यूनीक इंडेक्स इस्तेमाल किए जा सकते हैं:

CREATE TABLE table(...);
CREATE UNIQUE INDEX unique_table ON table(column1, column2, ...);

SQLite, Kotlin या Java कोड की तुलना में, कम समय में और कम ओवरहेड के साथ कंस्ट्रेंट की पुष्टि करता है. ऐप्लिकेशन कोड के बजाय SQLite का इस्तेमाल करना सबसे सही तरीका है.

एक ही लेन-देन में कई आइटम एक साथ जोड़ना

लेन-देन में कई कार्रवाइयां शामिल होती हैं. इससे न सिर्फ़ बेहतर तरीके से काम करने में मदद मिलती है, बल्कि सटीक नतीजे भी मिलते हैं. डेटा में एकरूपता बनाए रखने और परफ़ॉर्मेंस को बेहतर बनाने के लिए, एक साथ कई आइटम जोड़े जा सकते हैं:

Kotlin

db.beginTransaction()
try {
    customers.forEach { customer ->
        db.execSql(
            "INSERT INTO Customers VALUES (?, ?, ...)",
            arrayOf(customer.id.toString(), customer.name, ...)
        )
    }
} finally {
    db.endTransaction()
}

Java

db.beginTransaction();
try {
  for (customer : Customers) {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ...)",
        new String[] {
          String.valueOf(customer.id),
          customer.name,
          ...
        });
  }
} finally {
  db.endTransaction()
}

समस्या हल करने वाले टूल का इस्तेमाल करना

SQLite, परफ़ॉर्मेंस को मेज़र करने में मदद करने के लिए, समस्या हल करने वाले ये टूल उपलब्ध कराता है.

SQLite के इंटरैक्टिव प्रॉम्प्ट का इस्तेमाल करना

क्वेरी चलाने और सीखने के लिए, अपनी मशीन पर SQLite चलाएं. Android प्लैटफ़ॉर्म के अलग-अलग वर्शन, SQLite के अलग-अलग वर्शन का इस्तेमाल करते हैं. Android डिवाइस पर मौजूद इंजन का इस्तेमाल करने के लिए, adb shell का इस्तेमाल करें. इसके बाद, टारगेट डिवाइस पर sqlite3 चलाएं.

SQLite से क्वेरी के लिए समय का अनुरोध किया जा सकता है:

sqlite> .timer on
sqlite> SELECT ...
Run Time: real ... user ... sys ...

EXPLAIN QUERY PLAN

SQLite से यह पूछा जा सकता है कि वह किसी क्वेरी का जवाब कैसे देगा. इसके लिए, EXPLAIN QUERY PLAN का इस्तेमाल करें:

sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SCAN Customers

पिछले उदाहरण में, पेरिस के सभी ग्राहकों को ढूंढने के लिए, इंडेक्स के बिना पूरी टेबल स्कैन करने की ज़रूरत होती है. इसे लीनियर कॉम्प्लेक्सिटी कहा जाता है. SQLite को सभी पंक्तियों को पढ़ना होगा और सिर्फ़ उन पंक्तियों को रखना होगा जो पेरिस में रहने वाले खरीदारों से मेल खाती हैं. इस समस्या को ठीक करने के लिए, इंडेक्स जोड़ा जा सकता है:

sqlite> CREATE INDEX Idx1 ON Customers(city);
sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SEARCH test USING INDEX Idx1 (city=?

अगर इंटरैक्टिव शेल का इस्तेमाल किया जा रहा है, तो SQLite से हमेशा क्वेरी प्लान के बारे में बताने के लिए कहा जा सकता है:

sqlite> .eqp on

ज़्यादा जानकारी के लिए, क्वेरी की योजना बनाना लेख पढ़ें.

SQLite ऐनालाइज़र

SQLite, परफ़ॉर्मेंस से जुड़ी समस्याओं को हल करने के लिए, sqlite3_analyzer कमांड-लाइन इंटरफ़ेस (सीएलआई) उपलब्ध कराता है. इसका इस्तेमाल करके, ज़्यादा जानकारी डंप की जा सकती है. इसे इंस्टॉल करने के लिए, SQLite के डाउनलोड पेज पर जाएं.

विश्लेषण के लिए, टारगेट डिवाइस से डेटाबेस फ़ाइल को अपने वर्कस्टेशन पर डाउनलोड करने के लिए, adb pull का इस्तेमाल किया जा सकता है:

adb pull /data/data/<app_package_name>/databases/<db_name>.db

SQLite ब्राउज़र

SQLite के डाउनलोड पेज पर जाकर, GUI टूल SQLite Browser भी इंस्टॉल किया जा सकता है.

Android लॉगिंग

Android, SQLite क्वेरी के लिए समय तय करता है और उन्हें आपके लिए लॉग करता है:

# Enable query time logging
$ adb shell setprop log.tag.SQLiteTime VERBOSE
# Disable query time logging
$ adb shell setprop log.tag.SQLiteTime ERROR

Perfetto ट्रेसिंग

Perfetto को कॉन्फ़िगर करते समय, अलग-अलग क्वेरी के लिए ट्रैक शामिल करने के लिए, यह कोड जोड़ा जा सकता है:

data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      atrace_categories: "database"
    }
  }
}

dumpsys meminfo

adb shell dumpsys meminfo <package-name>, ऐप्लिकेशन की मेमोरी के इस्तेमाल से जुड़े आंकड़े प्रिंट करेगा. इसमें SQLite मेमोरी के बारे में कुछ जानकारी भी शामिल होगी. उदाहरण के लिए, यह डेवलपर के डिवाइस पर adb shell dumpsys meminfo com.google.android.gms.persistent के आउटपुट से लिया गया है:

DATABASES
      pgsz     dbsz   Lookaside(b) cache hits cache misses cache size  Dbname
PER CONNECTION STATS
         4       52             45     8    41     6  /data/user/10/com.google.android.gms/databases/gaia-discovery
         4        8                    0     0     0    (attached) temp
         4       52             56     5    23     6  /data/user/10/com.google.android.gms/databases/gaia-discovery (1)
         4      252             95   233   124    12  /data/user_de/10/com.google.android.gms/databases/phenotype.db
         4        8                    0     0     0    (attached) temp
         4      252             17     0    17     1  /data/user_de/10/com.google.android.gms/databases/phenotype.db (1)
         4     9280            105 103169 69805    25  /data/user/10/com.google.android.gms/databases/phenotype.db
         4       20                    0     0     0    (attached) temp
         4     9280            108 13877  6394    25  /data/user/10/com.google.android.gms/databases/phenotype.db (2)
         4        8                    0     0     0    (attached) temp
         4     9280            105 12548  5519    25  /data/user/10/com.google.android.gms/databases/phenotype.db (3)
         4        8                    0     0     0    (attached) temp
         4     9280            107 18328  7886    25  /data/user/10/com.google.android.gms/databases/phenotype.db (1)
         4        8                    0     0     0    (attached) temp
         4       36             51   156    29     5  /data/user/10/com.google.android.gms/databases/mobstore_gc_db_v0
         4       36             97    47    27    10  /data/user/10/com.google.android.gms/databases/context_feature_default.db
         4       36             56     3    16     4  /data/user/10/com.google.android.gms/databases/context_feature_default.db (2)
         4      300             40  2111    24     5  /data/user/10/com.google.android.gms/databases/gservices.db
         4      300             39     3    17     4  /data/user/10/com.google.android.gms/databases/gservices.db (1)
         4       20             17     0    14     1  /data/user/10/com.google.android.gms/databases/gms.notifications.db
         4       20             33     1    15     2  /data/user/10/com.google.android.gms/databases/gms.notifications.db (1)
         4      120             40   143   163     4  /data/user/10/com.google.android.gms/databases/android_pay
         4      120            123    86    32    19  /data/user/10/com.google.android.gms/databases/android_pay (1)
         4       28             33     4    17     3  /data/user/10/com.google.android.gms/databases/googlesettings.db
POOL STATS
     cache hits  cache misses    cache size  Dbname
             13            68            81  /data/user/10/com.google.android.gms/databases/gaia-discovery
            233           145           378  /data/user_de/10/com.google.android.gms/databases/phenotype.db
         147921         89616        237537  /data/user/10/com.google.android.gms/databases/phenotype.db
            156            30           186  /data/user/10/com.google.android.gms/databases/mobstore_gc_db_v0
             50            57           107  /data/user/10/com.google.android.gms/databases/context_feature_default.db
           2114            43          2157  /data/user/10/com.google.android.gms/databases/gservices.db
              1            31            32  /data/user/10/com.google.android.gms/databases/gms.notifications.db
            229           197           426  /data/user/10/com.google.android.gms/databases/android_pay
              4            18            22  /data/user/10/com.google.android.gms/databases/googlesettings.db

DATABASES में आपको यह जानकारी मिलेगी:

  • pgsz: डेटाबेस के एक पेज का साइज़, केबी में.
  • dbsz: पूरे डेटाबेस का साइज़, पेजों में. केबी में साइज़ पाने के लिए, pgsz को dbsz से गुणा करें.
  • Lookaside(b): हर कनेक्शन के लिए, SQLite लुकसाइड बफ़र को असाइन की गई मेमोरी. यह बाइट में होती है. ये आम तौर पर बहुत छोटे होते हैं.
  • cache hits: SQLite, डेटाबेस पेजों की कैश मेमोरी बनाए रखता है. यह पेज कैश मेमोरी के हिट की संख्या है.
  • cache misses: पेज की कैश मेमोरी में मौजूद नहीं होने वाले पेजों की संख्या (गिनती).
  • cache size: कैश मेमोरी में मौजूद पेजों की संख्या (गिनती). केबी में साइज़ पाने के लिए, इस संख्या को pgsz से गुणा करें.
  • Dbname: DB फ़ाइल का पाथ. हमारे उदाहरण में, कुछ डीबी के नाम के आगे (1) या कोई अन्य नंबर जोड़ा गया है. इससे पता चलता है कि एक ही डेटाबेस से एक से ज़्यादा कनेक्शन हैं. आंकड़ों को हर कनेक्शन के हिसाब से ट्रैक किया जाता है.

POOL STATS में आपको यह जानकारी मिलेगी:

  • cache hits: SQLite, तैयार किए गए स्टेटमेंट को कैश मेमोरी में सेव करता है. साथ ही, क्वेरी चलाने के दौरान उन्हें फिर से इस्तेमाल करने की कोशिश करता है, ताकि एसक्यूएल स्टेटमेंट को कंपाइल करने में कम मेहनत और मेमोरी का इस्तेमाल हो. यह स्टेटमेंट कैश मेमोरी में मौजूद डेटा के इस्तेमाल की संख्या है.
  • cache misses: स्टेटमेंट कैश मेमोरी में मौजूद नहीं है (गिनती).
  • cache size: Android 17 से शुरू होने वाले वर्शन में, इससे कैश मेमोरी में मौजूद तैयार स्टेटमेंट की कुल संख्या का पता चलता है. पिछले वर्शन में, यह वैल्यू अन्य दो कॉलम में दी गई हिट और मिस की संख्या के बराबर होती है. साथ ही, यह कैश मेमोरी के साइज़ को नहीं दिखाती है.