mssql speed

[lesouef]

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.

[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

Code: Select all

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...

[lwjwillemsen]

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

Code: Select all

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,

[lesouef]

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.

[erdione]

Could you please provide your servoy conf:
- jdbc driver (ms or jtds) and version
- url string used for connecting to mssql server

[lesouef]

jdbc is ms v3.0.1119.0
jdbc:sqlserver://localhost:1433;DatabaseName=ocifam;SelectMethod=cursor;autoReconnect=true

[erdione]

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

[lesouef]

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.

[lesouef]

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?

[Harjo]

Can't you use the foundsetUpdater instead?? (under databaseManager)

It's hard to follow you, without ANY code, what you are doing....

[lesouef]

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);
}

[ROCLASI]

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.

Code: Select all

var div = ($i+1)/1000 ; // pitch
if (div== Math.floor(div)) {

into

Code: Select all

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:

Code: Select all

for (var $i = 1 ; $i <= foundset.getSize() ; $i++) {

So your code would be looking like this:

Code: Select all

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);
}

[lesouef]

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...

[ROCLASI]

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:

Code: Select all

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.

[lesouef]

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.