Mass Updates with the Smart Client

Questions, tips and tricks and techniques for scripting in Servoy

Mass Updates with the Smart Client

Postby ryan.m.beasley » Mon Oct 19, 2020 10:17 pm

Hello,
I've a couple things for this. Pausing the autosave option on the databaseManager then trying to insert the data into the the database in a separate table then running databaseManager.savedata (after which I'd run a stored procedure to move the data into the correct table and update the data there). I've also tried the foundsetUpdater method by extracting the data into two arrays and putting those in the foundsetUpdater function for the fields that need updating. In both situations I'm updating 1602 different entries in the database and it's taking about 10-15 minutes to do this which is very excessive. Is there some other way of doing this that I'm missing to make this quick? It looks like no matter what when the transactions get committed, it's doing them one at a time.
Thanks
ryan.m.beasley
 
Posts: 57
Joined: Wed Oct 10, 2018 8:51 pm

Re: Mass Updates with the Smart Client

Postby Joas » Tue Oct 20, 2020 6:31 pm

It will be quicker to use the rawSQL plugin. With that you can just execute sql statements.
You can use update or insert statements, but if you need to insert a lot of records you can for example also use the COPY statement on postgres (https://www.postgresql.org/docs/13/sql-copy.html), other databases will have something similar.

Make sure to call plugins.rawSQL.notifyDataChange() or plugins.rawSQL.flushAllClientsCache() afterwards to make sure that the clients will see the changes.
Joas de Haan
Yield Software Development: Need help on your project?
yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 825
Joined: Mon Mar 20, 2006 4:07 pm
Location: Amersfoort, NL

Re: Mass Updates with the Smart Client

Postby ROCLASI » Wed Oct 21, 2020 10:06 am

Also be aware that if you use PostgreSQL, unless you started a db transaction yourself, every SQL statement is implicitly wrapped inside a db transaction. This is because of how Multi Version Concurrency Control (MVCC) works in PostgreSQL.
So if you do a lot of inserts/updates then you can reduce the overhead of this implicit transaction by starting your own database transaction at the beginning and commit at the end of all your inserts/updates.
This applies to both the rawSQL plugin approach as when using the FoundSet(updater).

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5368
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Mass Updates with the Smart Client

Postby ROCLASI » Wed Oct 21, 2020 10:19 am

Hi Ryan,

ryan.m.beasley wrote:In both situations I'm updating 1602 different entries in the database and it's taking about 10-15 minutes to do this which is very excessive.

If my math is correct then it takes between 374 and 561 milliseconds per record to be inserted. That is pretty excessive.
You say this is done via a smartclient, is that running close to the server? It's best that you run these bulk operations as close to the server as possible to reduce the roundtrip time. Preferably with a headless client.

Also is this a solution with a lot of users? And is that table you are filling used/seen by a lot of users? If so you might get some slowdown by the databroadcast as well. Using the rawSQL + notify/flush at the end will help with that.

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5368
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Mass Updates with the Smart Client

Postby ryan.m.beasley » Wed Oct 21, 2020 8:51 pm

The application server is hosted on a completely separate server than the database server; this is generally how we do these things, so there's not really a way to shrink that distance. Our database server is MSSQL Server 2016. We generally have 25-30 concurrent users on the system at a time, and the table involved will likely be used by a lot of them. Will I need to call databaseManager.startTransaction() then run my rawsql code then call databaseManager.commitTransaction() at the end?
ryan.m.beasley
 
Posts: 57
Joined: Wed Oct 10, 2018 8:51 pm

Re: Mass Updates with the Smart Client

Postby ryan.m.beasley » Wed Oct 21, 2020 9:39 pm

Update: using rawsql halved the time it takes to update everything which is a little more bearable.

databaseManager.startTransaction();
for (var i = 6; i < data.length; i++) {
plugins.rawSQL.executeSQL(
'server',
updatestatement
);
}
databaseManager.commitTransaction();
plugins.rawSQL.notifyDataChange('server','table',pks,SQL_ACTION_TYPES.UPDATE_ACTION);
plugins.rawSQL.flushAllClientsCache('server','table');

Just to clarify, this is that you were talking about doing right?
ryan.m.beasley
 
Posts: 57
Joined: Wed Oct 10, 2018 8:51 pm

Re: Mass Updates with the Smart Client

Postby ROCLASI » Thu Oct 22, 2020 1:28 am

ryan.m.beasley wrote:The application server is hosted on a completely separate server than the database server; this is generally how we do these things, so there's not really a way to shrink that distance. Our database server is MSSQL Server 2016. We generally have 25-30 concurrent users on the system at a time, and the table involved will likely be used by a lot of them.

I was more referring to the distance of the users (servoy smartclient) to the application server, as the update code runs in those clients.
If the update code runs on the application server itself (via headless client) it doesn't have the roundtrip from the client to the server.

ryan.m.beasley wrote:Will I need to call databaseManager.startTransaction() then run my rawsql code then call databaseManager.commitTransaction() at the end?

Correct.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5368
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Mass Updates with the Smart Client

Postby ROCLASI » Thu Oct 22, 2020 1:33 am

ryan.m.beasley wrote:Just to clarify, this is that you were talking about doing right?

Correct.

One thing though. You would use either notifyDataChange() or flushAllClientsCache(), not both.
flushAllClientsCache() invalidates the cache of the whole table while notifyDataChange() invalidates the cache of only the records you specify.

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5368
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Mass Updates with the Smart Client

Postby swingman » Thu Oct 22, 2020 9:58 am

This sounds very slow. Some ideas of where to look, but you may already have tried these:

- Any table-events that may be slowing you down? Cascading table events touching multiple tables?
- Do the inserts trigger any calculations that cause queries that can be speeded up by adding indexes?
- Any database triggers in PostgreSQL itself that may be slowing you down?
- When working through the data, do you use foundsets (faster) or controllers (slower)?
- Check the queries that the Servoy server database performance logs to see if there is any that are slow.
- Any issues in PostgreSQL's own logs?
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1344
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: Mass Updates with the Smart Client

Postby ROCLASI » Thu Oct 22, 2020 10:09 am

Hi Ryan,

Christian is making some good points.

Also, where is this updated data coming from? From the same database?
Perhaps you should do it all in SQL to begin with instead of pulling it over the wire to push it back in again.
This thread from 10 years ago might give you some ideas: viewtopic.php?f=4&t=14006

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5368
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Mass Updates with the Smart Client

Postby sbutler » Thu Oct 22, 2020 8:49 pm

You really never want to perform batch data updates or inserts through a remote data tool (like Servoy). Some suggestions

Any batch data changes need to happen locally on the Postgres server itself. If the data is coming from a third-party source (like a web service or something) that Servoy is retrieving, then write the data to a CSV file and move the file to a location that the Postgres server can access it. Share a folder like //postgres-server/imports, FTP it, whatever. Then write a Postgres function called something like "raw_import" and you can call via raw sql, like: SELECT raw_import(fileName) and let Postgres do the work locally.
The raw_import function would look something like this (pseudo code):

Code: Select all
--create the temp table that matches your CSV data
CREATE TEMP TABLE tmp_x (col1 text, col2 text);

--COPY the csv into the temp table
COPY tmp_x FROM '/absolutePath/to/$fileName (FORMAT csv);

--Update the real table
UPDATE realTable
SET    whatever= tmp_x.col2
FROM   tmp_x
WHERE  tbl.id = tmp_x.col1

--get rid of the temp table
DROP TABLE tmp_x;


With an approach like that, everything happens directly on the Postgres server, so it will be much faster.

If the data you are updating isn't from a third-party source, and is instead derived data from existing Postgres data, then you should just be able to write a Postgres function to do the whole thing.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 712
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH


Return to Methods

Who is online

Users browsing this forum: No registered users and 4 guests