I am doing a modification on a foundset which is 70k lines.
I am using a dupped foundset to avoid screen refresh and only displays the records count in the console every 100 records to follow the progress.
the loop start executing quite fast, then progressively slows down untill damned slow.
is that a servoy or a db performance setup problem? db is mssql2008.
How do you loop?
What sounds a bit “old fashioned” is the approach of duplicating a foundset. If you loop over your foundset like this
for ( var i = 1 ; i <= foundset.getSize() ; i ++ ) {
var vRecord = foundset.getRecord(i);
// do something with your record
}
there will be no updates in the UI, because everything happens in memory (as opposed to foundset.setSelectedIndex()). It is also way faster than looping with the selectedIndex and should not give you any memory or performance problems. YOu could also consider doing all this inside a transaction…
patrick:
How do you loop?What sounds a bit “old fashioned” is the approach of duplicating a foundset. If you loop over your foundset like this
for ( var i = 1 ; i <= foundset.getSize() ; i ++ ) {
var vRecord = foundset.getRecord(i);
// do something with your record
}
there will be no updates in the UI, because everything happens in memory (as opposed to foundset.setSelectedIndex()). It is also way faster than looping with the selectedIndex and should not give you any memory or performance problems. YOu could also consider doing all this inside a transaction...
Thanks for the tip Patrick ! I did not think in the past when iterating through foundset of the Record-object…
Regards,
I did it with current foundset too, same, I did not use the controller to loop, I know this is only when you want to show the user what happens and way slower.
the reason I do duplicated the foundset is because I once noticed that when doing operations on a related foundset (shown in a portal), changes were not visible untill the end when using a dupped foundset.
my suspicions is towards mssql at the moment, I just got hints from a mssql guy, I’ll try that.
Could you please provide your servoy conf:
- jdbc driver (ms or jtds) and version
- url string used for connecting to mssql server
jdbc is ms v3.0.1119.0
jdbc:sqlserver://localhost:1433;DatabaseName=ocifam;SelectMethod=cursor;autoReconnect=true
If you think that it’s a db performance problem you should explore 2 two way:
don’t change your servoy code and just try different parameters in your db connection
-
you should try SelectMethod=direct and see if you get better performances, I don’t know if servoy use cursor for fetching data. (Maybe Servoy could complete)
-
try the jtds driver included in the standard servoy install or get the latest version http://jtds.sourceforge.net
for that just change you servoy server config
jdbc:jtds:sqlserver://localhost:1433/ocifam
net.sourceforge.jtds.jdbc.Driver
using “direct” slightly improves, but not significantly. jtds, I have v 1.2.5, no difference.
speed is fast for 1k lines, acceptable for 5k, then not usable.
while 5k lines last 15s, 72k lines last 6 hours…
I have no time to test this now, but i will re-install this solution on a mysql machine later on to split the pb in between servoy and the db.
now tested with xp & mssql2005 (versus vista and mssql2008) > much faster result, but still not optimum if the amount of records to update > 5k
now changed the code to start/commit transaction every N record, and this is blasting fast on my old p4/single core!
N max being 5k before speed collapses, best result speed around 500 lines for each transaction. (10s to update 73k lines in this case, same machine runs mssql and servoy dev)
I’ll see if this method give the same good result on my other vista/sql2008 machine on monday, but obviously the servoy generated query in this situation puts the sql server in its knees if there are too many records. maybe a clue for servoy to take this into account?
I have installed the same solution with mysql 5, I get similar results, overall speed is lower, but usable, except for bulk deletes which are obviously a problem, but I had no time to see if reducing the amount of records in this case would help. maybe a direction as well for servoy to break bulk commands in smaller lots?
Can’t you use the foundsetUpdater instead?? (under databaseManager)
It’s hard to follow you, without ANY code, what you are doing…
to me this foundsetupdater updates from the db, not to the db. right or wrong? so I never uses it.
my code is down here: if I increase the pitch (see comments) to 20000 or to foundset.getSize (73k in my case here), the machine dies! and it last for ages.
function import_srl_post_process()
{ databaseManager.setAutoSave(false);
databaseManager.startTransaction();
for (var $i = 0 ; $i < foundset.getSize() ; $i++)
{ var $rec= foundset.getRecord($i+1);
if ($rec.semarq != null)
{ …stuff here…
}
else
{ …other stuff here…
}
var div = ($i+1)/1000 ; // pitch
if (div== Math.floor(div))
{ var $result = databaseManager.commitTransaction();
databaseManager.startTransaction();
application.output("updated " + $i +“recs. result=”+$result);
}
}
var $result = databaseManager.commitTransaction();
application.output("updated " + $stop +“recs. result=” + $result );
databaseManager.setAutoSave(true);
}
Hi Lesouef,
The foundsetUpdater does what the name implies…it updates the foundset (and therefor the database).
Can you tell us what the actual change to the data is? I mean are you using information of the same record to change some columns? Or perhaps some related data from this record? Or is it more of a general update using non-related data?
To understand this would help us to see what the best option would be because there are several options:
- this loop
- foundsetUpdater
- loop combined with foundsetUpdater
- pure SQL update
Also your code can be optimized a little. You declare div, $rec and $result in the loop over and over again. Declaring them once is enough.
Also the following code can be simplified.
var div = ($i+1)/1000 ; // pitch
if (div== Math.floor(div)) {
into
if ( $i%1000 == 0 ) {
Also your for loop is zero based but the foundset is 1 based (starts with 1) so you keep adding 1 to $i when you addressing the record. Why not just changing the for loop code instead:
for (var $i = 1 ; $i <= foundset.getSize() ; $i++) {
So your code would be looking like this:
function import_srl_post_process() {
var $rec,
$result;
databaseManager.setAutoSave(false);
databaseManager.startTransaction();
for (var $i = 1 ; $i <= foundset.getSize() ; $i++) {
$rec = foundset.getRecord($i);
if ($rec.semarq != null) {
.......stuff here..........
} else {
........other stuff here.........
}
if ($i%1000 == 0) {
$result = databaseManager.commitTransaction();
databaseManager.startTransaction();
application.output("updated " + $i +" recs. result="+$result);
}
}
$result = databaseManager.commitTransaction();
application.output("updated " + $stop +" recs. result=" + $result );
databaseManager.setAutoSave(true);
}
you are right for code optimisation, but these changes done, though smarter, does not improve significantly.
about the changes inside the loop, this particular case sets 1 field from various values from other fields of the same table.
but I have the problem somewhere else where it sets a field from a related table/foundset also. and sometimes combined, 1 of each.
at the moment, the best I get is 8s to update the 73k foundset using only internal values and 60s using a related field value. still using a “pitch” of 500 recs to commit transaction.
where would the foundsetupdater more efficient? the “internal” fields I guess?
I have attached the code…
mycode.txt (2.84 KB)
Hi Lesouef,
Does this code only have to run on MSSQL or do you require it to be portable to other backends ?
Anyway, I think you could do all this with a single SQL statement to update all records in one go and let the database server handle it all. It will be way more efficient than pulling all that data out and back again.
Also one bug in your code:
else if ($rec.semarq.substr(0,3)=='MAIL') $rec.canalcde = 1 ; //mail
This will never trigger because of the length of the substring.
Hope this helps.
mail > copy/paste syndrom…
mssql is enough.
full sql: my sql skills are too limited for this. pulling data takes no time, the problem seems to be that the servoy generated sql queries are far more complex than a simple sql update, probably to be able to rollback changes. but you are right, I have to improve my sql, I just know the basics, but never took time to learn strings manipulation, conditions, aso… one of the reasons to use servoy btw.
Learning SQL is very much recommended. There are so many things you shouldn’t be doing or are just not efficient in the application layer (and thus in Servoy) but should be done in the database layer. SQL is required then.
So lets look at what you are doing, you are doing an UPDATE and a bunch of IF-THEN-ELSE statements.
In (MSSQL) SQL that translates to
UPDATE tableName
SET columName = <expression>, [ ...n ]
WHERE <conditions>
And to use an IF-THEN-ELSE construct in SQL you can use the following syntax:
CASE
WHEN <condition> THEN <value>
[ ...n ]
[ ELSE <value> ]
END
To trim a string you use LTRIM() and RTRIM(), which does what it suggests.
The substr function will translate to SUBSTRING(value, start, length).
And the utils.stringPatternCount() function translates to CHARINDEX(searchValue,value), well at least the way you are using it in your method. CHARINDEX() returns the position of the string, not the count.
So this will give you all the ingredients you need for the update query.
Of course there is one bit in your code that we don’t know and that is the related value that is also a calc. Is this a stored calc and what is the join criteria ?
Anyway, the following (untested) SQL is a translation of what you are doing in your method.
UPDATE tableName
SET
skmarq = LTRIM(RTRIM(semarq))
,canalcde = CASE
WHEN semarq IS NOT NULL THEN
CASE
WHEN SUBSTRING(semarq, 1, 3) = 'WEB' THEN 2
WHEN SUBSTRING(semarq, 1, 3) = 'SCE' THEN 7
WHEN SUBSTRING(semarq, 1, 3) = 'TEL' THEN 9
WHEN SUBSTRING(semarq, 1, 4) = 'SRCE' THEN 8
WHEN SUBSTRING(semarq, 1, 4) = 'MAIL' THEN 1
WHEN SUBSTRING(semarq, 1, 3) = 'COM' OR semarq IN ('BGNW','DIET','RIVIERE','RIVEBONTE','SERSOUB','SALGUES') THEN 10
ELSE null -- assuming this will do in case of a non-match
END
ELSE
CASE
WHEN t7t10$ = 'TEL' THEN
CASE
WHEN CHARINDEX('SCE',skstat) > 0 THEN 7
WHEN CHARINDEX('SRC',skstat) > 0 THEN 8
WHEN CHARINDEX('CO',skstat) > 0 OR CHARINDEX('PW',skstat) > 0 THEN 9
ELSE 3
END
WHEN CHARINDEX('SRC',skstat) > 0 THEN 8
WHEN CHARINDEX('WEB',skstat) > 0 THEN 2
WHEN CHARINDEX('LIB',skstat) > 0 THEN 4
WHEN CHARINDEX('VC20',skstat) > 0 OR SUBSTRING(skstat,1,2) = '20' THEN 5
WHEN (t7t01$ = 'M' OR t7t10$ = 'M') AND NOT (t7t10$ = 'TEL') THEN 1
ELSE 6
END
END
,ca_ht = ((skqte * skmont) - (skqte * skmont * skrem / 100 )) * skaf
,net_ann = CASE
WHEN skcep IN ('01','30','24') THEN '1'
WHEN skcep IN ('19','20') THEN '0'
ELSE '2'
END
-- calc and related field, don't know the criteria for both. Using pseudo code for the columns here.
,recap = CASE
WHEN related.publication_id IS NOT NULL THEN related.recap
ELSE '0'
END
Do test the SQL in a real SQL editor (and probably with a WHERE clause so you only test on 1 or a few records).
Hope this gets you going.
gosh, you never stop, worst than me!
I’ll try that then, since you starting my training… I never started sql really, because of the language similarity with many others (I already mix quite a lot in between all others, php, pearl, js, different shells syntaxes), and I have been trapped once realizing that sql is “almost” standard, ie when you switch from say sql anywhere to mysql, you may have to review your code, kind of situation I thought I’d would to avoid, but it seems that I have reached the limit of efficiency too.
back to the pb:
I know this serie of “if then else” is bad, but the condition changes everyday at the moment, so I’ll wait for the asker to stabilize his demand to put this in a table; it would generate an extra field if i user the servoy relations since I need to link on a part of a field, but sqlwise, this can be done on the fly, i guess I can do a join on substring(field,1,3)=otherfield for instance. a real bad habit they have in this place to stuff 10 informations in the same field using it for something else than its original purpose. but that a commercial app on db2, and the structure cannnot change…
about the patterncount, I just need to find it anywhere in the field, so as long as charindex returns -1 if the string is not found, that’s enough for me to find out if it is found or not.
so i’ll be back later on after these tests and see the time difference. the advantage of this sql is that I may able to integrate it in the sql import method directly (already in sql this one) and get the records already modified directly, but I need to make a linked server as some tables ar not on the same server.
I could not get the related field as expected.
without this, the speed can’t even be measured, less than 1 s. wunderbar!
I use databaseManager.refreshRecordFromDatabase(foundset, -1) to refresh my servoy client >0
but if I use:
plugins.rawSQL.notifyDataChange(“db”, “table”,databaseManager.convertToDataSet(foundset),3);
the whole thing goes nuts for ever. Can this command be used for a complete foundset? or is it too big?
as for getting the related field, I have tried several ways, but obviously I miss the way to do it. I just get a good list of sql management studio insults.
what I want to do is:
- get sktits.publication_id from sktits (linked on titre_srl.sktit=sktits.sktit)
once I get this, use the result to get another one from another table, ie: - get publications.recap from publications (linked on titre_srl.publication_id=publications.publication_id)
I can’t even get the first one correct, it says the result is not unique…
the query as of now is:
UPDATE ocifam.dbo.titre_srl
SET
canalcde = CASE
WHEN semarq IS NOT NULL THEN
CASE
WHEN SUBSTRING(semarq, 1, 3) = ‘WEB’ THEN 2
WHEN SUBSTRING(semarq, 1, 3) = ‘SCE’ THEN 7
WHEN SUBSTRING(semarq, 1, 3) = ‘TEL’ THEN 9
WHEN SUBSTRING(semarq, 1, 4) = ‘SRCE’ THEN 8
WHEN SUBSTRING(semarq, 1, 4) = ‘MAIL’ THEN 1
WHEN SUBSTRING(semarq, 1, 3) = ‘COM’ OR semarq IN (‘BGNW’,‘DIET’,‘RIVIERE’,‘RIVEBONTE’,‘SERSOUB’,‘SALGUES’) THEN 10
ELSE 0
END
ELSE
CASE
WHEN t7t10$ = ‘TEL’ THEN
CASE
WHEN CHARINDEX(‘SCE’,skstat) > 0 THEN 7
WHEN CHARINDEX(‘SRC’,skstat) > 0 THEN 8
WHEN CHARINDEX(‘CO’,skstat) > 0 OR CHARINDEX(‘PW’,skstat) > 0 THEN 9
ELSE 3
END
WHEN CHARINDEX(‘SRC’,skstat) > 0 THEN 8
WHEN CHARINDEX(‘WEB’,skstat) > 0 THEN 2
WHEN CHARINDEX(‘LIB’,skstat) > 0 THEN 4
WHEN CHARINDEX(‘VC20’,skstat) > 0 OR SUBSTRING(skstat,1,2) = ‘20’ THEN 5
WHEN (t7t01$ = ‘M’ OR t7t10$ = ‘M’) AND NOT (t7t10$ = ‘TEL’) THEN 1
ELSE 6
END
END
,ca_ht = ((skqte * skmont) - (skqte * skmont * skrem / 100 )) * skaf
,net_ann = CASE
WHEN skcep IN (‘01’,‘30’,‘24’) THEN ‘1’
WHEN skcep IN (‘19’,‘20’) THEN ‘0’
ELSE ‘2’
END
,recap = case
when (select ocifam.dbo.sktits.publication_id
from ocifam.dbo.titre_srl
join ocifam.dbo.sktits on ocifam.dbo.titre_srl.sktit=ocifam.dbo.sktits.sktit) is not null
then 1 – any value as this is just a test
end
and I of course need a nested query to get the final field value. now you understand why I was reluctant to attempt this in sql with by poor background.
When you use sub-selects in a query like this then it should only return 1 row, so if it returns more you get these errors.
To make sure a select only returns 1 row you use the SELECT TOP 1 syntax.
Also you need to join the sub-select with the row you are updating in the main query or else you are doing an unrelated select.
So doing a select on the target table (ocifam.dbo.sktits) with the titre_srl.skitt from the main query in the WHERE clause will suffice.
,recap = CASE
WHEN (SELECT TOP 1 ocifam.dbo.sktits.publication_id
FROM ocifam.dbo.sktits WHERE ocifam.dbo.sktits.sktit=ocifam.dbo.titre_srl.sktit) IS NOT NULL
THEN 1 -- any value as this is just a test
END
i did for the last field:
,recap = case
when (select sktits.publication_id from ocifam.dbo.sktits where sktits.sktit=titre_srl.sktit ) is not null
then (select publications.recap from ocifam.dbo.publications where titre_srl.publication_id=publications.publication_id)
else null
and that’s 3s against 60s at best from within servoy. this raises quite a few questions, I was not expexcting such a big difference… even if I include the screen refresh, that makes 18s overall.
now this remains a difficult method is the query is issued from by a user using the UI, but for bulk operations, no doubt, worth spending a week on sql.
I’ll try to make a linked server from my other source (db2) and query joined tables from mssql/db2 this way.
thanks very much for this move forward, I do appreciate.