7. Testing the effectiveness of different Android Tools in detecting performance issues caused by data fetch from Room Db without indexing

9 min readDec 18, 2022

7.1 Introduction

Apps which has a lot of structured data handling use persistent on-device DB storage a lot. Database queries are one of the most commonly used data-loading operations used in Android.

Android uses SQLite database which is an open-source SQL on-device database. As a result, running inefficient queries to achieve the same task can have a lot of impact on UI rendering performance if SQLite queries are running on the main thread. As part of this test, we will test the inefficiency of fetching calls on a particular non-indexed column in an SQLite table.

7.2 Code Implementation of Test

As part of testing the effectiveness of different tools in UI rendering performance degradation due to data loading in Android, we created a test application where we fetch data from a non-indexed column of a table in the Room Sqlite database[21]. This table consists of 10000 rows of person entity. 1000 fetch queries are made on a nonindexed column of the table with a name stored at the 9800th row. This operation is quite expensive and blocks the main thread for a considerable period of time. To verify that the tool test results are correct in pinpointing the real cause of UI jank in this case( which is an expensive data loading call on the main thread), we ran the same tools with the same application but with indexing done on fetch column in Room Database[22].

The DataOperationsActivity ( Code 33 ) consists of a gif of an animated dancing cartoon. At the launch of the activity, the animation is clearly visible smoothly. When a user clicks on ‘Fetch Person By Name’ on the screen, 1000 SQL fetch call is made on the Person table on the firstName column. In one case data fetch happens with no index on firstName column and in another case data fetch happens using the index on firstName column. When data fetch without an index is happening, the main thread is completely occupied with data fetch calls and the animation is stalled momentarily as the animation also runs on the main thread. When data fetch with index is happening, the data fetch completes very quickly and the animation is not stalled and there is no UI jank. In the next sections, we will apply different tools to check their effectiveness in detecting UI jank caused by data loading from DB

Figure 135. Image demonstrating DataOperationsActivity UI

7.2.1 DataOperationsActivity.java

package com.example.perforamancetest;
import static com.example.perforamancetest.TestApplication.FIRST_NAME;
import static com.example.perforamancetest.TestApplication.LAST_NAME;
import android.app.Activity;
import android.os.Bundle;
import android.webkit.WebView;
import android.widget.TextView;
import com.example.perforamancetest.data.Person;
import com.example.perforamancetest.data.PersonDao;
import com.example.perforamancetest.data.PersonDataBase;
import com.example.performancetest.R;

/**
* Demonstration Activity to present aview showing overlapping stack of cards. This will demonstrate difference in UI rendering performance
* for both View with extra drawing and another with clipping to avoid drawing areas of the screen which are not visible
*/
public class DataOperationsActivity extends Activity {
 @Override
 public void onCreate(Bundle savedInstanceState) {
 super.onCreate(savedInstanceState);
 setContentView(R.layout.activity_db_operations);
 WebView webView = (WebView) findViewById(R.id.webview);
 webView.getSettings().setUseWideViewPort(true);
 webView.getSettings().setLoadWithOverviewMode(true);
 webView.loadUrl("file:///android_asset/dancing_cartoon.gif");
 PersonDataBase personDataBase =((TestApplication)getApplication()).getPersonDataBase();
 PersonDao personDao = personDataBase.personDao();
 TextView textView = findViewById(R.id.tv_name);
 findViewById(R.id.btn_get_data).setOnClickListener(v -> {
 textView.setText("In Progress");
 for( int index =0; index<1000; index++){
 Person person = personDao.findByFirstName(FIRST_NAME);
 textView.setText(person.firstName + " " + person.lastName);
 }
 textView.setText("Done");
 });
 }
}

Code 33. DataOperationsActivity.java

7.2.2 activity_db_operations.xml

<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
 xmlns:app="http://schemas.android.com/apk/res-auto"
 xmlns:tools="http://schemas.android.com/tools"
 android:id="@+id/activity_droid_cards_container"
 android:layout_width="match_parent"
 android:layout_height="match_parent"
 android:paddingLeft="@dimen/activity_horizontal_margin"
 android:paddingTop="@dimen/activity_vertical_margin"
 android:paddingRight="@dimen/activity_horizontal_margin"
 android:paddingBottom="@dimen/activity_vertical_margin"
 tools:context="com.example.perforamancetest.DataOperationsActivity"
 tools:ignore="MissingDefaultResource">
 <Button
 android:id="@+id/btn_get_data"
 android:layout_width="wrap_content"
 android:layout_height="wrap_content"
 android:layout_marginTop="32dp"
 app:layout_constraintEnd_toEndOf="parent"
 app:layout_constraintStart_toStartOf="parent"
 app:layout_constraintTop_toTopOf="parent"
 android:text="Fetch Data using FirstName"/>

 <TextView
 android:id="@+id/tv_name"
 android:layout_width="wrap_content"
 android:layout_height="wrap_content"
 android:layout_marginTop="32dp"
 app:layout_constraintEnd_toEndOf="parent"
 app:layout_constraintStart_toStartOf="parent"
 app:layout_constraintTop_toBottomOf="@id/btn_get_data"
 android:hint="data will be displayed here "
 />
 <WebView
 android:layout_width="300dp"
 android:layout_height="300dp"
 android:id="@+id/webview"
 android:layout_gravity="center_horizontal"
 app:layout_constraintTop_toBottomOf="@id/tv_name"
 app:layout_constraintStart_toStartOf="parent"
 app:layout_constraintEnd_toEndOf="parent"
 android:layout_marginTop="32dp"/>
