No, adding a COMMIT will not speed it up, the associated explicit BEGIN TRAN will likely slow it down slightly.  SQL will (auto)commit after the statement finishes anyway.

Be sure you...

You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block.  Checking XACT_STATE in the...

You need to step back and do proper data modeling on all your entities.  That is the most important step in proper design.

Then, as you convert the entities to physical...

SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'".  Google the syntax of the command.

You'll need to use dynamic SQL if you want to feed in a FROM file...

I'm guessing you don't already have a row_num.

;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS row_num
 ...

Would need to see the tables' DDL and the actual query plan.  Can't troubleshoot air.

Yeah, really should have mentioned that Pervasive thing to begin with :-).  That means there are multiple rows being returned for each lookup.  Which one you get will be random,...

Another possibility is a bit shorter, uses only LIKE instead of LIKE and the LEN() function, and does the same thing is...

IIF(D.Zip LIKE '[0-9][0-9][0-9][0-9][0-9]', d.Zip,...

--...
      ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
          ALTER DATABASE [DBNameToRestoreHere]...

I say don't use IIF.  It's a completely foreign coding approach to other SQL.  And there's no genuine need for it, since CASE can do the same thing, and IIF...

If you want to rely on the existing zip always being fully padded on the left with zeros, you can do this:

CASE WHEN LEN(D.Zip) = 5 AND...

You could probably use TRIGGER_NESTLEVEL to do that.

I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>').  If you want more info on this...

Don't use functions in a WHERE clause, that's a very bad habit.  Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some...

'19 Nov 2022' is converts to the datetime value of '19 Nov 2022 00:00:00.000', which is earlier (less than) '19 Nov 2022 15:19:12.000'.

Try:

WHERE [...]
AND CONVERT(date, TransferredDate)...

And the view did not exist before the proc was called and still did not exist after it was called?

If so, put the SQL in a variable and print it...