I have a simple update statement that used to work without a glitch that now just plain does not.
Currently working statement
update sou
set sou.adt_usager_id = usa.adt_usager_id
from cusm_bdg.medivisit.source sou
inner join elgi_edi_01.adt.usager usa on 'v' + REPLICATE('0', 7 - LEN(sou.amb_dossier)) + sou.amb_dossier
= usa.adt_dossier_installation
where sou.amb_Installation = 'rvh'
Currently not working statement
update sou
set sou.adt_usager_id = usa.adt_usager_id
from cusm_bdg.medivisit.source sou
inner join elgi_edi_01.adt.usager usa on 'g' + REPLICATE('0', 7 - LEN(sou.amb_dossier)) + sou.amb_dossier
= usa.adt_dossier_installation
where sou.amb_Installation = 'mgh'
There are over 5 million records with sou.amb_installation = 'rvh' and only 200k with 'mgh'. It takes 11 seconds to run the rvh and so far at 45mins the mgh was still going.
Again this used to work (part of an ssis package), I moved the code to ssms to make sure the package wasn't the issue and it doesn't work there as well.
Can anyone think of anything that would cause this?
When you query for the count like shown below, does it work? Is that how you found the number of rows to be updated?
SELECT COUNT(*)
from cusm_bdg.medivisit.source sou
inner join elgi_edi_01.adt.usager usa on 'g' + REPLICATE('0', 7 - LEN(sou.amb_dossier)) + sou.amb_dossier
= usa.adt_dossier_installation
where sou.amb_Installation = 'mgh'
Can you look at the query plan and see if there are any differences. Also, make sure that the statistics are updated and indexes are maintained.
By the way, although it may have nothing to do with your current problem, my personal preference would be to join like this:
FROM
cusm_bdg.medivisit.source sou
INNER JOIN elgi_edi_01.adt.usager usa ON
'g' + RIGHT('00000000'+sou.amb_dossier) = usa.adt_dossier_installation
It avoids the code breaking if sou.amb_dossier is longer than 7 characters.
djj55: I've updated the statistics and the indexes are at about 5% avg.
JamesK:
The select count works fine (also ran a select usa.adt_usager_id which returned values), and no, I know how many records are being affected because it's part of an SSIS package and the file being loaded had 200k rows in it.
Unfortunately I am unable to look at a query plan since the update statement with 'mgh' never completes until I cancel it.
And thanks for the right suggestion. I never took into mind what would happen in the event the value was longer (Though that cannot happen in this particular case)
update sou
set sou.adt_usager_id=usa.adt_usager_id
from cusm_bdg.medivisit.source as sou
where sou.amb_Installation='mgh'
and exists (select 1
from elgi_edi_01.adt.usager as usa
where usa.adt_dossier_installation='g'+right('0000000'+sou.amb_dossier,7)
)
Agreed, would like to see estimated query plan (which, btw, you can get without actually running the query, just highlight the query, right-click on it, and select "Display Estimated Execution Plan").
Also need to see all the indexes defined on the tables.
However, as a blind shot, limiting the rows that need examined from the usa table might be possible like below.
update sou
set sou.adt_usager_id = usa.adt_usager_id
from cusm_bdg.medivisit.source sou
inner join elgi_edi_01.adt.usager usa on
usa.adt_dossier_installation like 'g%' --<<--****
and 'g' + REPLICATE('0', 7 - LEN(sou.amb_dossier)) + sou.amb_dossier
= usa.adt_dossier_installation
where sou.amb_Installation = 'mgh'
are not going to be SARGable so will perform badly ... and scale worse!
Can you create a column in cusm_bdg.medivisit.source that has the [amb_dossier] dossier value WITH leading "g" and zeros added - so you can then just make a straight join? AND create an index on that.
Or an Indexed View? (Or maybe you can use a Computed Column and Index that, but we had "issues" with Computed Columns mucking something else up (I've forgotten, but we wound up having to use some non standard ANSI settings) so we try to avoid them now)
Ok sorry for the delay in responding, I headed home before I became violent. Sigh
I forgot to mention in the update that I have changed the update code to use the right function instead of replicate.
New Update:
Ok having a clearer head this morning I decided to do a few things prior to starting my tests again.
Closed the SSIS dev tools
Killed all the connections that were linked to the cusm_bdg DB
Brought the DB offline
Brought the DB online
Closed SSMS
Restarted SSMS
Ran the update command against the value 'RVH' took 20secs (4million records)
Ran the update command against the value 'MGH' took 1sec (200k records)
Cleared the data from the table, ran the SSIS package and it ran properly for all steps (Including the update)
At this point I'm thinking something might have been locking records in the table but I don't understand why the issue was present when I moved the data to a new table. I mean a lock wouldn't move???
All that being said, I would like to thank everyone for their time and help. And thanks again JamesK for pointing out the right function rather than replicate.
Query plan is now cached, whereas before it was not?
I still think it would be worth trying to avoid both REPLICATE() and RIGHT() in the JOIN or WHERE if you can ... and making sure that the query is covered by (AND making use of!!) an appropriate index ... but maybe the performance times you are now getting are fine so not worth spending more time on?
I could add a derived column step to the SSIS package to create the value into a separate column, create an index on that field then use that for the lookup. (The current index is on the amb_installation and amb_dossier field paired together)
Unfortunately I don't have control over the indexes in the source system and the field that I do the lookup against does not have an index.
Probably a daft idea? but can you create a new database on the server that the source system is on? and then put a VIEW (to the Source System's table) in that and an INDEX on that?
I have no idea if this would actually improve things though ... nor if it would be worth-the-candle!
Rats! Just tried it. VIEW has to have WITH SCHEMABINDING, and you can only use SCHEMABINDING for 2-part named objects, so not on an object in another database.
The process is not time sensitive enough for me to have to go to the extreme. The overall response time as things stand will do. I'm just glad whatever was causing the update failure has been rectified.
However, I'm half-expecting that you will find it back at the original run-speed soon. Hopefully I will be proven wrong. (and if that does happen the "cause" can be found and fixed, given that you've solved it once with stop/re-start THIS and THAT object.)
