Community
Showing results for 
Search instead for 
Do you mean 
Reply

How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

JM
Nickel Contributor
Posts: 584
Country: USA

How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

I just learned a trick for either a bug, or my DB just exceeds the normal ACT user's DB record count.  After years of Randomly happening to painfully watch / wait on 20,000-75,000 records when I need to perform on a field the Edit Copy, Swap, or Replace field that could take 3-10 hours now only takes 3-8 min.

 

I would Lookup on a field for that Contains Data (or whatever) that may result listing +20,000 contact records. If you omit one record from the results list view then perform the Edit Copy, Swap or Replace it speeds right through them.

JM

Bronze Super Contributor
Posts: 1,679
Country: Australia

Re: How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

I have to admit, I have never tried this, but when I'm next going to do this, I'll test this out, see if it's just something on your system or if it's a 'trick' we can all use.

Skilled Commentator
Posts: 71
Country: United Kingdom

Re: How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

Amazing - It does

 

Any thoughts on why this happens?

Copper Contributor
Posts: 82
Country: Australia

Re: How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

We just tested this.  And we did a replace on 56248 contacts.

 

We are using Act! 17.0.151.0 and Act! DB is sitting on an SSD.

 

 Test 1

1. create a lookup

2. Edit | Replace Field...

 

Timed replace at 6min

 

Looked up all contacts and performed the same lookup.

 

Test 2

1. create a lookup

2. omit one contact

3. Edit | Replace Field

 

Timed replace at 65sec

 

I'm really surprised this worked tbh.  I tested this a couple more times and same results.

 

I ran the test 1 again and it was really slow second time round.  I closed Act! and restarted SQL and could not get the contacts done in 6min again.  But test 2 is still fast.

 

 

Thanks

Damon

Employee
Posts: 102
Country: United_Kingdom

Re: How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

Interesting.

 

Has anyone experienced this on v18?

JM
Nickel Contributor
Posts: 584
Country: USA

Re: How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

[ Edited ]

The Tip I offered in 2013 was done back in the standard HDD days and still helpful for V18.

Be sure after you make massive number of field data changes or touch lots of records to run ACT Tools, Database Maint., Check and Repair... which will do a Reindex.

For added measure every couple months or after a major record(s) change do a backup then go through the ACTDIAG.EXE - Database - Actions to repair rebuild and even the Database Rebuild steps.

 

Even with today's new PC's with Windows 10, faster RAM technology, PCIExpress Graphics chips(cards), SSD drives connected to motherboard with SATA-6 all are awesome way to increase ACT! speed.

I use Samsung SSD drive so be sure to run the Magician wizard to Optimize SSD by turning on if your PC motherboard BIOS supports them S.M.A.R.T., Over Provisioning, and RAPID Mode.

 

The day's of asking ACT! to make it faster is no longer needed.

JM

Silver Super Contributor
Posts: 2,328
Country: USA

Re: How to speed up an Edit Copy, Swap or Replace field data over 20,000 records, ACT 2013, v15.1

This is a great tip.  I think I know why it happens.  When you remove an item from the list view you essentially change the lookup from a query based lookup to a lookup based on the unique ids of the records.  Since the unique ids of the records don't change when you do a copy/replace/edit, etc. the database doesn't need to rerun the query.  Most people who are doing an edit/replace would typically be editing the field that they are querying which would affect the query.  So Act! refreshing the query periodically during the edit/replace would cause a performance hit.  This is just speculation on my part but it would seem to fit the circumstances.  Thanks for the post.  I will definitely use this technique in the future and it has made it into my Tips & Tricks presentation for the Act! Cruise coming up next week!

 

Stan

 


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!