Databases
StackOverflow2013
Postsdbo
POSQLite Database taking too much time to retrieve (or fetch some data)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLite Database taking too much time to retrieve (or fetch some data)
    primarykey
    data
    text
    <p>I have a sqlite cipher database with around 80000 records (containing 7 columns). Its currently taking too much time in searching the records. I need an optimized way of searching for 1 or more record based on some criteria (need something like indexing) so that I can reduce the searching time. Does anybody has any idea about how to proceed to achieve this? Need help urgently. :(</p> <p>EDITED: I am using sqlite cipher API because I need to store the encrypted data in sqlite database. I have set the pragma key and the encryption being used is AES - 256. I have also created the index on each table in my database. But my searching is not fast. Its taking too much time. Need to solve this ASAP.</p> <p>2nd TIME EDITED: Hey guys, I am badly stuck this time. I am making a search as soon as user clicks on a text field. To summarize the problem, let me tell you the flow.</p> <p>I have 2 text fields and a table containing ~80k records and 7 columns. When I type anything in the field1, a search is made onto the table with the content of the field1. So lets say I type 'a' in my field1, then a select query returns all the records containing 'a' letter in column1. So, the table is being searched as soon as I type the letters. Uptill now there is no problem. Its returning the records without any time delay. The query is:</p> <pre><code>SELECT DISTINCT &lt;COL1&gt; COLLATE NOCASE as &lt;COL1&gt; from &lt;TABLE_NAME&gt; where &lt;COL1&gt; like '%ab%' LIMIT 0,501 </code></pre> <p>Now, if I click on field 2, then I see the cursor on field2 after 1 secs as this query gets execute:</p> <pre><code>SELECT DISTINCT &lt;COL2&gt; COLLATE NOCASE as &lt;COL2&gt; from &lt;TABLE_NAME&gt; where &lt;COL1&gt; like '%ab%' LIMIT 0,501 </code></pre> <p>Now, the db fetching process is taking time if I type anything on field2. Here is the final query:</p> <pre><code>SELECT DISTINCT &lt;COL2&gt; COLLATE NOCASE as &lt;COL2&gt; from &lt;TABLE_NAME&gt; where &lt;COL1&gt; like '%ab%' AND &lt;COL2&gt; like '%cd%' LIMIT 0,501 </code></pre> <p>When I tried to analyse the problem, I came to know that the problem is due to DISTINCT Keyword. I have created indices on the columns as follows:</p> <pre><code>CREATE INDEX &lt;name1&gt; on &lt;TABLE_NAME&gt;(&lt;COL1&gt;) CREATE INDEX &lt;name2&gt; on &lt;TABLE_NAME&gt;(&lt;COL1&gt; COLLATE CASE) CREATE INDEX &lt;name3&gt; on &lt;TABLE_NAME&gt;(&lt;COL2&gt;) CREATE INDEX &lt;name4&gt; on &lt;TABLE_NAME&gt;(&lt;COL2&gt; COLLATE CASE) </code></pre> <p>Anyone has any idea how to make this retrieval fast.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USanshul
    1. USanshul
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COmaybe the problem not in the sqlite size, but your sql statement. you want to post your sql statement here?
      singulars
      1. POSQLite Database taking too much time to retrieve (or fetch some data)
      1. UShawshy
    2. COThe query is something like [@"select rowid from `%@` where \"Values\" = '%@'", tableName, descValue] where descValue is the col value for col "Values"
      singulars
      1. POSQLite Database taking too much time to retrieve (or fetch some data)
      1. USanshul
    3. COwhat is the returnType of the function? Always try to return the class object.
      singulars
      1. POSQLite Database taking too much time to retrieve (or fetch some data)
      1. USAnil Kothari
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload