Hi All,
I have an app which is bringing data back from an SQL server to update local tables (SQLite) so that the app can be used offline. I have about 20 tables that need updating when the app starts, and I have been using a sleep statement in between each one to reduce the occurrences of the job done event being overwritten between statements.
I was wondering if it was possible to speed things up as I have so many tables to update, currently it takes over 30 seconds to complete. Admittedly it is in the background, but the chances of a table being updated when in use is a worry.
Here is how I am calling the events... This is just a quarter of the calls.
Then the Refresh Static does the following....
And in the job done, I call the update for the sqlite database where it drops the table and recreates, and then adds the records...
Which I had to split as the sub was getting too big.
It seems to work but feels clunky, I don't know how to call the event, make it pause until the SQLite table is up to date and then immediately move onto the next statement, so an help would be greatly appreciated.
David
I have an app which is bringing data back from an SQL server to update local tables (SQLite) so that the app can be used offline. I have about 20 tables that need updating when the app starts, and I have been using a sleep statement in between each one to reduce the occurrences of the job done event being overwritten between statements.
I was wondering if it was possible to speed things up as I have so many tables to update, currently it takes over 30 seconds to complete. Admittedly it is in the background, but the chances of a table being updated when in use is a worry.
Here is how I am calling the events... This is just a quarter of the calls.
B4X:
Sub UpdateStaticData_RA_Dynamic
Try
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","aqr")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","cli")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","con")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","eqt")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","nmt")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","bio")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","cls")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","haz")
Sleep(1000)
'CallSub2(Me,"RefreshStaticDataDB","tal")
'CallSub2(Me,"RefreshStaticDataDB","tir")
CallSub2(Me,"RefreshStaticDataDB","tco")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","tsp")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","stt")
Sleep(1000)
CallSub2(Me,"RefreshStaticDataDB","ppe")
Catch
Log(LastException)
End TryThen the Refresh Static does the following....
B4X:
If strSection="aqr" Then
Dim cmd As DBCommand = CreateCommandIn("select_static_aqr_34567", Null)
Wait For (req.ExecuteQuery(cmd, 0, "static_aqr")) JobDone(j As HttpJob)
End If
If strSection="cli" Then
Dim cmd As DBCommand = CreateCommandIn("select_static_cli_34567", Null)
Wait For (req.ExecuteQuery(cmd, 0, "static_cli")) JobDone(j As HttpJob)
End If
If strSection="con" Then
Dim cmd As DBCommand = CreateCommandIn("select_static_con_34567", Null)
Wait For (req.ExecuteQuery(cmd, 0, "static_con")) JobDone(j As HttpJob)
End If
..... ETCAnd in the job done, I call the update for the sqlite database where it drops the table and recreates, and then adds the records...
B4X:
else if sf.Left(Job.Tag,7)="static_" Then
CallSub2(Me,"JobDoneStat",Job)
else if sf.Left(Job.Tag,7)="static-" Then
CallSub2(Me,"JobDoneJob",Job)Which I had to split as the sub was getting too big.
B4X:
reqManager.HandleJobAsync(Job, "req")
Wait For (reqManager) req_Result(res As DBResult)
If res.Tag="static-jobs" Then
'work with result
Dim m As Map
Dim listofmaps As List
If Job.Success Then
Dim m As Map
Dim listofmaps As List
If res.Rows.Size>0 Then
'We know we have data, remove the job data table and recreate
'SQLJobs.ExecNonQuery("Delete from job")
ChkndropJob("job")
'If SQLJobs.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='job'") = 1 Then
' strSQL = "DROP TABLE 'job'"
' SQLJobs.ExecNonQuery(strSQL)
'End If
strSQL ="CREATE TABLE 'job' ( `job_ID` INTEGER NOT NULL, `job_type` INTEGER NOT NULL, `job_status_ID` INTEGER NOT NULL, `job_status` TEXT, `job_object_type_ID` INTEGER NOT NULL, `job_object_ID` INTEGER, `job_object_name` TEXT, `job_allocated_ID` INTEGER, `job_allocated` TEXT, `job_department_ID` INTEGER, `job_department` TEXT, `job_source_ID` INTEGER, `job_source` TEXT, `job_short_desc` TEXT, `job_long_desc` TEXT, `job_created` TEXT, `job_required` TEXT, `job_question_set_ID` INTEGER, `job_warning` TEXT ,`job_depot_ID` INTEGER, PRIMARY KEY(`job_ID`) )"
SQLJobs.ExecNonQuery(strSQL)
'Add the new data
For myrows = 0 To res.Rows.Size - 1
Dim Record() As Object = res.Rows.Get(myrows)
'Here we are going to get all the data and post it into the table
m.Initialize
listofmaps.Initialize
m.Put("job_ID",Record(0))
m.Put("job_type",Record(1))
m.Put("job_status_ID",Record(2))
m.Put("job_status",Record(3))
m.Put("job_object_type_ID",Record(4))
m.Put("job_object_ID",Record(5))
m.Put("job_object_name",Record(6))
m.Put("job_allocated_ID",Record(7))
m.Put("job_allocated",Record(8))
m.Put("job_department_ID",Record(9))
m.Put("job_department",Record(10))
m.Put("job_source_ID",Record(11))
m.Put("job_source",Record(12))
m.Put("job_short_desc",Record(13))
m.Put("job_long_desc",Record(14))
m.Put("job_created",Record(15))
m.Put("job_required",Record(16))
m.Put("job_question_set_ID",Record(17))
m.Put("job_warning",Record(18))
m.Put("job_depot_ID",Record(19))
listofmaps.Add(m)
DBUtils.InsertMaps(SQLJobs,"Job",listofmaps)
Next
Else
ToastMessageShow("No Job data",False)
End If
'Job.Release
Else
ToastMessageShow("Job data could not be updated",False)
End If
else if res.Tag="static-js" Then
If Job.Success ThenIt seems to work but feels clunky, I don't know how to call the event, make it pause until the SQLite table is up to date and then immediately move onto the next statement, so an help would be greatly appreciated.
David