Why is the rawExecSQL command slower than execSQL?


#1

Hello guy,
Same script but
rawExecSQL running takes 10 seconds
execSQL running takes 1 seconds
1.Why is the rawExecSQL command slower than execSQL?
2.The rawExecSQL command runs very slowly, is there a way to improve it?

Thanks.


#2

Hello @chihung93

While we don’t have performance metrics covering those two API calls, the main difference is that rawExecSQL calls sqlite3_exec internally, whereas execSQL uses a prepared statement. We could certainly consider a refactoring of the JNI layer for rawExecSQL in the future. Do you have a specific scenario that you executed between the two for verification?


#3

“Do you have a specific scenario that you executed between the two for verification?” => Yes, of course.

I have a database of 200MB.
Now on the app will call the API get the .sql file, then run the command is available in the file.
if less than 1MB I will run all of the lines a once. ( still slower than execSQL)
if larger than 1MB, every 100 lines I will run once.

I do not know what’s in the file so I need to use rawExecSQL to make sure it runs as well.

[Update]
Ex :
File update.sql
Size : 830KB
Lines : 4478 (each line is a SQL command)
Ex of line command :
Insert into RunDirs (RunRoute,Sequence,Longitude,Latitude,StopID,TotalMileage) values (‘S02.599 16’,162,-84.3959153404632,33.5896082979238,‘S02.S02001’,16.65);
Insert into RunDirs (RunRoute,Sequence,Longitude,Latitude,StopID,TotalMileage) values (‘S02.599 16’,163,-84.3957807826493,33.5896387786798,‘S02.S02001’,16.65);

Run all of the lines with rawExecSQL command => it takes about 10s ~ 20s

Run all of the lines with execSQL command => it takes about 100 millisecond

Thanks