August 17, 2009 at 5:46 am
Hi,
I am running the following query to select a filter out a single row where duplicate records exist from my wce_ilr table then left outer joining the wce_contact table wce_ilr.edrs_no = wce_contact.edrs OR wce_ilr.company_name = wce_contact.company
I am expecting this query to show me rows of data from the wce_ilr table where they do not exist in the contact based on edrs number or company name but after 40 minutes the query in management studio express is still running!
If I do this query with just the wce_ilr.edrs_no = wce_contact.edrs or just wce_ilr.company_name = wce_contact.company I get my results straight away.
Problem is the OR and I presume amount of data it is checking against. Is there any advice on this matter someone can offer? Or another method to achieve the end result I need?
Thanks in advance.
SELECT edrs_no, company_name, company_code, Employer_address_1, Employer_address_2, Employer_address_3, Employer_address_4, Employer_POSTCODE, 'employer', 'Company'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY wce_ilr.company_name
ORDER BY wce_ilr.company_name) r, wce_ilr.company_name,
wce_ilr.company_code, wce_ilr.edrs_no,
wce_ilr.Employer_address_1, wce_ilr.Employer_address_2, wce_ilr.Employer_address_3,
wce_ilr.Employer_address_4, wce_ilr.Employer_POSTCODE
FROM wce_ilr LEFT OUTER JOIN
wce_contact ON wce_ilr.edrs_no = wce_contact.edrs OR wce_ilr.company_name = wce_contact.company COLLATE database_default
WHERE wce_contact.edrs IS NULL AND wce_ilr.edrs_no IS NOT NULL
) A
WHERE r = 1
August 17, 2009 at 6:04 am
It's hard to give an advice without having the whole picture.
I suggest you take a look at this article http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/ and provide as much information as you can.
Regards
Gianluca
-- Gianluca Sartori
August 17, 2009 at 7:26 am
Thanks for the reply. The query just finished over 2 hours later and only had 700 rows.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply