Speeding up Import times from SQL MAs

1. Snapshot Views to Tables

If you are using a view to bring data from different tables into one location you may well find that your import times improve dramatically when you first copy the data into a table.

Create a Table with the same fields as the view, in the same order. Take care with the data types and field lengths. Then run this SQL query:

INSERT INTO <TABLE>
SELECT * FROM <VIEW>

2. Generate Delta tables

MIIS supports delta imports from SQL, but you have to create the Delta tables. This is a whole post in itself, so more on that coming.

3. Separate into single-valued and multi-valued MAs

It is perfectly possible to pile all your single- and multi-valued attributes into one MA. Say you already have an MA that is importing information about Users and Groups, why not add a multivalue table and do the group memberships at the same time?

Well I guess it depends on the amount of data you’re dealing with. I had 6000 groups and almost 40,000 user accounts and, when I tried to pile all that onto one MA, a full import was taking around 12 hours! As far as I could tell, there is an exponential, rather than a linear, relationship between the amount of data coming in, and the slowness of the import job. It’s not just that there’s more data to import – the rate at which it is imported becomes slower as well. (Hint: you can watch the import rate in Performance Monitor.)

The solution, for me, was to split my MAs into:

  • People (single-valued attributes only)
  • Groups (single-valued attributes only)
  • AD Multivalue (multi-valued attributes destined for Active Directory)
  • LDAP Multivalue (multi-valued attributes destined for LDAP)

Again it may seem unnecessary to have those two Multivalued MAs, but I was just trying to get the import times down. While the full import of the LDAP Multivalue MA took 2½ hours, the complete set took under 4 hours – a great improvement on 12!

About: Carol

I've been doing IT for 30 years, and IdM for 15. I live in Australia and build IdM solutions based on Microsoft Identity Manager. I also play the violin, but that doesn't help much with the IdM solutions.