First time Cursor Operation is so slow, when query bulk data. How to solve?

Question

I have to query three table, and display the data to my customerView.

My code is like this:

Log.v(TAG, System.CurrentTimeMillis())
int len = cursor.getCount();
Log.v(TAG, System.CurrentTimeMillis())

Product[] products = new Product[len];
int i = 0;
while(cursor.moveToNext()){
    products[i] = new Product(cursor.getstring(0),.....);
}
Log.v(TAG, System.CurrentTimeMillis())

Sqlite query:

 String sql = "SELECT T1.PRODUCT_ID, CODE, SHORT_DESCRIPTION, CATEGORY_CODE,
     BRAND_CODE, FORM_CODE, DENOMINATOR, T1.PIECE_PRICE, T1.lowest_piece_price, 
     T2.sku_type, T1.master_sku " + 
 "FROM CUSTOMER_PROD_LIST_ITEMS T1 INNER JOIN PRODUCT T2 ON 

T1.PRODUCT_ID = T2.ID INNER JOIN PRODUCT_UOMS ON T2.ID = 
                                          PRODUCT_UOMS.PRODUCT_ID"+ 
"WHERE T1.VALID = 1 AND PRODUCT_UOMS.VALID = 1 AND 
   CUSTOMER_PRODUCT_LIST_ID = " + customer_pdtlist_ID + " 
ORDER BY T1.PRODUCT_ID ASC";

After my testing, if we have 1500rows in the cursor, we have to spend more than 30s to finish this line(cursor.getcount()) . If I delete this line, and use ArrayList to take place. i can find that we should spend more than 30s for Cursor.moveToNext().

So my question is why the first time cursor operation should take such long time? and how do we solve?

And this man have the same question Poor SQLite implementation? First time data access way too slow. but the answer is not working for me. by the way, I find display same 1500rows in Iphone, just need amost 3s.

thanks in advance!!

String sql = "SELECT T1.PRODUCT_ID, CODE, SHORT_DESCRIPTION, CATEGORY_CODE, BRAND_CODE, FORM_CODE, DENOMINATOR, T1.PIECE_PRICE, T1.lowest_piece_price, T2.sku_type, T1.master_sku " + "FROM CUSTOMER_PROD_LIST_ITEMS T1 INNER JOIN PRODUCT T2 ON T1.PRODUCT_ID = T2.ID INNER JOIN PRODUCT_UOMS ON T2.ID = PRODUCT_UOMS.PRODUCT_ID " + "WHERE T1.VALID = 1 AND PRODUCT_UOMS.VALID = 1 AND CUSTOMER_PRODUCT_LIST_ID = " + customer_pdtlist_ID + " ORDER BY T1.PRODUCT_ID ASC"; — Oscar.huang, Feb 15 '12 at 08:09
i guess its your query being too much exhaustive to cause this delay. If you are using this result to be shown in listview, then i guess you should use dynamic loading to fetch only few records (lets say 20) by using `LIMIT` and then load more once the list is scrolled till bottom — waqaslam, Feb 15 '12 at 08:17
i want to dispaly those data in my CustomerView which like Table, not listview. Thanks Waqas. i don't think this delay because of exhaustive sql. i can see how long should excute for this sql, it's about 3 ms. very fast. The point is read cursor. — Oscar.huang, Feb 15 '12 at 08:26
ok, guys, i have not been here for acouple days.And i found the solution that is you have to create index for your table which will improve the query speed. thanks all the same. — Oscar.huang, Feb 20 '12 at 07:45

score 15 · Answer 1 · edited Jun 20 '20 at 09:12

This is an answer to why the first operation on your cursor is so slow. When a Cursor is backed by SQLite, Android uses the sqlite C library internally and creating a Cursor is analogous to creating a prepared statement in the C library. Creating a prepared statement is cheap and it does not perform any query. Taken from the C library's documentation:

sqlite3_prepare()

This routine converts SQL text into a prepared statement object and returns a pointer to that object. This interface requires a database connection pointer created by a prior call to sqlite3_open() and a text string containing the SQL statement to be prepared. This API does not actually evaluate the SQL statement. It merely prepares the SQL statement for evaluation.

When you call moveToNext() on the Cursor, that's when the query actually gets executed. moveToNext results in a call to the sqlite3_step() function in the C library. Again, taken from the documentation:

sqlite3_step()

This routine is used to evaluate a prepared statement that has been previously created by the sqlite3_prepare() interface. The statement is evaluated up to the point where the first row of results are available. To advance to the second row of results, invoke sqlite3_step() again. Continue invoking sqlite3_step() until the statement is complete. Statements that do not return results (ex: INSERT, UPDATE, or DELETE statements) run to completion on a single call to sqlite3_step().

So creating a Cursor is done lazily and the query is only evaluated when the cursor is first moved.

To find out why the query is taking such a long time, use EXPLAIN QUERY PLAN on your query and see where the bottleneck lies. Usually it's the lack of an appropriate index.

This should be the accepted answer. Indexing decreases query time in general, but on a large database and complex search it is still possible to experience a lag on the first execution of the cursor (which this answer explains). — ılǝ, Dec 17 '13 at 01:30

score 1 · Accepted Answer · answered Aug 14 '13 at 03:38

1

ok, guys, i have not been here for acouple days.And i found the solution that is you have to create index for your table which will improve the query speed. thanks all the same

answered Aug 14 '13 at 03:38

Oscar.huang

61
1
6

First time Cursor Operation is so slow, when query bulk data. How to solve?

2 Answers2

Linked