Union Query is slow , how to speed up? ( Find duplicates from 2 combined tables)

[fluffyvampirekitten]

I have two tables , CrossSystemData and RequestUpdateData .
Table RequestUpdate Data have 1000 to 5000 data while CrossSystemData have around 20 000 ++ data .
I want to find duplicates for both tables so I'm using the "UNION"
However , it take forever to display the results .
Is there any way that I can speed the process ?
Thanks in advance .

This is query swl statement for Union

SELECT CrossSystemData.[PATIENT_ID], CrossSystemData.[PATIENT_NAME], CrossSystemData.[GENDER], CrossSystemData.[DOB], CrossSystemData.[C_SOURCE], CrossSystemData.[CATEGORY_TYPE]
FROM CrossSystemData
UNION ALL SELECT RequestUpdateData.[EXT_PAT_ID], RequestUpdateData.[PATIENT_NAME], RequestUpdateData.[GENDER], RequestUpdateData.[DOB], RequestUpdateData.[C_SOURCE], RequestUpdateData.[CATEGORY_TYPE]
FROM RequestUpdateData;

The below sql statement is to find the duplicate values for both tables

SELECT UnionQuery.[PATIENT_ID], UnionQuery.[PATIENT_NAME], UnionQuery.[GENDER], UnionQuery.[DOB], UnionQuery.[C_SOURCE], UnionQuery.[CATEGORY_TYPE]
FROM UnionQuery
WHERE (((UnionQuery.[PATIENT_ID]) In (SELECT [PATIENT_ID] FROM [UnionQuery] As Tmp GROUP BY [PATIENT_ID] HAVING Count(*)>1 )))
ORDER BY UnionQuery.[PATIENT_ID];

[orange]

What exactly does this mean?

I want to find duplicates for both tables

Can you give us an example or two?

[JamesDeckert]

you have a query which combines data from two tables, then looks to see if the ID occurs more than once in the table.
Wouldn't be simpler to just have one query which selects each record from the first table and checks to see if the ID occurs IN the second table?
I'm assuming the ID is a PK and is not duplicated within a single table.

[orange]

Good guess James.. I was wondering given tables A and B, are we looking for duplicates in A, and/or duplicates within B
or possibly records in A not in B, or records in B not in A, or records in A and B....

[fluffyvampirekitten]

you have a query which combines data from two tables, then looks to see if the ID occurs more than once in the table.
Wouldn't be simpler to just have one query which selects each record from the first table and checks to see if the ID occurs IN the second table?
I'm assuming the ID is a PK and is not duplicated within a single table.

Yes, I have a query that combines from two tables and find Duplicate IDs from the combined table .
I didn't set the ID as a Primary Key because there might be duplicates.

Example:
Scenario 1
Table A
ID : ABC123
Name: Ben
Category : Cross

Table B
ID : ABC123
Name: Ben
Category : Update

Final result :
ID | Name| Category
ABC123|Ben | Cross
ABC123|Ben | Update

Scenario 2
Table A
ID :XYZ99
Name: Apple
Category : Cross

ID :XYZ99
Name: Apple
Category : Cross

Table B
ID : XYZ99
Name: Appplee
Category : Update

Final Result
ID | Name | Category
XYZ99| Apple | Cross
XYZ99| Apple | Cross
XYZ99| Appplee | Update

[fluffyvampirekitten]

I'm looking duplicates in within B .
Records in A and B .

[fluffyvampirekitten]

Good guess James.. I was wondering given tables A and B, are we looking for duplicates in A, and/or duplicates within B
or possibly records in A not in B, or records in B not in A, or records in A and B....

I'm looking duplicates in within B .
Records in A and B .

[JamesDeckert]

so that's why it's taking so long. each record is searching every record of the combined two tables for a duplicate id.
100 records would be about 100x99 searches. This will go up exponentially with more records.
Is the ID at least indexed?

[fluffyvampirekitten]

so that's why it's taking so long. each record is searching every record of the combined two tables for a duplicate id.
100 records would be about 100x99 searches. This will go up exponentially with more records.
Is the ID at least indexed?

Yeah , is there any other approach?

The ID's are not indexed , will it be faster if the IDs are indexed ?

[orange]

Indexing will improve speed.
Fundamental to relational database is the use of Primary keys.

You could also try UNION (no duplicates) instead of UNION ALL(duplicates and replicates).