recommendation from microsoft is CU - so go that route

https://learn.microsoft.com/en-us/archive/blogs/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism

something along these lines - it assumes that none of the duplicates already contain a row that is 1 hour ahead

if object_id('tempdb..#keys') is not null
  ...

if the proc is running then using sp_whoisactive MAY give you the current explain plan - to got it the command would be

exec sp_whoisactive @get_plans = 1 -- or =...

you need to install IBM's odbc driver for I series - see https://www.ibm.com/docs/en/i/7.2?topic=oa-set-up-your-system-i-access-odbc-driver

with it you can use SSIS or C#/Powershell to connect to your as400 and then load data onto...

Ring knocker here. I have 25+ years of SQL Server experience, you should listen to what I have to say. 😉

One a personal note, continuous learner here as there...

and speaking of blocking.

whoever did the code does not really understand what "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" and "with (nolock)" do.

apart from the fact that it allows you to...

I've just looked at one of our processes - a 16 Million rows file, 5.5GB in size, 50 columns (mix of char, date, ints) - loading from a NAS share...

7 hours for 7GB... they definitely doing something wrong (like...

splitting the file should be last resource after you try out the remaining options.

• how many rows on file
• spec of server where SSIS runs
• spec of destination SQL server...

following "works" - but see comments

select t.objid
     --, t.COMMENT 
     , times.*
     , team.chg_ass_team
 ...

update will take longer but none are fast - see https://www.sqlservercentral.com/steps/stairway-to-columnstore-indexes-level-6-updating-and-deleting-data-in-a-columnstore-index

both will require index reorg to get ride of the "deleted rows" from the table.

for large etl like this one...

following up on Phil answer - go and read these 2 links.

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

https://www.sqlservercentral.com/articles/how-to-post-performance-problems-1

and as an IRL example of what Phil is asking for - you Can't build a car if the...

would be good if you could supply the full code of all required functions - as well as part of what the invocation of it is - even the invocation...

code above formatted in case anyone interested in seeing what it it

with failedjobs
as
(select serverproperty('Servername') as ServerName
      , sysjobhist.job_id
    ...

thanks for feedback.

as that does not seem to work I now ask if the calling of the main function

SELECT * FROM

dbo.fn_GetMatchingItemsForGlobalSearchSOC('111889,111891,111899','12181', 3)

can be change so that the...