07-30-2013 07:51 AM
I've imported data from an old ACT database (where the database is all on the Contacts record...500+ fields) to a new ACT database (where 35 events made up of several fields on the old Contact record are imported as 35 Opportunity records per Contact record).
Our database has:
150,000 Opportunities (all newly imported)
Since the import:
It takes 6 minutes to load the Opportunities List
It takes 1-6 minutes to perform an Opportunity lookup
It takes 1 minute to load the Opportunities tab (from a Contact/Company view)
It takes 20-30 seconds to sort the Opportunity tab columns
I know 150k records is a lot, but it doesn't seem like so many that ACT should take this long to pull them up.
Anyone have ideas on speeding up these load times? Would ACT be doing any extra calculation on recently created records that might cause something like this?
I'm currently looking at using a custom table to store these records as an alternative if I can't get the Opps to load in a timely manner.
We're using the latest version - Sage ACT! Premium (Web) 2013 Version 18.104.22.168, Hot Fix 3.
The server hardware is dual-core 2 GHz Xeon, 4 GB ram, 80 GB HDD w/Win 2008 R2 x64 - everything else in ACT runs pretty quick
07-30-2013 09:17 AM
Personally I would increase the RAM for starters, I run 8 GB on one destop system and 12 DB on the other. Also what's the hard drive speed in RPM.
The ACT! program is more sensitive to I/O rather than processor speed.
07-30-2013 01:02 PM
07-30-2013 01:51 PM - edited 07-30-2013 01:52 PM
I'm only testing Desktop performance at this point.
Also to note, I've copied the database to a faster computer and the results are better (down to about 3min for the Opps List, about 30 sec to open the Opps tab within in Company/Contact and still about 30 sec to sort columns), but still not performant enough for our sales team to pull information in a timely manner.
The faster computer specs: Intel i7 2.8 GHz quad core, 12 GB ram, 120 GB SSD on Win 7 x64
07-30-2013 04:03 PM
Short of understanding the exact nature of what you are doing with Opportunities, it is our experience with clients with large record numbers such as you have, and using the database proactively, that ACT! is stretched to handle that quantity of Opportunity records when it comes to displaying the data. For instance the whole list is being pulled rather than exposing pages of data. If for instance you were to change a record in the list view annd save, ACT! would then refresh the whole list again - that's not ideal So yes, while ACT! is a SQL database the limitation you are experiencing is not at the SQL level nor just a hardware resource issue.
Overcoming this is not a trivial task and would need re-design in the way that ACT! displays data. We advise people to purge records that they no longer need or trim down the fields to just the bare essentials.
07-30-2013 09:49 PM
After your import did you do from Tools | Database Maintenance | Check and Repair.
Then close ACT and from Windows Serach Programs and Files type in ACTDIAG.EXE and hit Enter to get it open and run the Check DB | Then run the Reindex step.
You can also run the trial Exponenciel add-on http://exponenciel.com/actaddons/actaddon.aspx?ID=124 to see how many of each type of History records you have. Run a test from a copy of DB to delete hsitory records then run the ACTDIAG reindex steps and see if that helps.
07-31-2013 10:43 AM
@gleo Thanks for the info. I agree purging old records is a good way to maintain performance and managability. I won't have that luxury for this database, the historic data is required. I did run SQL Profiler to get an idea of why the load times were so long and it appears to boil down to a specific complex query. My next step is going to be using a custom table to load the opportunities instead and I'm hoping that won't incur the same performance penalty.
@JM I'm glad you mentioned that. I did run through the database maintenance routines (both in ACT and ACTDIAG).
Check and Repair (ACT)
Refresh Analytics (ACTDIAG)
Refresh Search Indexes (ACTDIAG)
Reindex Database (ACTDIAG)
The differences before and after were negligable.
07-31-2013 11:03 AM