{"id":11,"date":"2007-05-27T10:13:52","date_gmt":"2007-05-27T10:13:52","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=11"},"modified":"2023-01-16T06:05:45","modified_gmt":"2023-01-16T06:05:45","slug":"speeding-up-import-times-from-sql-mas","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/speeding-up-import-times-from-sql-mas","title":{"rendered":"Speeding up Import times from SQL MAs"},"content":{"rendered":"

1. Snapshot Views to Tables <\/em><\/p>\n

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.<\/p>\n

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:<\/p>\n

INSERT INTO <TABLE>
\nSELECT * FROM <VIEW><\/span><\/p><\/blockquote>\n

2. Generate Delta tables<\/em><\/p>\n

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.<\/p>\n

3. Separate into single-valued and <\/em>multi-valued<\/em><\/a>\u00a0MAs<\/em><\/p>\n

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?<\/p>\n

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.)<\/p>\n

The solution, for me, was to split my MAs into:<\/p>\n