LongRange Developer Forum

Hi,

Our customer would like to download data to local database table for over 10,000 records(one column per each record), but it takes over 1 minutes to download it.

Is there any way to improve this download time?

Also if you know the expected size of data which they can download to local database table within one minute, could you please let us know based on your experience ?

Best Regards,
Megumi Sawada

On my iPhone 6s, I can download (and insert) 10,000 records in 40 seconds. Each record contains first name, surname, address, phone, email fields.

The actual downloading takes only around 20 seconds. The rest of the time is spent inserting the records to the SQLite database table. The actual time it takes to insert the records depends on the overall performance of the device.
Inserting records using the standard method can be time consuming as a check needs to be performed on every insert to see if the record already exists on the table (if so, update the record instead of inserting).

An alternative way, which could give better performance in your case, is to use direct SQL statements. You'd need to explicitly clear the target table first, followed by your 'INSERT' SQL statements.

You can specify SQL statements by using the "DB.SQL%N" property.

For example:

Hi Tony,

Thank you for your advice.
I have tried it but I got an error saying "DB.SQL%1 does not exist"...
Could you please let me know in more details?
Also,do I need to run "ExecSQL" operation?
Sorry for these basic questions.

Best Regards,
Megumi Sawada

My apology - the property should be 'DB.PostSend.SQL%N' (it was missing the PostSend property).

There is however another limitation that I came across while trying out this method.
The server side RPG API currently limits the property index (%N) to 10000, so in your case, with the current limit, this method wouldn't be too helpful.
However we can adjust the limit to be higher and your customer can redeploy the server side component if required.
I'd like to see first however if this method does make a difference in terms of the amount of time required to populate the 10,000 records.

Hi Tony

This is all very interesting stuff - so constructing a bunch of "postsend" SQL statements would be quicker than the standard passing down of data? I'd never considered that, and although I have used multiple PostSend statements (usually to update or delete local client side data - as a tidy up after an upload) I'd always assumed that passing SQL statements down the line it would perform worse. If its better (or no worse) then that's great

Can you clarify - when you say there is an overhead on the "standard" insert (where it checks for existing records and either performs an insert or update) I assume that only happens if the SQLite table has been defined with a key? If there's no key does it just add to the bottom of the local table? Would that be quicker that postsend SQL statements?

Cheers!

Marcus.

Hi Marcus,
Even though in my test, passing direct SQL statements currently performed better, I don't recommend this as a long term solution.
I believe the overhead of checking for existing records is only one of the reasons, there may be other factors that might have inadvertently slowed down the standard insert. We are going to have a closer look at this very soon to see if we can improve it.
I will keep everybody updated on our progress.

Hi Tony,

The customer confirmd that using direct SQL statements makes a big difference in downloading performance.
And they would like to ask you to adjust the limit to be higher, to 30,000.
Should I send enhancement request via support?

Best Regards,
Megumi Sawada

Hi Megumi,
Yes please can you log an enhancement request with LANSA support?
Thanks.