</androidx.constraintlayout.widget.ConstraintLayout>

Code 34. activity_db_operations.xml

7.2.3 Person.java

package com.example.perforamancetest.data;
import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.Index;
import androidx.room.PrimaryKey;
@Entity
 (indices = {@Index("first_name")})
public class Person {
 @PrimaryKey
 public int uid;
 @ColumnInfo(name = "first_name")
 public String firstName;
 @ColumnInfo(name = "last_name")
 public String lastName;
}

Code 35. Person.java

7.2.4 PersonDataBase.java

package com.example.perforamancetest.data;
import androidx.room.Database;
import androidx.room.RoomDatabase;

@Database(entities = {Person.class}, version = 1)
public abstract class PersonDataBase extends RoomDatabase {
 public abstract PersonDao personDao();
}

Code 36. PersonDataBase.java

7.2.5 PersonDao.java

package com.example.perforamancetest.data;
import androidx.room.Dao;
import androidx.room.Delete;
import androidx.room.Insert;
import androidx.room.OnConflictStrategy;
import androidx.room.Query;
import java.util.List;
@Dao
public interface PersonDao {
 @Query("SELECT * FROM Person")
 List<Person> getAll();
 @Query("SELECT * FROM Person WHERE uid IN (:userIds)")
 List<Person> loadAllByIds(int[] userIds);
 @Query("SELECT * FROM Person WHERE first_name = :first LIMIT 1")
 Person findByFirstName(String first);
 @Query("SELECT * FROM Person WHERE uid = :id")
 Person findById(int id);
 @Insert(onConflict = OnConflictStrategy.REPLACE) // or OnConflictStrategy.IGNORE
 void insert(Person user);
 @Delete
 void delete(Person user);
}

Code 37. PersonDao.java

7.2.5 TestApplication.java

package com.example.perforamancetest;
import androidx.room.Room;
import com.example.perforamancetest.data.Person;
import com.example.perforamancetest.data.PersonDataBase;
public class TestApplication extends android.app.Application {
 PersonDataBase personDataBase ;
 public static String FIRST_NAME = "Abhishek";
 public static String LAST_NAME = "Luthra";
 @Override
 public void onCreate() {
 super.onCreate();
 personDataBase = Room.databaseBuilder(getApplicationContext(),
 PersonDataBase.class, "personDatabase").allowMainThreadQueries().build();
 addDataToDb();
 }
 private void addDataToDb(){
 for( int index =0; index<10000; index++){
 Person person = new Person();
 person.uid = index;
 if(index == 9800){
 person.firstName = FIRST_NAME;
 person.lastName = LAST_NAME;
 }
 else{
 person.firstName = "Ram" + Math.random();
 person.lastName ="Gupta" + Math.random();
 }
 personDataBase.personDao().insert(person);
 }
 }
 public PersonDataBase getPersonDataBase(){
 return personDataBase;
 }
}

7.3 Tool Test

7.3.1 LayoutInspector Tool Test

7.3.1.1 LayoutInspector on SQL fetch on non-indexed columns

Figure 136. LayoutInspector result on DataOperationsActivity with SQLite DB fetch calls on non-indexed columns of the table with large data

7.3.1.2 LayoutInspector on SQL fetch on indexed columns

Figure 137. LayoutInspector result on DataOperationsActivity with SQLite DB fetch calls on indexed columns of the table with large data

7.3.1.3 LayoutInspector Tool Test Result

It is clearly visible from the component tree of Figure 136 and Figure 137 that the layout hierarchy for both is the same. This indicates LayoutInspector is ineffective in detecting any UI performance degradation due to SQLite fetch call on a non-indexed column

7.3.2 Debug GPU Overdraw Tool Test

7.3.2.1 Debug GPU Overdraw on SQL fetch on non-indexed columns

