The cursor operation is so slow for the first time when data arrays are requested. How to solve?

Question

I need to query three tables and display the data in my client.

My code looks 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 request:

  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 1,500 lines in the cursor, we must spend more than 30 seconds to finish this line (cursor.getcount ()). If I delete this line and use ArrayList. I can find that we should spend more than 30 seconds on Cursor.moveToNext ().

So my question is: why should the cursor operation for the first time last so long? and how do we decide?

And this person has the same question. Bad SQLite implementation? The first data access path is too slow . but the answer does not work for me. By the way, I find the display of the same 1500s in Iphone, I just need amost 3s.

Thanks in advance!

Answer 1

OK guys, I have not been here for such days. And I found the solution you need to create an index for your table, which will improve query speed. thank you all the same

Answer 2

This answers the question why the first operation on your cursor is so slow. When Cursor is supported by SQLite, Android uses the sqlite C library internally, and creating a Cursor similar to creating a prepared statement in the C library. Creating a prepared statement is cheap and it does not perform any queries. Taken from the C library documentation:

sqlite3_prepare ()

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

When you call moveToNext() in Cursor , this is when the request is actually being executed. moveToNext sqlite3_step() function in the C library. Again, taken from the documentation:

sqlite3_step ()

This procedure is used to evaluate a prepared statement that was previously created by the sqlite3_prepare () interface. The operator is evaluated until there is a first row of results. To jump to the second row of results, call sqlite3_step () again. Keep calling sqlite3_step () until the statement completes. Statements that do not return results (for example: INSERT, UPDATE, or DELETE statements) are executed to completion with a single call to sqlite3_step ().

So, creating a cursor is lazy, and the query is evaluated only the first time the cursor is moved.

To find out why the query takes such a long time, use EXPLAIN QUERY PLAN at your request and see where the bottleneck is. Usually this is the lack of an appropriate index.