mssql speed

Questions and answers regarding general SQL and backend databases

mssql speed

Postby lesouef » Thu Apr 15, 2010 11:22 am

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.
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: mssql speed

Postby patrick » Thu Apr 15, 2010 1:01 pm

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...
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3669
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: mssql speed

Postby lwjwillemsen » Thu Apr 15, 2010 1:46 pm

patrick wrote: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,
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 626
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: mssql speed

Postby lesouef » Thu Apr 15, 2010 8:38 pm

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.
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: mssql speed

Postby erdione » Fri Apr 16, 2010 9:11 am

Could you please provide your servoy conf:
- jdbc driver (ms or jtds) and version
- url string used for connecting to mssql server
Fabrice
erdione
 
Posts: 112
Joined: Thu Feb 01, 2007 1:19 pm

Re: mssql speed

Postby lesouef » Fri Apr 16, 2010 9:19 am

jdbc is ms v3.0.1119.0
jdbc:sqlserver://localhost:1433;DatabaseName=ocifam;SelectMethod=cursor;autoReconnect=true
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: mssql speed

Postby erdione » Fri Apr 16, 2010 9:59 am

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
Fabrice
erdione
 
Posts: 112
Joined: Thu Feb 01, 2007 1:19 pm

Re: mssql speed

Postby lesouef » Fri Apr 16, 2010 11:37 am

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
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: mssql speed

Postby lesouef » Sat Apr 17, 2010 1:14 pm

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?
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: mssql speed

Postby Harjo » Sat Apr 17, 2010 1:23 pm

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

It's hard to follow you, without ANY code, what you are doing....
Harjo Kompagnie
Direct ICT / Servoy Hosting / ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
User avatar
Harjo
 
Posts: 4296
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: mssql speed

Postby lesouef » Sat Apr 17, 2010 1:49 pm

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);
}
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: mssql speed

Postby ROCLASI » Sat Apr 17, 2010 2:38 pm

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);
}
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: 5322
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: mssql speed

Postby lesouef » Sat Apr 17, 2010 4:03 pm

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...
Attachments
mycode.txt
my code
(2.84 KiB) Downloaded 113 times
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: mssql speed

Postby ROCLASI » Sat Apr 17, 2010 6:11 pm

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.
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: 5322
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: mssql speed

Postby lesouef » Sat Apr 17, 2010 7:09 pm

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.
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Next

Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 1 guest