I have a job which performs and update or insert on a table but it currently takes too long to run. In the past I've replaced a single dboutput component with 2 and used a map inner join to split out the inserts and updates so I can more efficiently deal with the data.
In this particular job I'm not getting the results I'm expecting. The inner join seems to be rejecting too many rows and I'm getting errors about inserting duplicate keys from the dbOutput that is configured for inserts.
I've confirmed in the database that the majority of the data that is being rejected by the inner join exists in the database.
Can anyone help me in diagnosing what is wrong with the job or what I've missed?
Thanks @TRF and @nthampi for your help. Through adding extra debugging I eventually discovered that the consumer_key column in my db was a char field and any values that were shorter than the length of the field had trailing spaces added as padding. I changes the settings on the MSSQLInput to trim String/Char columns and now it works perfectly.
Probably due to your inner join condition or maybe an upper/lower case problem. You don't share enough information to be sure of what happen (complète tMap and sample data for each case should be great)
Any ideas where to start there? I've confirmed that the keys from the main input that are being rejected by the inner join actually exist in the output table. I'm assuming that means they exist in the lookup table as there's no filtering there
I'm unable to share a full data set but I can share keys (they are what's being matched anyway)
The keys are all 64 byte hashed strings. the list below is the first 20 that are output from the inner join reject. I've copied these into a sql query against the same table using a where key in query and have found all of them in the db so I don't understand why they are being rejected by the inner join
Could you please try the Unique Match option in tmap and let us know the results? I could see that you are using All Matches option in your current join.
Since you are trying to check whether the incoming data is having a matching record in DB, unique match should be enough in your case.
Thanks @TRF and @nthampi for your help. Through adding extra debugging I eventually discovered that the consumer_key column in my db was a char field and any values that were shorter than the length of the field had trailing spaces added as padding. I changes the settings on the MSSQLInput to trim String/Char columns and now it works perfectly.