Figure 138. Debug GPU overdraw result on DataOperationsActivity with SQLite DB fetch calls on non-indexed columns of the table with large data

7.3.2.2 Debug GPU Overdraw on SQL fetch on indexed columns

Figure 139. Debug GPU overdraw result on DataOperationsActivity with SQLite DB fetch calls on indexed columns of the table with large data

7.3.2.3 Debug GPU Overdraw Tool Test Result

It is clearly visible from the overdraw areas of DataOperationsActivity with SQLite DB fetch calls with and without indexing on searched columns, that there is no difference in results of applying Debug GPU on both Ui. This proves that Debug GPU overdraw tool is ineffective in detecting performance degradation due to SQLite DB fetch calls on non-indexed columns of tables with large data.

7.3.3 Profile GPU rendering Tool Test

7.3.3.1 Profile GPU rendering on SQL fetch on non-indexed columns

Figure 140. Profile GPU rendering result for DataOperaionsActivity with SQLite DB fetch calls on non-indexed columns of the table with large data

7.3.3.2 Profile GPU rendering on SQL fetch on indexed columns

Figure 141. Profile GPU rendering result with SQLite DB fetch calls on indexed columns of the table with large data

7.3.2.3 Profil GPU rendering Tool Test Result

It is clearly visible from the rendering bars for SQLite DB fetch call on non-indexed columns of a table with large data( Figure 140) and for SQLite db fetch call on indexed columns of the table with large data(Figure 141) that the rendering bars fetch calls on indexed columns are smaller in height than rendering bars for fetch calls on non-indexed columns. The increase in height of rendering bars is primarily because of the green line which represents the time that the app spends executing operations in between two frames. This operation is DB fetch call on non-indexed column of the table with quite a lot of data.

The above indicates that the profile GPU rendering is effective in detecting the rendering performance issues caused by SQLite DB fetch calls on non-indexed columns of tables with large data.

7.3.4 Show View Updates Tool Test

7.3.4.1 Show View Updates for SQL fetch without indexing

The result of applying the Show View Updates developer option on SQLite DB fetch call on non-indexed columns of the table with large data[35]

7.3.4.2 Show View Updates for SQL fetch with indexing

The result of applying the Show View Updates developer option on SQLite DB fetch call on indexed columns of table with large data[36]

7.3.4.3 Show View Updates Tool Test Result

It is clearly visible from the video result on non indexed columns of table with large data[35] and on indexed columns of table with large data[36] that there is a noticeable difference to the human eye in the results of applying this tool. This indicates that the Show view updates tool is effective in detecting the rendering performance issues caused by animation without hardware layers.

7.3.5 Android CPU Profiler Tool Test

As part of this test, we will click on ‘Fetch data using First Name’ button to start fetch data from the SQLite DB. The state at the end of the test looks like

Figure 142. The end state of clicking on ‘FETCH DATA USING FIRST NAME’ button.

7.3.5.1 Android CPU Profiler tool test for SQL fetch on non-indexed columns
7.3.5.1.1 System Trace CPU Profiler output

Figure 143. The CPU profiler system trace with SQLite DB fetch calls on non-indexed columns of table with large data

Figure 143 presents that most of the frames are janky for SQLite DB fetch calls on non-indexed columns of tables with large data

Figure 144. The CPU profiler system trace — All Frame visualization with SQLite DB fetch calls on non-indexed columns of the table with large data

Figure 145. The CPU profiler system trace-jank visualization with SQLite DB fetch calls on non-indexed columns of table with large data

7.3.5.2 Android CPU Profiler tool test for SQL fetch on indexed columns
7.3.5.2.1 System Trace for SQL fetch on indexed columns

Figure 146. The CPU profiler system trace visualization with SQLite DB fetch calls on indexed columns of table with large data

Figure 147. The CPU profiler system trace — All Frame visualization with SQLite DB fetch calls on indexed columns of the table with large data

Figure 148. The CPU profiler system trace — Janky Frame visualization with SQLite DB fetch calls on indexed columns of the table with large data

From Figure 148, we see that there is no UI jank when sql fetch call is made on indexed columns.

7.3.5.3 Android CPU Profiler Tool Test Result

It is clearly visible from the CPU profiler result for animation for SQLite DB fetch call on non-indexed columns of the table with large data( Figure 145) and for SQLite DB fetch call on indexed columns of the table with large data(Figure 148) that the that there is the significant reduction in the number of janks frames itself by running SQLite DB fetch call on indexed columns of the table rather than non-indexed columns. This indicates that the CPU profiler tool is effective in detecting the rendering performance issues caused by SQLite DB fetch call on indexed columns of table with large data.

This clearly shows that there is a 100 percent improvement in the number of jank frames by indexing columns if there are frequent reads from that table.