hey,

hold on - wasn't this why hierarchyID types were invented ?

you can even use persisted computed columns to make it nice and fast.

you would need a 1 time hit...

It's a good idea from Anthony, you might get even better performance if you also add an index to #TEMP:

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
   ...

• This reply was modified 6 years, 7 months ago by Jonathan AC Roberts. Reason: maybe a different index

I think performance might improve if you add an index on SourceId including other columns in the query.

Try this and see if there is an improvement:

 CREATE...

No need for a cte:

update h
   set h.Person = t.Person_To, 
       h.Pctg = t.Pctg_New 
  from...

No need for a cte:

update h
   set h.Person = t.Person_To, 
       h.Pctg = t.Pctg_New 
  from #holders h
...

I would use a NOT EXISTS in some form

;WITH CTE AS
(
    SELECT cs_subscription.cs_contactyominame AS 'Contact Name',
        ...

• This reply was modified 6 years, 7 months ago by Jonathan AC Roberts.

Yeah, as you mention Jonathan, I could have multiplied from same table. Thats what i went on to do, but stupid as I am I then realised that I...

Can't you multiply two matrices together from the same table?

I don't see why you need two different tables?

https://knoema.com/FREDIR2017Aug/u-s-interest-rate?tsId=1033510

https://knoema.com/FREDIR2017Aug/u-s-interest-rate?tsId=1033450

Remarks

STRING_SPLIT inputs a string that has delimited substrings, and inputs one character to use as the delimiter or separator. STRING_SPLIT outputs a single-column table whose rows contain the substrings. The name...

You need to speed up the statements that run after this stored procedure is called that are contained in the same transaction. Or, if you can, rearrange the calls to...

Could it be that the SP is called by long running processes (transactions) that are updating the same row (with the same [object_name]) and one of the transactions hasn't finished...

You could do it with a SQL Agent job and a call to DBMail.

;WITH cte AS 
(
    SELECT Revision_ID, 
           COUNT(*) CountParts
     ...

Another option is to use dynamic SQL. For any input parameters that are null the AND clause could be removed.

For example, if @PatientID were null you could remove this line...