Announcement

**Duncan Rounding** · 20-Mar-2009, 07:39 PM

I think an update query (although not as user friendly) would perhaps work better than linking.

**Jan** · 21-Mar-2009, 07:08 AM

It might also be quicker if you move the table into access as well.

Regards,

**Mike Hughes** · 21-Mar-2009, 01:35 PM

It might also be quicker if you move the table into access as well.

Thanks Jan,

I've created a routine to export the excel data to an access database (thank heavens for the internet and people who share their stuff). For more on how to do this visit this site: http://www.excelguru.ca/taxonomy/term/2

The good news is that this reduces the external linking time down to 9 seconds on my 'performance' PC. Which is quite brilliant.

The odd news is that external linking to the csv file is now also taking 9 seconds on my 'performance' PC. Odd because it definitely took an hour and a half the first time I did this yesterday.

Odd again is that externally linking to the Access database on my 'workhorse' pentium D PC is still a no-go. I gave up after 30 minutes because when doing this actinic just uses the CPU at 100% for a few minutes and then activity drops off to nothing.

Conclusion?

I don't think I have enough to reach any kind of sensible conclusion on this.

My best guess as to what's happening here is that the first time you link externally Actinic goes and creates the database fields to hold the external data and it is this process that is taking all the time and CPU power. When you disable external linking I suspect that Actinic doesn't clear this table so it's already there the next time you turn external linking on again.

I'll look into this by checking the database and also by trying a database compact to see if that removes the external linking table and re-introduces the problem.

There are two worrying points this raises though:

1. It shouldn't take very long to create the new table so it could be that whatever 'housekeeping' actinic is doing during my other performance problem is also being triggered regularly while this is happening.

2. Why does actinic go to sleep when doing the same thing on a Pentium D procesor? CPU activity for actinic goes down to zero with the only sign that something is still happening being an occasional change in memory usage and a rare uptick in CPU usage to 2%.

Mike

PS. I'm kind of stuck with my 'workhorse' Pentium D machine as this is the only one I have now with XP on it. As my live site is still V6 I can't move to a Vista machine until I can get V9 sorted.

**Jan** · 21-Mar-2009, 02:35 PM

It could be related to different ODBC driver versions (these are used to perform the linking) as these may be different on the two machines.

There is no first time setup stage (other than when you actually setup the links) so there is no logical reason for the linking to take a long time the first time and then be quick subsequently.

What fields are you linking?

Regards,

**Mike Hughes** · 21-Mar-2009, 03:41 PM

There is no first time setup stage (other than when you actually setup the links) so there is no logical reason for the linking to take a long time the first time and then be quick subsequently.

I was sure that Actinic imported the data into the database when externally linking, but I think you're right. I've looked all over the database in V6, V8 and V9 and there's no table for the external data.

I'm only linking 2 fields. 'Product Reference' and 'Stock Checks Enabled'.

One thing that is different between the 'external linking' and 'unlinked' state is that there are about 2,000 unallocated products in the table. What Actinic does when it finds these on linking in I have no idea. They just appear at the bottom of the list as 'Unallocated External Products'. These have no description or anything like that so I wonder if Actinic is trying to do some checking / housekeeping when it comes across these. Why this would only happen the first time is still a mystery as I can't see anywhere that Actinic could be holding the state of previous linkings.

If I use the same fields on an import then Actinic does throw up line errors about wanting a short description.

I'll check the odbc driver versions on the two PCs.

Mike

**Jan** · 21-Mar-2009, 08:41 PM

Hi Mike,

Have you got auto-generate product references switch on? If you haven't and are still getting that short description error, use a hierarchical import instead, you don't have to use the complex file that is exported, you can have a single section entry with all of the products within it, with just the same fields as you have, the products will be updated in their existing sections not moved.

Regards,

**Duncan Rounding** · 21-Mar-2009, 10:03 PM

Originally posted by Jan View Post

...the products will be updated in their existing sections not moved...

Good tip Jan - I never knew that, I always spent ages getting all the section structure correct before importing product updates.

**Mike Hughes** · 24-Mar-2009, 12:56 PM

Auto generate product references is off. I haven't tried the hierarchical import yet as I'd like to use external linking if I can. The line errors on the standard import aren't a problem though, I just thought they might be relevant in working out what the problem is.

I have now managed to link V9.0.3 to the external file on my main PC. It's messy and I only came across it by accident while trying other things, but it does seem to work.

Here's what you have to do:

1. Create a small subset of the external file (I used 10 products) and link to that. This should be relatively quick, about 10 seconds for me. Once the products are linked in, close Actinic.

2. Open Actinic, change external linking to point to the full external file. Click OK. Actinic will start importing, the external linking window will stay open.

3. Open Task Manager. catalog.exe will run at 99% cpu usage for a minute or so and then drop off to 0%. Actinic is still frozen and all you can do is move the external linking window about. It doesn't respond in any way. Select catalog.exe in task manager and end the process.

4. Now open Actinic again and there you are. The external products are all linked in and from what I can see everything is working normally. Actinic can be opened and closed without any re-occurence of the total lock-up.

The first stage appears to be crucial here as it means that after you kill the actinic process, actinic will open and assume the linking was successful. Even though this was only the case with a different file, it remembers the full external file as the one to link to.

As I said. Not pretty and reliant on some odd behaviour but it does appear to get around the total lock-up problem.

With external linking in operation, creation of new sections (with page preview enabled) or products in a new section (with page preview disabled) causes about double the 'freezing' delay that you get without external linking. For me that's about 35 seconds instead of 15 seconds. This might well be related to the fact that I have about twice as many products when externally linked rather than to the external linking process itself.

So, one down, one to go. If we can just solve the freezing when making these content changes I might just be able to use V9.

Mike

Edit: I just tested with only 10 products linked in (or about 1,000 products in total), and the content change delay goes to 22 seconds. Not as good as the 15 seconds with no linking, but better than the 35 seconds with 3,000 odd products linked. So it sounds like a combination of external linking and the number of products.

Announcement

Slow external linking in V9

Slow external linking in V9

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment