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!!

Answer 1

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.

Answer 2

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