Skip to content

Posts tagged ‘SQL’

Inaport 7.3 – Dramatically improving matching performance using local cache tables

With the rise of CRM systems such as Microsoft CRM 2011 which are accessed via web services, performance becomes more of an issue. Analysis of Inaport projects indicates that for a typical import into a web hosted CRM system, up to 99% of the time may be spent in the web service calls to the target CRM system.

An important objective, then, is to minimize the number of web services calls as far as possible. This post describes some techniques available in Inaport 7.3 that can dramatically reduce or eliminate the cost of  matching on the target system, by removing the need to make any web services calls.

While these techniques have the biggest impact with web services based systems, they can also be very useful for on premise based systems such as SalesLogix.

It is a long and moderately technical post. A subsequent post will provide some examples of using these caching techniques. If you have any questions, it is likely that other readers do as well – feel free to ask for clarification in the comments, or to contact InaPlex direct.

The basic technique is caching the primary key of the target records in the source database. This can be done by one of:

  1. updating the source record with the primary key of the matched record in the target;
  2. using a cross reference table, that stores the primary keys of the source and target records, along with match information;
  3. building a match table that stores the primary key of the target, along with required match information.

Each of these techniques is useful in different scenarios.

Read more

Cross database queries – or, how to rescue a bad database

Even InaPlex gets it wrong occassionally.

It was late, we were tired, instructions from the client were not clarified properly… and the database was not backed up before a SQL update was issued that created some real problems. The SQL update modified the primary userid of most of the account records in the CRM system.

Of course, the problems were not discovered until the next morning – second last day of the financial year for the client, and the sales people could not see all their accounts in the CRM system because they no longer had appropriate permissions.

The project manager at the client was very polite, but was clearly under substantial pressure to get it fixed.

But the database was live, had been modified, and the most recent backup was a few days old. Rolling back to the backup was out of the question, continuing with the current system was impossible for the sales people.

All in all, not a good situation.

Read more

SQL for Data Quality

I gave a workshop on managerial and technical challenges of integration projects at Sage Insights in Nashville last week.

One of the areas I covered was some useful SQL queries for checking data integrity during a migration; the example used was from a Siebel to SageCRM migration project we are currently engaged in. I had a number of requests from audience members for details of the queries, so here they are.

Read more

%d bloggers like this: