Community
Showing results for 
Search instead for 
Do you mean 
Reply

Opportunities slow to load after bulk import

Copper Contributor
Posts: 8
Country: USA

Opportunities slow to load after bulk import

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:

5,000 Contacts

5,000 Companies

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 15.1.108.0, 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

 

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: Opportunities slow to load after bulk import

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.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Bronze Elite Contributor
Posts: 2,548
Country: New_Zealand

Re: Opportunities slow to load after bulk import

You refer to Premium for Web. Are these load times through the desktop or browser view?

Graeme Leo
Xact Software - consultants and developers
Follow us on Twitter and check out our Blog


Copper Contributor
Posts: 8
Country: USA

Re: Opportunities slow to load after bulk import

[ Edited ]

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

 

 

Bronze Elite Contributor
Posts: 2,548
Country: New_Zealand

Re: Opportunities slow to load after bulk import

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.

Graeme Leo
Xact Software - consultants and developers
Follow us on Twitter and check out our Blog


JM
Nickel Contributor
Posts: 584
Country: USA

Re: Opportunities slow to load after bulk import

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.

JM

Copper Contributor
Posts: 8
Country: USA

Re: Opportunities slow to load after bulk import

@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).  

I ran:

Check and Repair (ACT)

Refresh Analytics (ACTDIAG)

Refresh Search Indexes (ACTDIAG)

Reindex Database (ACTDIAG)

 

The differences before and after were negligable.

Bronze Elite Contributor
Posts: 2,548
Country: New_Zealand

Re: Opportunities slow to load after bulk import

I'd be interested to see your stats on the outcome, appreciate your reporting them here.

Graeme Leo
Xact Software - consultants and developers
Follow us on Twitter and check out our Blog