SQLTeam.com | Weblogs | Forums

Baffled


#1

Hi All,

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?

Thanks,

Rog


#2

Have you checked your index fragmentation and statistics?


#3

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.


#4

Forgot the length:

RIGHT('00000000'+sou.amb_dossier,7)

But otherwise totally agree :+1:


#5

Heh! Thanks bitsmed :smile:


#6

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)

Rog


#7

UPDATE:

Ok things I've now tried.

  1. Created a table with 3 fields and transferred all the mgh recs to it. Ran the update and it ran about 15secs.
  2. Cleared the table and copied all the records. Ran the update using RVH ran in 20secs (4 million records)
  3. Ran the update with MGH and it's still running after 8 mins

Time to shoot myself.

Rog


#8

Try this:

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


#9

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'

#10

That was my first thought too. Joining on

'g' + REPLICATE('0', 7 - LEN(sou.amb_dossier)) + sou.amb_dossier 
	= usa.adt_dossier_installation
or
'g' + RIGHT('00000000'+sou.amb_dossier, 7)	=  usa.adt_dossier_installation

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)


#11

Hi All,

Ok sorry for the delay in responding, I headed home before I became violent. Sigh

  1. 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.

  1. Closed the SSIS dev tools
  2. Killed all the connections that were linked to the cusm_bdg DB
  3. Brought the DB offline
  4. Brought the DB online
  5. Closed SSMS
  6. Restarted SSMS
  7. Ran the update command against the value 'RVH' took 20secs (4million records)
  8. Ran the update command against the value 'MGH' took 1sec (200k records)
  9. 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.

Have a good day,

Rog


#12

CPU / Memory busy with something else?

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?


#13

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.

Rog


#14

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!


#15

If I recall correctly, base tables referenced by an index view have to be in the same database in which the view is created.


#16

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.


#17

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.

Rog


#18

That's good to hear.

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.)


#19

Did you try my approach of WHERE LIKE ... AND ...? The AND will not cause the query issues an OR would.