Thanks for that.   We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.

I'll give it a go with a test database...

Thanks for that.   We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.

I'll give it a go with a test database and see...

create an extended event session and log all the code being issued by the pc where you execute that app - that will give you full list of sql statements...

search for the content instead of the object id

SELECT * 
FROM sys.sql_modules
WHERE definition like '%usp_drop_temp_ingenix_codes%' 
and see what you get.

I would also recreate the SP just in...

most of the code looks like someone used to Oracle that was moved to work with SQL Server - in Oracle world majority of things are case sensitive - and...

check if someone had DENY'ed create tables in TEMPDB- or even deny'ed other permissions that could cause the issue.

It is the Portuguese for AI

how many rows of tbl A (18900000  total) have the ReferenceNum = 0 ?

and why aren't you filtering them when doing the insert into the temp table?

I also see, dev team is using DISTINCT operator as a common practice. Is there any proper ways to re-write to the queries to avoid the usage of distinct...

You said in your narrative in your original post that "it reads last 30 mins of data from the tables involved" and yet ALL of your...

before we can even suggest anything else...

1 - WHY are they returning 40 Million rows on a function call? what purpose does this accomplish?

ask the devs what is this output...

• This reply was modified 2 years, 8 months ago by frederico_fonseca.
• This reply was modified 2 years, 8 months ago by frederico_fonseca. Reason: add table name to proposed indexes

can you also get a ACTUAL execution plan of doing those sqls

and there is some duplication of access to table C_B_PARTY

take following snippet.

take following snippet.
first INNER join...

• This reply was modified 2 years, 8 months ago by frederico_fonseca.

If you want a load into a new table, then create the empty table AND create the Clustered Index.  Then, forget SSIS... use BULK INSERT with the TABLOCK option...

helps to read the documentation.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

search for tuple-mover

Sql server edition (express, standard, enterprise) - developer --- if you are using this for Production usage you CAN NOT use developer license - if it is only for development...