Poor Query Performance

[mkelly11]

I have a team member that is trying to run the following query...

SELECT
DISTINCT REGID,
TBL_02ND_NAME_DOB_SEX_ZIP.*
FROM
TBL_02ND_NAME_DOB_SEX_ZIP LEFT JOIN TBL_07TH_DOB_SSN
ON (TBL_02ND_NAME_DOB_SEX_ZIP.DOB_SSN = TBL_07TH_DOB_SSN.DOB_SSN
OR TBL_02ND_NAME_DOB_SEX_ZIP.NAME_DOB = TBL_07TH_DOB_SSN.NAME_DOB)

aside from the poor naming convention used, here's the issue:
He is trying to find records between two tables where either the DOB and SSN (concatenated field) match or the Name and DOB (concatentated field) match.
This query is currently taking 7 hours to run, the fields are varchar, and each table has about 50,000 records.
Any idea's on optimizing this query?

 

[JayKusch]

Do you have indexes on these fields. It sure will help!

Thanks

J. Kusch

 

[mkelly11]

Yes the fields are indexed. I know there is a way to run this query through profiler, but I'm not certain how to capture just this query. If I'm on the right path, please send me a suggestion

 

[fluteplr]

If it is taking 7 hours on a 50,000 record table something else has got to be going on also.

You might try two queries with a union.

SELECT
REGID,
TBL_02ND_NAME_DOB_SEX_ZIP.*
FROM
TBL_02ND_NAME_DOB_SEX_ZIP inner join TBL_07TH_DOB_SSN
ON TBL_02ND_NAME_DOB_SEX_ZIP.DOB_SSN = TBL_07TH_DOB_SSN.DOB_SSN
union
SELECT
REGID,
TBL_02ND_NAME_DOB_SEX_ZIP.*
FROM
TBL_02ND_NAME_DOB_SEX_ZIP inner JOIN TBL_07TH_DOB_SSN
ON TBL_02ND_NAME_DOB_SEX_ZIP.NAME_DOB = TBL_07TH_DOB_SSN.NAME_DOB)


I am not sure why you are using a left join and the union will take care of the distinct for you.

 

[zhavic]

I think, you has wrong condition in JOIN clause.
For example, if that condition is bad, there may be that all or rows from one table can be joined to all rows from the second table .
Based on that you sad ( 50.000 rows in each table ), you can get 50.000 * 50.000 rows, and this can take some hours to get result.

Try to run query analyzer, check the 'Show execution plan' option in Query menu ( or press CTRL+K ), run your query, wait for 7 hours and let's see how many rows it uses. ( click on lines in execution plan and see 'Row count' property )


Also, the DISTINCT statement does query to be slower. Try to use GROUP BY clause, if it is possible.


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[mkelly11]

Thanks for the many suggestions! The problem turned out to be with the table design,(I decided to take a look instead of taking my co-workers word for it) After changing the datatype and field lengths of several columns, and then adjusting the query to use the correct fields (instead of the concatenated values) the query only took 3 seconds to find all matching records and 1 second to find all unmatched records.

Thanks again for the help.