V8 dead slow with external linking

[Mike Hughes]

Hi Jan,

Yes, there are some user defined properties. Each product has a property for 'RRP' and 'saving'. There are also a few products that have a file property for things like additional images.

Mike

[Nadeem]

Hi Mike,

Sorry to take a bit of time with this, In Development we have been looking at this, and the following seems to have made it load a bit faster. Both myself and another Developer have checked it.

I have done the following and seems it is working slightly better

moved the stock sheet to a separate file and removed all unnecessary
macros etc

imported the product table from the original DB named Product1, then
copied and pasted all the existing products into this new table but when
access prompt me to save the design of the table said no, renamed the old
product table to productold and the product1 to product

Please could you try that, so basically what you will have is a file which has no MACRO at all, just plain text.

Kind Regards

[Mike Hughes]

Hi Nadeem,

The worksheet file, just text with no macro, is exactly the middle test I've been talking about since the beginning. Yes, it is significantly faster than when the worksheet is embedded in a larger workbook but it's still far too slow to be usable (currently taking about 10 minutes to open and correspondingly slow on all actions within actinic such as changing pages, creating new sections, etc, etc).

The second element of your post appears to be a way of using the product tables to remove any unallocated products from the linked worksheet. The current setup has about 2,750 products linked in, 750 of which are allocated and 2,000 of which are unallocated. I'm not surprised that cutting the number of linked products from 2,750 to 750 makes actinic quicker but that isn't a solution.

