Weird execution issue

Question

Weird execution issue

radb4u

SSC Eights!

Points: 917
More actions
September 8, 2011 at 3:01 pm

#390370

Hi,
I have developed a package and completed the performance test also for which I had to make few changes. Suddenly today it started hanging on execution. I moved back to previous versions where it didn't have the latest changes and it used to work fine. But, still its hanging on at old version too. When I started debugging, its givign error at one lookup which was completely fine before. At the progress tab its giving following error:
[Lookup ncoa initial [2605]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data.
The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only.
Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.
The lookup query:
select c.biCustomerID, c.dtLastEmailMatchAttempt, c.dtLastPhoneMatchAttempt, c.dtCustomerDateOfBirth, ca.biAddressID, c.vchCustomerFirstName, c.vchCustomerLastName, nf2.vchName from ActiveCustomer c
inner join ActiveCustomerAddress ca on c.biCustomerID = ca.biCustomerID
left outer join NicknameFamilies nf1 on c.vchCustomerFirstName = nf1.vchName
left outer join NicknameFamilies nf2 on nf1.iNicknameFamilyID = nf2.iNicknameFamilyID
I am completely lost. Please help me.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Apparently the reference data contains some duplicate values, so you'll need to clean that up a bit.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kramaswamy SSCoach Points: 18181 More actions · Answer 2

I assume you probably have new data inputted in one of your tables which is causing the rows you are returning to have some duplicates.

Try running the same query in SSMS, using GROUP BY and HAVING COUNT(*) > 1:

select c.biCustomerID, c.dtLastEmailMatchAttempt, c.dtLastPhoneMatchAttempt, c.dtCustomerDateOfBirth, ca.biAddressID, c.vchCustomerFirstName, c.vchCustomerLastName, nf2.vchName from ActiveCustomer c

inner join ActiveCustomerAddress ca on c.biCustomerID = ca.biCustomerID

left outer join NicknameFamilies nf1 on c.vchCustomerFirstName = nf1.vchName

left outer join NicknameFamilies nf2 on nf1.iNicknameFamilyID = nf2.iNicknameFamilyID

GROUP BY c.biCustomerID, c.dtLastEmailMatchAttempt, c.dtLastPhoneMatchAttempt, c.dtCustomerDateOfBirth, ca.biAddressID, c.vchCustomerFirstName, c.vchCustomerLastName, nf2.vchName

HAVING COUNT(*) > 1

I expect you'll find some rows. If you fix whatever is causing the duplicate rows to appear, then youl'l be fine. If you can't, then you'll need to leave the GROUP BY clause in there.

radb4u SSC Eights! Points: 917 More actions · Answer 3

Thank you so much for your responses. I have tried group by but, I need duplicate values (but not vchName) as I am trying fuzzy lookup manually (as existing direct fuzzy lookup not works for remote systems). So, I am collecting all the related (sounds similar) name for given last name. But, I will try to change the query according to your suggestions.

Thanks

kramaswamy SSCoach Points: 18181 More actions · Answer 4

Something you might want to look into is SOUNDEX encoding.

Might be useful - try it out:

SELECT

SOUNDEX('John'),

SOUNDEX('Jon'),

SOUNDEX('Jonn'),

SOUNDEX('Jhon'),

SOUNDEX('Smith'),

SOUNDEX('Smth'),

SOUNDEX('Smithh'),

SOUNDEX('Smit')

All the firstnames and lastnames end up with the same SOUNDEX code. So you could use that maybe as a way of "fuzzy" matching your lastnames.

radb4u SSC Eights! Points: 917 More actions · Answer 5

Sorry for the late reply. But, very thankful to your advice.