I have a query that returns records that have duplicate names within a given DBSTATUS. I used to do this with a direct connection to the remote oracle database server with this query. (Names changed to protect the innocent, but otherwise, this is my code.)SELECT  SUBSTR(NAME, 1, 3) AS CATID,     TABLE1.NAME,    TABLE1.DESC1 AS DESCRIPTION,     TABLE1.DBKEY,     TABLE1.DBSTATUS,     ISSUES.NAME_CTFROM  (SELECT  NAME, DBSTATUS, COUNT(NAME) AS NAME_CT       FROM  TABLE1       WHERE  DBSTATUS IN (1,2)       GROUP BY NAME, DBSTATUS       HAVING COUNT(NAME) > 1) ISSUES,        TABLE1WHERE ISSUES.NAME = TABLE1.NAME     AND ISSUES.DBSTATUS = TABLE1.DBSTATUSORDER BY CATID, NAME, DBKEYThe requirements have changed and now I need to exclude a list of CATIDs from this report, but only when they have a DBSTATUS of 2. This is what I came up with, but I don't much like the duplicated select in the WHERE statement. Since the exclusion table resides on the local server, I have switched to an OPENQUERY instead of a direct connection to the remote server. How can I improve this?SELECT *FROM OPENQUERY(REMOTE_ORACLE_SERVER,'SELECT  SUBSTR(NAME, 1, 3) AS CATID,     TABLE1.NAME,    TABLE1.DESC1 AS DESCRIPTION,     TABLE1.DBKEY,     TABLE1.DBSTATUS,     ISSUES.NAME_CTFROM  (SELECT  NAME, DBSTATUS, COUNT(NAME) AS NAME_CT       FROM  TABLE1       WHERE  DBSTATUS IN (1,2)       GROUP BY NAME, DBSTATUS       HAVING COUNT(NAME) > 1) ISSUES,        TABLE1WHERE ISSUES.NAME = TABLE1.NAME     AND ISSUES.DBSTATUS = TABLE1.DBSTATUSORDER BY CATID, NAME, DBKEY')drvWHERE (CATID IN(SELECT CATID FROM RACK_CATIDS) AND DBSTATUS = 1)OR (CATID NOT IN(SELECT CATID FROM RACK_CATIDS))
Checking for Duplicates, Except
sql;sql server;oracle;ssis
After reading through the question and the code more carefully I have come to the conclusion that I think you are doing everything that you should be doing to make this OpenQuery work correctly.OpenQuery doesn't let you do much, if you can you should have a stored procedure on the originating database that prepares a table for you in advance and then SELECT * from that table in your OpenQuery. After that you can manipulate the data the way you need to on SQL SERVER like you are doing.The only other option is to give the OpenQuery the Query that will return the full data set that you require. The only way to speed this up is to make the query performed on the remote server faster, after that you have connection lag, and that is just going to happenThere really isn't anything that you can change here.