Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Determine which columns should be updated during an incremental load Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 5:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:26 AM
Points: 119, Visits: 483
Hi Folks,

I have the following Scenario: I receive customer data every day from an external Company over an ETL process. I store this data in a database called the Data Hub. The customer table has two columns to identify new and updated records: CreateDt (timestamp) and UpdateDt (datetime). I need to load these data into a Dynamics CRM. For the Initial load all is clear, but to perform incremental loads over the CRM entities I've proposed the following strategy: if a record has a CreateDT or UpdateDT subsequent to the last loading should cause an insert or an update respectively. The whole row content should be in the correspondent CRM entity updated.
Up to here everything seems to be ok. The problem is, the CRM guys have complained , because the load is performed over a Web Service using a CozyRoc component and the process is very slow. Furthermore, the update of some fields Trigger several CRM internal procedures. So they ask me to update only the fields that have changed.
I think this is time expensive too, and I couldn't figure out how I could do that without comparing row by row and column value by column value. I have about 20 columns. I can dynamically create each query checking the value column by column but for me this is the Flintstone’s Style way, and I find it even worst as the CRM latency Problems.

Any other proposed solution? It will be appreciated it.

Kind Regards


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1438726
Posted Thursday, April 4, 2013 5:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
Can you get to the database directly, or do you have to use the web service method?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1438731
Posted Thursday, April 4, 2013 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:26 AM
Points: 119, Visits: 483
Hi Phil thanks for your answer.

Now I'm trying to persuade my colleagues to go directly to the database and not over the web service. They allege that write directly is unsupported and I say that the web service for this task is like to go deep in the jungle. The major problem is any modification in any CRM attribute triggers an activity (modification, modified by, modified on). Some fields have additional events.
Even worst, I cannot dynamically configure the mappings in the component that calls the web service.
I will try to convince them to use dynamic SQL queries intead.

Kind Regards,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1438754
Posted Thursday, April 4, 2013 6:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
Paul Hernández (4/4/2013)
Hi Phil thanks for your answer.

Now I'm trying to persuade my colleagues to go directly to the database and not over the web service. They allege that write directly is unsupported and I say that the web service for this task is like to go deep in the jungle. The major problem is any modification in any CRM attribute triggers an activity (modification, modified by, modified on). Some fields have additional events.
Even worst, I cannot dynamically configure the mappings in the component that calls the web service.
I will try to convince them to use dynamic SQL queries intead.

Kind Regards,


I suspect that they are correct in their allegations of no support for direct access. But that sure makes things difficult for you - those 'triggers' are presumably set off by the web service call and that's a black box - you don't know what they're doing, other than by observation.

I'll sign off at this point because I don't think I can help. I would not expect the web service method to ever be quick for bulk update operations, so matter how much you refine things - maybe you should cross to the dark side and frequent a CRM forum to see whether they have any suggestions.

Actually, I did have one idea. Writes to the database may be unsupported, but what about reads? These reads could, perhaps, be used to (relatively) quickly identify which columns need to be updated, with just the updates being pumped through the treacle-like update web service.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1438768
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse