150K records of what? the parent or relatedrecords?
But if you convert the parent foundset (that holds the 150K records or the 150K related records through all the parents) and you do convect foundset set. And then the foundset updater. Then it should be only ONE update statement. Isn’t this the case?
Firstly, I was using 2.0.
Secondly, I was updating the related records, using the parent foundset. When looking in the logs, an update statement was created then an array of 20 PKs was entered, before another update staement was created and the next set of 20 PKs were updated.
Here is my code, and in my example I was updating backround field in original, for 150k records (both tables have an almost 1:1 relationship in terms of PKs)
// convert stock to original foundset and create foundset updater
var stock_orig = databaseManager.convertFoundSet(foundset,stock_to_original);
var fs_stock_orig = databaseManager.getFoundSetUpdater(stock_orig);
if (globals.background) {
fs_stock_orig.setColumn('background',globals.background);
}
fs_stock_orig.performUpdate();
and that piece of code is executed once for the parent foundset.getSize() of 150K?
but then it shouldn’t matter. Because only one update statement is fired to the database. Which also shouldn’t take very long.
Yes, I have used the debugger to go through the method, it is just executed once.
The logs are churning out an update statement for every group of 20 PKs.
Just ran it again looking at the logs, and it is definatly creating an update statement for each PK in the converted foundset!
It also appears to be doing a select statement from stock, getting a list of 20 PK, the creating an update statement for the original table for each of the 20 PKs, then repeating this for the next set.
e.g.
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535018 ,type: java.lang.Integer
used sql select nascode, seed_type_id, mutagen_id, background, pedigree, ploidy, transformation_method from original where original.nascode in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
questiondata[0]= 535019 ,type: java.lang.Integer
questiondata[1]= 535022 ,type: java.lang.Integer
questiondata[2]= 535023 ,type: java.lang.Integer
questiondata[3]= 535032 ,type: java.lang.Integer
questiondata[4]= 535035 ,type: java.lang.Integer
questiondata[5]= 535037 ,type: java.lang.Integer
questiondata[6]= 535041 ,type: java.lang.Integer
questiondata[7]= 535042 ,type: java.lang.Integer
questiondata[8]= 535044 ,type: java.lang.Integer
questiondata[9]= 535046 ,type: java.lang.Integer
questiondata[10]= 535048 ,type: java.lang.Integer
questiondata[11]= 535053 ,type: java.lang.Integer
questiondata[12]= 535055 ,type: java.lang.Integer
questiondata[13]= 535056 ,type: java.lang.Integer
questiondata[14]= 535057 ,type: java.lang.Integer
questiondata[15]= 535058 ,type: java.lang.Integer
questiondata[16]= 535063 ,type: java.lang.Integer
questiondata[17]= 535066 ,type: java.lang.Integer
questiondata[18]= 535067 ,type: java.lang.Integer
questiondata[19]= 535069 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535019 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535022 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535023 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535032 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535035 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535037 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535041 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535042 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535044 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535046 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535048 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535053 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535055 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535056 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535057 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535058 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535063 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535066 ,type: java.lang.Integer
sql update original set background = ? where original.nascode = ?
questiondata[0]= '(tester)Col-0 (Columbia, CS60000)' ,type: java.lang.String
questiondata[1]= 535067 ,type: java.lang.Integer
yes that is true.. because this isn’t possible in standard sql (and very few db’s support it int their feature set)
update table1,table2 set table1=X where table1.column = table2.column and xxxxxx
and that is what you want (table1 is youre related and table2 youre parent)
so this won’t be easy even in plain sql to do. And because the parent foundset almost has the same number of records are youre related foundsets (i think you mentioned that) it is also not an option to cycle over all the parents and do the update directly on all the parents related foundset.
like:
if (globals.background) {
for(var x=1;x<=foundset.getSize();x++)
{
var record = foundset.getRecord(x);
var fs_stock_orig = databaseManager.getFoundSetUpdater(record.stock_to_original);
fs_stock_orig.setColumn(‘background’,globals.background);
fs_stock_orig.performUpdate();
}
}
I created the update statement directly in mysql and it took about 10 seconds. Thats why I was asking if it would ever be possible to create custom sql updates in Servoy? I think I read that it would cause problems in the logs?
The updater works fine for smaller foundets, so I will probably restrict clients from updating more than ~5000 stocks at a time. Its very rare that they would ever do more than that anyway. I suppose I am just testing the solution to its limits.
If you have any more ideas, let me know.
Cheers
Simon
What is the update statement that you used?
yes mysql is one of the few databases that supports that i believe. But others don’t It is not standard SQL.
If I do a search using servoy where the collection_id is 18, then I get 150k hits. This update statement below updates all those records very quickly! (~10seconds)
update original, stock set background = ‘col’ where original.nascode = stock.nascode and stock.collection_id = 18;