mssql speed

[ROCLASI]

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

Code: Select all

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

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

Code: Select all

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.

Code: Select all

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.

[lesouef]

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.

[lesouef]

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.

[ROCLASI]

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.

Code: Select all

,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

[lesouef]

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.