# mssql speed

**URL:** <https://forum.servoy.com/t/mssql-speed/12051>\
**Category:** Classic Servoy\
**Created:** [April 15, 2010, 9:22am UTC](https://forum.servoy.com/t/mssql-speed/12051 "2010-04-15T09:22:38Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 15, 2010, 9:22am UTC](https://forum.servoy.com/t/mssql-speed/12051/1 "2010-04-15T09:22:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [April 15, 2010, 11:01am UTC](https://forum.servoy.com/t/mssql-speed/12051/2 "2010-04-15T11:01:54Z")

</div>

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

```auto
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…

---

<div class="post-metadata">

**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 15, 2010, 11:46am UTC](https://forum.servoy.com/t/mssql-speed/12051/3 "2010-04-15T11:46:15Z")

</div>

> 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
> 
> ```auto
> for ( var i = 1 ; i <= foundset.getSize() ; i ++ ) {
> 
> ```

var vRecord = foundset.getRecord(i);  
// do something with your record  
}

> ```auto
> 
> 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,

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 15, 2010, 6:38pm UTC](https://forum.servoy.com/t/mssql-speed/12051/4 "2010-04-15T18:38:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![erdione](https://avatars.discourse-cdn.com/v4/letter/e/e9a140/32.png) [@erdione](https://forum.servoy.com/u/erdione)\
**Post date:** [April 16, 2010, 7:11am UTC](https://forum.servoy.com/t/mssql-speed/12051/5 "2010-04-16T07:11:18Z")

</div>

Could you please provide your servoy conf:

- jdbc driver (ms or jtds) and version
- url string used for connecting to mssql server

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 16, 2010, 7:19am UTC](https://forum.servoy.com/t/mssql-speed/12051/6 "2010-04-16T07:19:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![erdione](https://avatars.discourse-cdn.com/v4/letter/e/e9a140/32.png) [@erdione](https://forum.servoy.com/u/erdione)\
**Post date:** [April 16, 2010, 7:59am UTC](https://forum.servoy.com/t/mssql-speed/12051/7 "2010-04-16T07:59:48Z")

</div>

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](http://jtds.sourceforge.net)  
for that just change you servoy server config  
jdbc:jtds:sqlserver://localhost:1433/ocifam  
net.sourceforge.jtds.jdbc.Driver

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 16, 2010, 9:37am UTC](https://forum.servoy.com/t/mssql-speed/12051/8 "2010-04-16T09:37:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 17, 2010, 11:14am UTC](https://forum.servoy.com/t/mssql-speed/12051/9 "2010-04-17T11:14:26Z")

</div>

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?

---

<div class="post-metadata">

**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:** [April 17, 2010, 11:23am UTC](https://forum.servoy.com/t/mssql-speed/12051/10 "2010-04-17T11:23:26Z")

</div>

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

It’s hard to follow you, without ANY code, what you are doing…

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 17, 2010, 11:49am UTC](https://forum.servoy.com/t/mssql-speed/12051/11 "2010-04-17T11:49:12Z")

</div>

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

---

<div class="post-metadata">

**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 17, 2010, 12:38pm UTC](https://forum.servoy.com/t/mssql-speed/12051/12 "2010-04-17T12:38:04Z")

</div>

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.

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

```

into

```auto
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:

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

```

So your code would be looking like this:

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

```

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 17, 2010, 2:03pm UTC](https://forum.servoy.com/t/mssql-speed/12051/13 "2010-04-17T14:03:35Z")

</div>

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](https://forum.servoy.com/uploads/short-url/AdKtWf8HUybD4ugCf1MkNy0n1tP.txt) (2.84 KB)

---

<div class="post-metadata">

**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 17, 2010, 4:11pm UTC](https://forum.servoy.com/t/mssql-speed/12051/14 "2010-04-17T16:11:26Z")

</div>

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:

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

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 17, 2010, 5:09pm UTC](https://forum.servoy.com/t/mssql-speed/12051/15 "2010-04-17T17:09:54Z")

</div>

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.

---

<div class="post-metadata">

**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 17, 2010, 8:39pm UTC](https://forum.servoy.com/t/mssql-speed/12051/16 "2010-04-17T20:39:43Z")

</div>

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

```auto
UPDATE tableName 
SET columName = <expression>, [...n]
WHERE <conditions>

```

And to use an IF-THEN-ELSE construct in SQL you can use the following syntax:

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

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

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 18, 2010, 8:14am UTC](https://forum.servoy.com/t/mssql-speed/12051/17 "2010-04-18T08:14:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 18, 2010, 11:54am UTC](https://forum.servoy.com/t/mssql-speed/12051/18 "2010-04-18T11:54:00Z")

</div>

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.

---

<div class="post-metadata">

**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 18, 2010, 1:12pm UTC](https://forum.servoy.com/t/mssql-speed/12051/19 "2010-04-18T13:12:57Z")

</div>

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.

```auto
,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

```

---

<div class="post-metadata">

**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\
**Post date:** [April 18, 2010, 3:40pm UTC](https://forum.servoy.com/t/mssql-speed/12051/20 "2010-04-18T15:40:54Z")

</div>

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.