The total table count is for STIPS is under 1,000 It could grow to about 10,000 over 4 years

The output is a few hundred records. There are only about 10 fields in the table.

The primary ID used in STIPS is under 15,000. Not all of them have STIPS. Usually, if there is a STIP, there re several.

The suggestion above for nesting the Select query in an execute for calling Oracle is interesting.

The SQL Server Linked Server uses the Oracle Java based ODBC via Listener.

From the Linked Oracle Server, a read-only view representing an Oracle table is created in the SQL Server DB.

A very simple Select * from <Oracle Linked Table view> where Primary Key ID = X takes over 8 seconds to respond with the 1st record.

Then, the rest of the records appear very quickly.

Simple table joins go downhill quickly and can take 10+ seconds to respond.

I think the ODBC to Oracle is an overwhelming bottleneck.

Perhaps I should start a new thread detailing how the SQL Server Linked Server through the View to the database view is constructed.

I will continue with the Execute suggestion and the IN (instead of the Where) for now.