Why not open the PST files with a local copy of Outlook and automate the email extraction from there?
Speed: impossible to say without looking at the architecture of the solution and what the users (or the system) does with the data. Having several million records in a table is not in issue in and by itself. If that table however has 200 fields and most of them are calcs (stored or unstored) and you have layouts with many portals and summary fields on it then you are going to feel a lot of pain
So the design is the key, not the amount of data. Having a lot of data is going to expose design flaws more quickly obviously.
Not going to be a lot of fields: Simply the typical email send/recieve fields,
I assume that I should:
- Normalize the tables: CRM>JoinCRM_Email>Email
- Query the join table
- NOT have the jointable fire in the viewEmailPortal in the CRM UNLESS click a script to load the portal with data
What other best practices to keep this lean and fast?
- ... in the viewEmailPortal ...
That's a bit of a red flag right there (realize that I can just go by the description you've provided)
Portals can be expensive, especially if they use a fairly generic relationship and you then filter on the client side.
Any reason why you can not simply show a list view of the emails instead of a portal?
To keep things lean:
- avoid many portals
- certainly avoid tabbed layouts with portals on each tab (same applies to slide panels)
- avoid sorted relatiosnhips if you can
In general, try to reduce the amount of data on any one layout and reduce the # of records the user is sent.
we use the PCEM Plugin (Productive Computing Exchange Manipulator) to talk to Outlook and extract each Mail via plugin-call.
I cannot imagine that reading the raw pst file is going to be an easy task to programm - but do let me know if you find a solution!
- Create a lean mininimum fields layout for just email....
- keep the attachments in a separate table and only show if that email is selected....
- to view the email attached to a contact's record, pop up a window NOT a portal, and have it show and interact from there.
- Here's one that can run as a service and update a database. http://www.email2db.com/introduction.aspx