I was thinking that the problem could be Chris' suggestion that more than 200 or so products in a section slows things down so I've just spent this morning allocating all the unallocated products into sections of 20 products each.(and deleted 20 or so product references in the worksheet that have a " or . in them)

The net result. No improvement. Linking to a single worksheet file with just text, no macros, no DB links etc with 2,750 products still takes 10 minutes to open. This is with all the linked products allocated into sections with typically 20 or so products per section. I've also tried with the preview window open and closed without any noticeable difference.

So we don't seem to getting anywhere other than that V8 is seriously slow at handling products linked to an external excel file. Even in this best case scenario it's still more than fifty times slower than V6 is at it's worst.

There has to be a bug somewhere or some seriously sloppy code. Either way it needs to be fixed before I can use V8 for a live site. I really want to use it, but right now it's unusable when linking to an external excel file.

Mike

[Nadeem]

Hi Mike,

Just to say this is still being worked on and we are trying to pinpoint the issue. Just feeding what you have mentioned back into Development

Kind Regards

[Mike Hughes]

Thanks Nadeem,

There seems to be another aspect to feed back into the mix. If I create a new section, the window asking for the name appears immediately, I can enter a name and then as soon as I hit enter, actinic locks up for 1 minute 20 seconds. If I select another section, then go back to new one and hit 'delete' actinic again locks up for 1 minute 20 seconds before coming back and displaying 'this section has been deleted' in the preview. Then purging deleted content is immediate. As you can tell this is with previewing enabled.

If I switch off the preview window, things are different. I can create the section instantly and delete it instantly, but then doing a purge/content takes the 1 minute 20 seconds before actinic comes back to life again.

This to me suggests that there's a problem with the database access as I assume actinic accesses the database during the creation and deletion of a section when preview is enabled, but only does so during the purge process when previewing is disabled. If it's not the database access then there should be a good clue here as to what it is that's really slowing things up.

I wanted to make sure there was nothing else slowing up the database access, (even though my V6 has none of this problem) and so have repeated the test with both AVG and Microsoft defender switched off. This made no difference.

Hopefully this gives you another avenue to consider when trying to identify the problem. It should also help everyone to understand why I don't consider V8 to be usable at the moment.

Mike

[zgap111]

Actinic v8 slow on external links

We've just upgraded from v6 to v8 and all has been running fine.

We've decided to add a Geotrust Smart Seal to our site and put the code into the cart table in the side bar.

Code inserted is:
<div align="center">
<!-- GeoTrust QuickSSL [tm] Smart Icon tag. Do not edit. -->
<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript" SRC="//smarticon.geotrust.com/si.js"></SCRIPT>
<!-- end GeoTrust Smart Icon tag -->
</div>

Once that was inserted, the "Design" & "Content" pages behave very slow.
Everytime you change to a different product, the preview page pauses a long time before finally refreshing.

I've tried just closing that panel, but it still happens (probably in background).

Seeing this post, I'm sure that my external links is causing v8 to be very slow.

Please advise solution (if any).

[cdicken]

<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript" SRC="//smarticon.geotrust.com/si.js"></SCRIPT>

Change this to

<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript" SRC="http://smarticon.geotrust.com/si.js"></SCRIPT>

That should help.

[zgap111]

Added the: http://

And the problem persists.

To test if it really is this code:
I've cut (removed) the code to see if it speeds up, and it does! I can click on each section with no pauses, virtually instant page refreshes.

When I add the code (with the http:// correction), it all grinds to a halt.

The code I've added isn't just a normal link but a *.js file, does that make a difference to how Actinic behaves? Since we have a "links page" with normal html links which doesn't cause this problem.

[Mike Hughes]

For a moment there I thought you'd found the problem I have as I also have the geotrust icon on my website.

Sadly though, doing anything in my V8 is still impossible due to it's slowness and that's when using the content in a clean V8 design (i.e. with no geotrust icon).

The two may be related, even though my linking problem is with an excel spreadsheet rather than an image). I have a feeling that Actinic introduced a bug into V7 that causes everything to slow down under certain conditions and that it's still around in V8. There have been quite a few reports of slowness in V7 that haven't been resolved and I'd be surprised if they weren't all related to the same thing.

PS. Your geotrust icon really needs to be on https: otherwise visitors will get a warning when they try and use one of your secure pages.

Mike

[cdicken]

OK - try this.

Highlight your code in v8 and then click the 'Insert Block' button (green 'C' with a '+').

Copy and paste the following code into the Condition Editor and click 'OK'

<actinic:variable name="IsPreviewMode" /> == false

This should put green 'block if' tags around your tracking code - and then job of these is to hide the code in preview mode, but then display the code when you upload your store.

[zgap111]

Adding the block works! It's back up to full useability speed!!

I was hoping for something that would temporarily disable part of code during previewing! And you have it.

Thanks for the quick solution!

(Hope Mike's issue will be resolved soon.)

[Mike Hughes]

OK. Here's something for actinic to think about.

I've been tracing the SQL statements from actinic V6 and V8 with some quite shocking results (and thsi is a very slow process with V8 as you'll understand in a minute):

Open V6 - Linked to external database: 19,600 SQL statements (enter & exit)

Open V8 - Linked to external database: 1,368,709 SQL Statements (enter & Exit)

So V8 is making something like 70x the number of SQL statements.

Looking at the log files I can see that there are a large number (over 4,000) of SQL errors of the form:

Catalog 7b0-b50 ENTER SQLSetStmtOption
HSTMT 01542678
UWORD 6 <SQL_CURSOR_TYPE>
SQLPOINTER 0x00000003

Catalog 7b0-b50 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 01542678
UWORD 6 <SQL_CURSOR_TYPE>
SQLPOINTER 0x00000003 (BADMEM)

Catalog 7b0-b50 ENTER SQLSetStmtOption
HSTMT 01542678
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000004

Catalog 7b0-b50 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 01542678
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000004 (BADMEM)

Catalog 7b0-b50 ENTER SQLPrepare
HSTMT 01542678
UCHAR * 0x016D9898 [ -3] "SELECT `Product`.nParentSectionID, ParentSection.nSectionID FROM `Product` LEFT JOIN `Catalog section` AS ParentSection ON `Product`.nParentSectionID = ParentSection.nSectionID WHERE (((`Product`.nParentSectionID <> 0) OR ((`Product`.nParentSectionID) Is NULL)) AND ((ParentSection.`nSectionID`) Is Null));\ 0"
SDWORD -3

Catalog 7b0-b50 EXIT SQLPrepare with return code -1 (SQL_ERROR)
HSTMT 01542678
UCHAR * 0x016D9898 [ -3] "SELECT `Product`.nParentSectionID, ParentSection.nSectionID FROM `Product` LEFT JOIN `Catalog section` AS ParentSection ON `Product`.nParentSectionID = ParentSection.nSectionID WHERE (((`Product`.nParentSectionID <> 0) OR ((`Product`.nParentSectionID) Is NULL)) AND ((ParentSection.`nSectionID`) Is Null));\ 0"
SDWORD -3

DIAG [S1C00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106)

Catalog 7b0-b50 ENTER SQLErrorW
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4
WCHAR * 0x0012EC9C
SWORD 511
SWORD * 0x0012F0FC

Catalog 7b0-b50 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4 (106)
WCHAR * 0x0012EC9C [ 74] "[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented "
SWORD 511
SWORD * 0x0012F0FC (74)

Catalog 7b0-b50 ENTER SQLErrorW
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4
WCHAR * 0x0012EC9C
SWORD 511
SWORD * 0x0012F0FC

Catalog 7b0-b50 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4
WCHAR * 0x0012EC9C
SWORD 511
SWORD * 0x0012F0FC

Catalog 7b0-b50 ENTER SQLSetStmtOption
HSTMT 01542678
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000002

Catalog 7b0-b50 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 01542678
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000002 (BADMEM)

Catalog 7b0-b50 ENTER SQLPrepare
HSTMT 01542678
UCHAR * 0x016D9898 [ -3] "SELECT `Product`.nParentSectionID, ParentSection.nSectionID FROM `Product` LEFT JOIN `Catalog section` AS ParentSection ON `Product`.nParentSectionID = ParentSection.nSectionID WHERE (((`Product`.nParentSectionID <> 0) OR ((`Product`.nParentSectionID) Is NULL)) AND ((ParentSection.`nSectionID`) Is Null));\ 0"
SDWORD -3

Catalog 7b0-b50 EXIT SQLPrepare with return code -1 (SQL_ERROR)
HSTMT 01542678
UCHAR * 0x016D9898 [ -3] "SELECT `Product`.nParentSectionID, ParentSection.nSectionID FROM `Product` LEFT JOIN `Catalog section` AS ParentSection ON `Product`.nParentSectionID = ParentSection.nSectionID WHERE (((`Product`.nParentSectionID <> 0) OR ((`Product`.nParentSectionID) Is NULL)) AND ((ParentSection.`nSectionID`) Is Null));\ 0"
SDWORD -3

DIAG [S1C00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106)

Catalog 7b0-b50 ENTER SQLErrorW
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4
WCHAR * 0x0012EC9C
SWORD 511
SWORD * 0x0012F0FC

Catalog 7b0-b50 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4 (106)
WCHAR * 0x0012EC9C [ 74] "[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented "
SWORD 511
SWORD * 0x0012F0FC (74)

Catalog 7b0-b50 ENTER SQLErrorW
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4
WCHAR * 0x0012EC9C
SWORD 511
SWORD * 0x0012F0FC

Catalog 7b0-b50 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 01541540
HDBC 015415E8
HSTMT 01542678
WCHAR * 0x0012F09C (NYI)
SDWORD * 0x0012F0F4
WCHAR * 0x0012EC9C
SWORD 511
SWORD * 0x0012F0FC

Catalog 7b0-b50 ENTER SQLSetStmtOption
HSTMT 01542678
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000001

Catalog 7b0-b50 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 01542678
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000001 (BADMEM)

Catalog 7b0-b50 ENTER SQLPrepare
HSTMT 01542678
UCHAR * 0x016D9898 [ -3] "SELECT `Product`.nParentSectionID, ParentSection.nSectionID FROM `Product` LEFT JOIN `Catalog section` AS ParentSection ON `Product`.nParentSectionID = ParentSection.nSectionID WHERE (((`Product`.nParentSectionID <> 0) OR ((`Product`.nParentSectionID) Is NULL)) AND ((ParentSection.`nSectionID`) Is Null));\ 0"
SDWORD -3

Catalog 7b0-b50 EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
HSTMT 01542678
UCHAR * 0x016D9898 [ -3] "SELECT `Product`.nParentSectionID, ParentSection.nSectionID FROM `Product` LEFT JOIN `Catalog section` AS ParentSection ON `Product`.nParentSectionID = ParentSection.nSectionID WHERE (((`Product`.nParentSectionID <> 0) OR ((`Product`.nParentSectionID) Is NULL)) AND ((ParentSection.`nSectionID`) Is Null));\ 0"
SDWORD -3

Catalog 7b0-b50 ENTER SQLExecute
HSTMT 01542678

Catalog 7b0-b50 EXIT SQLExecute with return code 0 (SQL_SUCCESS)
HSTMT 01542678

This whole piece of code should be 4 SQL statements but because of the error turns into 24 statements.

OK. So what's causing it?

Well after doing a bit of digging it turns out that the SQL statement SQLSetStmtOption is not supported in ODBC 3.0 but is an ODBC 2.0 command. When using an ODBC 3.0 driver it gets mapped to the new SQLSetStmtAttr command but at this stage my ability to understand the description dies a death.

So I suspect this could be what's causing the problem. It turns out in my log that SQLSetStmtOption is called 31,222 times with V8 which should be often enough to put a spanner in the works. It's also called 594 times in my V6 logs with similar associated errors (about 80 SQL errors).

Now, just to confirm some of my system settings. I have MDAC 2.8 (not 2.6 as the knowledge base insists I should use) because with WinXp these are a core part of windows and cannot be downgraded.

I also appear to have the ODBC 4.00.6304.00 desktop database drivers. I see from Microsoft that the 3.5 version drivers were backwards compatible with odbc 2.0 so maybe that's also why different systems respond differently.

Anyway, maybe actinic could look into this and see if it's part of the problem. I could well be way off track, but I'm sure there's something going on here and I think it's about time it got sorted.

Mike

[Mike Hughes]

More info:

Looking around the sql logs it's clear that there are several odbc 2.0 calls being used. yes, these do get mapped across to the new odbc 3.0 calls but I get the feeling that the mapping isn't always reliable and there's no real reason why actinic should be using odbc 2.0 anyway as odbc 3.0 drivers from microsoft have been available since the late 1990's.

having said all that, and while I do believe actinic should be using odbc 3.0 calls (from my less than knowledgable viewpoint), I still believe there's something else going on here. I can't see that old drivers are going to make V8 create 70x as many sql calls as V6 does in the same situation.

Mike

[Jan]

The main difference between external linking in V7 and V8 is that several of the fields that were linked from the product or section table in V7 are now UPDs in V8, this makes it a lot more complicated.

Regards,

[Mike Hughes]

Hi Jan,

I'm glad you're still watching this thread. I'm still blundering through this minefield.

I don't even know what UPDs are, but in my linking case all I'm linking is the product ID (20 chararcters) and a 1/0 for stock check enabled. I wouldn't have thought that would make things more complicated.

Anyway, there sems to be four things for actinic to look at:

1. Why is V8 hammering the database to death with 70 times more SQL calls than V6 did. (and this is with preview off, so a like-for-like comparison)

2. Why does V8 take so long to open when linking is enabled? The linking information is kept in the database whether linking is enabled or not, so what in the world is V8 doing all the time? How long can it take to make sure you have the relevant data?

3. Trying to print a catalog report (such as 'catalog summary') when linked always seems to give me an error in V8. Here it is:

There was a problem with the database

Operation: Executing the query 'UPDATE TemporaryProductTable INNER JOIN [ProductLinked] ON TemporaryProductTable.[Product reference] = [ProductLinked].[Product ID] SET [TemporaryProductTable].[bStockEnabled] = [ProductLinked].[Control?],[TemporaryProductTable].[Product reference] = [ProductLinked].[Product ID] WHERE [TemporaryProductTable].bUseExternalData = TRUE '

Database name: C:\Program Files\Actinic v8\Sites\Site1\ActinicCatalog

COUNT field incorrect

Action: Contact your Actinic reseller or Actinic Support as appropriate

.

4. Why does actinic still use odbc 2.0 sql calls when these appear to be a cause of sql errors? Yes, the software recovers from the errors, but it would be better if they weren't there in the first place.

Mike