Checking for Duplicates, Except

Question

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_CT
FROM  (SELECT  NAME, DBSTATUS, COUNT(NAME) AS NAME_CT
       FROM  TABLE1
       WHERE  DBSTATUS IN (1,2)
       GROUP BY NAME, DBSTATUS
       HAVING COUNT(NAME) > 1) ISSUES, 
       TABLE1
WHERE ISSUES.NAME = TABLE1.NAME 
    AND ISSUES.DBSTATUS = TABLE1.DBSTATUS
ORDER BY CATID, NAME, DBKEY

The 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_CT
FROM  (SELECT  NAME, DBSTATUS, COUNT(NAME) AS NAME_CT
       FROM  TABLE1
       WHERE  DBSTATUS IN (1,2)
       GROUP BY NAME, DBSTATUS
       HAVING COUNT(NAME) > 1) ISSUES, 
       TABLE1
WHERE ISSUES.NAME = TABLE1.NAME 
    AND ISSUES.DBSTATUS = TABLE1.DBSTATUS
ORDER BY CATID, NAME, DBKEY
')drv
WHERE (CATID IN(SELECT CATID FROM RACK_CATIDS) AND DBSTATUS = 1)
OR (CATID NOT IN(SELECT CATID FROM RACK_CATIDS))

Answer 1

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 happen

---

There really isn't anything that you can change here.

Answer 2

Let me point out some things that I know can be improved so that other answers can focus on my problem.

SELECT *

I shouldn't be selecting all like this. Even if there's not really a performance hit because open query needs to get all of the meta data anyway, this is a bad habit to get into. Selects should be explicit in what we're selecting.

'SELECT  SUBSTR(NAME, 1, 3) AS CATID,

CATID isn't a very good alias. CategoryId would be much better and clearer to someone following me someday.

   WHERE  DBSTATUS IN (1,2)

This needs a comment saying what these DBSTATUSes actually represent. Mr. Maintainer would have to go find the lookup table in order to find out. (And this query gives no hint that the lookup table even exists.)