1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| select INDCLI_ID, INDCLI_PRENOM_NOM, INDCLI_ADRESSE1, INDCLI_CODE_POSTAL
into #WA_AF
from WA_INDIVIDU_CLIENT
where INDCLI_PRENOM_NOM = 'AIR FRANCE HOST'
-- 20 604 lignes en 2s / 99% de tempdb disponible
create clustered index idx_dedoub on #WA_AF(INDCLI_ADRESSE1, INDCLI_CODE_POSTAL, INDCLI_PRENOM_NOM, INDCLI_ID)
-- 1s / 99% de tempdb disponible
UPDATE
WA_INDIVIDU_CLIENT
SET
INDCLI_FLAG = 1
WHERE
INDCLI_FLAG = 0
AND INDCLI_CLI_AH_ID = 0
AND INDCLI_PRENOM_NOM = 'AIR FRANCE HOST'
AND exists (select INDCLI_ID from #WA_AF T2
WHERE
WA_INDIVIDU_CLIENT.INDCLI_PRENOM_NOM = T2.INDCLI_PRENOM_NOM
AND WA_INDIVIDU_CLIENT.INDCLI_ADRESSE1 = T2.INDCLI_ADRESSE1
AND WA_INDIVIDU_CLIENT.INDCLI_CODE_POSTAL = T2.INDCLI_CODE_POSTAL
AND isnull(T2.INDCLI_PRENOM_NOM,' ') <> ' '
AND isnull(T2.INDCLI_ADRESSE1,' ') <> ' '
AND isnull(T2.INDCLI_CODE_POSTAL,' ') <> ' '
AND (WA_INDIVIDU_CLIENT.INDCLI_CLI_AH_ID = 0
or WA_INDIVIDU_CLIENT.INDCLI_CLI_AH_ID > T2.INDCLI_ID)
AND WA_INDIVIDU_CLIENT.INDCLI_ID <> T2.INDCLI_ID
)
-- 20 604 lignes en 12s
update
WA_INDIVIDU_CLIENT
set
INDCLI_CLI_AH_ID = (select min(T2.INDCLI_ID) from #WA_AF T2
WHERE
WA_INDIVIDU_CLIENT.INDCLI_PRENOM_NOM = T2.INDCLI_PRENOM_NOM
AND WA_INDIVIDU_CLIENT.INDCLI_ADRESSE1 = T2.INDCLI_ADRESSE1
AND WA_INDIVIDU_CLIENT.INDCLI_CODE_POSTAL = T2.INDCLI_CODE_POSTAL
AND WA_INDIVIDU_CLIENT.INDCLI_ID <> T2.INDCLI_ID),
INDCLI_CRITERE = 1
where
INDCLI_FLAG = 1
and INDCLI_CLI_AH_ID = 0
and INDCLI_PRENOM_NOM = 'AIR FRANCE HOST'
-- la requête n'aboutit pas (aucun IO physique ...) |
Partager