# speeding new records **URL:** \ **Category:** Classic Servoy\ **Created:** [April 2, 2010, 10:52am UTC](https://forum.servoy.com/t/speeding-new-records/11987 "2010-04-02T10:52:07Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [April 2, 2010, 10:52am UTC](https://forum.servoy.com/t/speeding-new-records/11987/1 "2010-04-02T10:52:07Z")
I am reading a lot of records (\>50k)from one table on one system, do a bit of change on the fly and write it back onto another system. I read a big bunch of data at once (using getDataSetByQuery) but writing is very slow since I use a loop with newrecord(). Is there a way to speed this up, apart from using a pure sql insert query with the whole bunch of data at once? I was thinking of extending the foundset or using addRow? ---
**Author:** ![lvostinar](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lvostinar/32/5248_2.png) [@lvostinar](https://forum.servoy.com/u/lvostinar)\ **Post date:** [April 6, 2010, 12:40pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/2 "2010-04-06T12:40:19Z")
> lesouef: > I am reading a lot of records (\>50k)from one table on one system, do a bit of change on the fly and write it back onto another system. > I read a big bunch of data at once (using getDataSetByQuery) but writing is very slow since I use a loop with newrecord(). Is there a way to speed this up, apart from using a pure sql insert query with the whole bunch of data at once? I was thinking of extending the foundset or using addRow? is autosave true ? you probably want it false here and save all data at once ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [April 6, 2010, 1:45pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/3 "2010-04-06T13:45:40Z")
it is “on”. just tried with off and writing at the end, the improvement is 40 recs in 12s instead of 14s. not the big change I am expecting. Is there an equivalent of a SQL INSERT DELAYED for this kind of large imports? How can I be as fast as importing from a file? (about 100 times faster) note my time measurement does not take into account the sql source response time, but start when I knows the getDataSetByQuery size. ---
**Author:** ![lvostinar](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lvostinar/32/5248_2.png) [@lvostinar](https://forum.servoy.com/u/lvostinar)\ **Post date:** [April 6, 2010, 1:50pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/4 "2010-04-06T13:50:31Z")
> lesouef: > it is “on”. just tried with off and writing at the end, the improvement is 40 recs in 12s instead of 14s. not the big change I am expecting. > Is there an equivalent of a SQL INSERT DELAYED for this kind of large imports? > How can I be as fast as importing from a file? (about 100 times faster) note my time measurement does not take into account the sql source response time, but start when I knows the getDataSetByQuery size. that is a long time… what takes that long ? I don’t think a simple newRecord would take 0.3 seconds. Maybe some query is run that takes more time ? Can you log in your code and see what statement takes more time ? ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [April 6, 2010, 3:21pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/5 "2010-04-06T15:21:09Z")
login my script? what is this? is that gonna time the duration line per line? function is attached… and I just tested to output the queried dataset to an output file, this is 100 times faster… [query.txt](https://forum.servoy.com/uploads/short-url/2jFfG6LWobHlCQIalzDgVKB57ZL.txt) (3.52 KB) ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [April 6, 2010, 3:28pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/6 "2010-04-06T15:28:09Z")
Hi Lesouf, Is this form you use the controller on visible perhaps? If so try to use a non-visible form or use foundset instead of controller. This way you don’t have delays for screen redraw. Hope this helps. ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [April 6, 2010, 4:43pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/7 "2010-04-06T16:43:21Z")
the thing is so long that I refresh the screen myself to make sure it runs. in normal condition, it refreshes once finished, so I don’t think this is the problem. the console can’t even be used realiably, it times out after a while. I’ll try foundset way, and see if any easier. actually I’d like to know what servoy uses to import from file, because this is the right way. I have tested from source to file, and it lasts 4.5ms/record, then from file to source \> 0.32ms/record while direct sql import is 0.3s/record which 42 times slower. So i’d like to create my records as import does if this is not foundset.newrecord. I’ll be back after the foundset way. ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [April 7, 2010, 7:52am UTC](https://forum.servoy.com/t/speeding-new-records/11987/8 "2010-04-07T07:52:09Z")
foundset.newrecord turns out to be an excellent improvement as long as there is no side function to slow it down like using a related record to fill one of the fields. it puts me in the import speed range. ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [April 7, 2010, 9:48am UTC](https://forum.servoy.com/t/speeding-new-records/11987/9 "2010-04-07T09:48:00Z")
```auto databaseMannager.autoSave = false; createNewRecords(); databaseManager.startTransaction(); if (databaseManager.saveData()) { databaseManager.commitTransaction(); } else { databaseManager.rollbackTransaction(); } databaseMannager.autoSave = false; ``` that should be the fastest way to create new records. The only thing is that if you really created many many many new records then maybe a saveData() and a commit/start should be done every X number of rows. ---
**Author:** ![lwjwillemsen](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lwjwillemsen/32/4987_2.png) [@lwjwillemsen](https://forum.servoy.com/u/lwjwillemsen)\ **Post date:** [April 7, 2010, 10:10am UTC](https://forum.servoy.com/t/speeding-new-records/11987/10 "2010-04-07T10:10:39Z")
> jcompagner: > > ```auto > databaseMannager.autoSave = false; > > ``` createNewRecords(); databaseManager.startTransaction(); if (databaseManager.saveData()) { databaseManager.commitTransaction(); } else { databaseManager.rollbackTransaction(); } databaseMannager.autoSave = false; > ```auto > > that should be the fastest way to create new records. > > The only thing is that if you really created many many many new records then maybe a saveData() and a commit/start should be done every X number of rows. > > ``` Hi Johan, Besides your remark above there is a great performance penalty when the foundset in which you add or update records is the foundset of a currently active and **shown** form… In our framework we do all our multi record update in a duplicateFoundSet of the active form to avoid this and the performance gain is remarkable. Regards, ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [April 7, 2010, 10:12am UTC](https://forum.servoy.com/t/speeding-new-records/11987/11 "2010-04-07T10:12:43Z")
yes thats true because then the UI must update itself after every newrecord call especially when you are also changing the selection to that new record that really shouldn’t be done. ---
**Author:** ![lwjwillemsen](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lwjwillemsen/32/4987_2.png) [@lwjwillemsen](https://forum.servoy.com/u/lwjwillemsen)\ **Post date:** [April 7, 2010, 10:26am UTC](https://forum.servoy.com/t/speeding-new-records/11987/12 "2010-04-07T10:26:54Z")
> jcompagner: > yes thats true because then the UI must update itself after every newrecord call > especially when you are also changing the selection to that new record that really shouldn’t be done. Yep, you’re right. I thought this to be useful (performance) info for the forum Servoy community… Regards, ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [April 7, 2010, 11:04am UTC](https://forum.servoy.com/t/speeding-new-records/11987/13 "2010-04-07T11:04:09Z")
to answer last 4 posts: I imports 75k records roughly in this case. I save data every 500 records to get some action on TV for the user! About using a dupped foundset, I did not do this, thinking that only controller.newrecord was refreshing, not foundset.newrecord. Seems like both are refreshing? what’s the refresh rate? every record? I don’t see anything if I don’t insert a command to do so so far, even when using controller.newrecord in a loop… ---
**Author:** ![Ruben79](https://avatars.discourse-cdn.com/v4/letter/r/b782af/32.png) [@Ruben79](https://forum.servoy.com/u/Ruben79)\ **Post date:** [February 21, 2013, 3:41pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/14 "2013-02-21T15:41:39Z")
Sorry for bumping this old thread… We are also in the process of importing a lot of data. A lot means 150k records, but now testing with a 50k subset. What we do is indeed a loop like this: ```auto var vSet = databaseManager.getFoundSet("db:/"); var vRecord; databaseManager.setAutoSave(false); databaseManager.startTransaction(); for (i = 0; i <= vAmountOfRecords; i++) { vRecord = vSet.getRecord(vSet.newRecord()); //import records //and even some related records aswell if (i % 500 == 0) { databaseManager.commitTransaction(true); // we thought clearing the foundset might be a good idea vSet.clear(); // start a new transaction databaseManager.startTransaction(); } } // we are done but there might be an open transaction if (databaseManager.hasTransaction()) { databaseManager.commitTransaction(true); } ``` So we commit every 500 records. This is quite fast at start, but the problem is that each commit takes longer to execute. Committing every 200 or 1000 does not really matter. Can anyone explain why those commits take longer every next one? ---
**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\ **Post date:** [February 21, 2013, 7:24pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/15 "2013-02-21T19:24:10Z")
I dont know the answer to that, but we do every (large) import by creating 500 INSERT row in plain text, and fire it by rawSQL, blazingly fast, even at 100.000k of rows… ---
**Author:** ![Ruben79](https://avatars.discourse-cdn.com/v4/letter/r/b782af/32.png) [@Ruben79](https://forum.servoy.com/u/Ruben79)\ **Post date:** [February 21, 2013, 9:14pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/16 "2013-02-21T21:14:12Z")
We thought about that too and tried that in the past. However, we have quite a lot of stored calculations in our solution (unfortunately). Using rawsql means you have to recalculate all the records after, which would take the same amount of time in the end. Apart from that, you don’t want to recalculate 100k records in one go. That would cause memory problems. I ran some more tests, and it turns out the commits take longer when creating related records. When I create records in the main table only, commit time stays the same. So despite the vSet.clear() after doing the commit, somehow the related foundsets are still in memory and also in the next commit. Can this behaviour be explained? ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [February 24, 2013, 7:43pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/17 "2013-02-24T19:43:02Z")
Hi Ruben, What if you get a new foundset instead of clearing it? So use ````auto vSet = databaseManager.getFoundSet("db:/"); ``` instead of ``` vSet.clear(); ```` ---
**Author:** ![Ruben79](https://avatars.discourse-cdn.com/v4/letter/r/b782af/32.png) [@Ruben79](https://forum.servoy.com/u/Ruben79)\ **Post date:** [February 25, 2013, 9:18am UTC](https://forum.servoy.com/t/speeding-new-records/11987/18 "2013-02-25T09:18:27Z")
Hi Robert, Same thing happens… By the way this is a comparison of the logged commit times on using related records or not. So this every 200 records. ```auto Related records No related records Commit took 5 seconds Commit took 2 seconds Commit took 4 seconds Commit took 2 seconds Commit took 5 seconds Commit took 2 seconds Commit took 6 seconds Commit took 3 seconds Commit took 7 seconds Commit took 3 seconds Commit took 7 seconds Commit took 2 seconds Commit took 7 seconds Commit took 2 seconds Commit took 8 seconds Commit took 3 seconds Commit took 9 seconds Commit took 3 seconds Commit took 9 seconds Commit took 3 seconds Commit took 10 seconds Commit took 2 seconds Commit took 10 seconds Commit took 2 seconds Commit took 11 seconds Commit took 3 seconds Commit took 11 seconds Commit took 3 seconds Commit took 13 seconds Commit took 3 seconds Commit took 12 seconds Commit took 2 seconds Commit took 14 seconds Commit took 2 seconds Commit took 12 seconds Commit took 3 seconds Commit took 13 seconds Commit took 2 seconds Commit took 13 seconds Commit took 3 seconds Commit took 13 seconds Commit took 2 seconds Commit took 17 seconds Commit took 2 seconds Commit took 18 seconds Commit took 3 seconds Commit took 18 seconds Commit took 3 seconds ``` ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [February 25, 2013, 11:08am UTC](https://forum.servoy.com/t/speeding-new-records/11987/19 "2013-02-25T11:08:30Z")
is this a smart client? What if you give the client way more memory, does that help? Also if you look at the database performance tab on the admin page, do you see there timings go up? Or what kind of queries are being done, are those piling up? ---
**Author:** ![ngervasi](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@ngervasi](https://forum.servoy.com/u/ngervasi)\ **Post date:** [February 25, 2013, 4:21pm UTC](https://forum.servoy.com/t/speeding-new-records/11987/20 "2013-02-25T16:21:25Z")
BTW: if it’s smart client consider doing the import server side using the HC plugin to save on latency. [Next page](https://forum.servoy.com/t/speeding-new-records/11987.md?page=2)