KB Article - Slow performance

[Benjamin Dyer]

Thanks Mike, this is fantastic data.

[TraceyHand]

interesting development.

This has cut down the 'thinking' (AKA 'doing-nothing') time, when creating sections, VERY significantly.

Now, if you can just find a similar 'fix' for the time it takes to check the catalogue before/during upload (because this, for me, takes over 10 minutes BEFORE the DB change I just tried) I'll be a happy bunny!

[fergusw]

Not sure if this would help anyone else testing this change, but here is a little visual basic script that lets you change this sData field in the database without needing to faff around with Access:

Simply copy this code into a blank notepad file, save it with the file extension of ".vbs" and then simply double click. It firsts prompts you to select your "site1" folder for the database you want to change, it then prompts you for the new value for the sData field.

disclaimer: Backup before using: Been tested only on V9 databases!

'This script will allow user to edit the sDate field as described here:
'http://community.actinic.com/showthread.php?t=44629
'@author    Fergus Weir - teclan
'@date    06/04/09
'@version    1.0

ON ERROR RESUME NEXT
siteFolder = pickFolder("C:\")
mydb = siteFolder + "\ActinicCatalog.mdb"

sData = 2048

nCount = 0
'create ADO objects
Set OBJdbConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObJect("ADODB.Command")
'Open db Connection

OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & mydb

'Alert user to current sData Value
set rsData = CreateObject("ADODB.Recordset")
rsData.open "SELECT sData FROM setupInfo WHERE nID = 365",OBJdbConnection,1,1
If NOT rsData.EOF then
    sCurrentData = rsData("sData")
End If
rsData.close()

sData = InputBox("Your current sData value is: " & sCurrentData &  vbcrlf & "Please enter the new value for the sData field: Default is 2048")

strSQL ="UPDATE setupInfo SET sData= " & sData  & " WHERE nID = 365"

'Execute update
objCommand.CommandText = strSQL
objCommand.ActiveConnection = OBJdbConnection
objCommand.Execute()


OBJdbConnection.Close
MsgBox ("The sData field has been reset to " & sData)

Function pickFolder(strStartDir)
    Dim SA, F
    Set SA = CreateObject("Shell.Application")
    Set F = SA.BrowseForFolder(0, "Choose the folder that contains the Actinic database you wish to work on.", 0, strStartDir)
    If (Not F Is Nothing) Then
          PickFolder = F.Items.Item.path
    End If
    Set F = Nothing
    Set SA = Nothing
End Function

[Mike Hughes]

Hi Fergus,

Thanks for this. It works well for me.

If there was one improvement it'd be nice to see, I'd like to see the existing value in the data field. This would a) give me more confidence I was changing the right value and b) Let me know if I needed to change it or not.

Minor picking though. It's a great utility.

Mike

[fergusw]

If there was one improvement it'd be nice to see, I'd like to see the existing value in the data field. This would a) give me more confidence I was changing the right value and b) Let me know if I needed to change it or not.

Mike,
I've edited the previous post to give you what you want
It now shows you the current sData field in the message box.

[TraceyHand]

I presume it would be prudent to advise that this be done with Actinic closed?
I would never attempt it with Actinic open (I'm scared to even breathe on Actinic when it's open sometimes!) and presume this is general advice when running this sort of thing on the DB?

ETA - Nice one, Fergus

[fergusw]

Tracey, TBH it depends on when Actinic polls the database for this sData value for uploading/moving between sections.
If it only pulls the sData value in when it first opens and then caches it in memory, then yes, Actinic would need to be closed and reopened for this to take effect.
Otherwise I can see no direct effect as this script only changes this one data field in the Actinic dB. It also closes the connection after itself as well, nice and cleanly, so I can't see a hit there. Perhaps Ben could enlighten us a bit further here.......

edit: always good to be over cautious though

[Mike Hughes]

Thanks Fergus, that works very nicely.

Mike

[grantglendinnin]

Not sure if this would be possible with your VB script, would it be possible for the script to check the database for the number of products, and pre-fill the sData field with the recommended 10%, but also allow the user to override the 'recommended' setting should they wish. Might be quite commercially viable if Actinic weren't already on the case

[fergusw]

Not sure if this would be possible with your VB script, would it be possible for the script to check the database for the number of products, and pre-fill the sData field with the recommended 10%, but also allow the user to override the 'recommended' setting should they wish. Might be quite commercially viable if Actinic weren't already on the case

Commercially viable? Now you have my attention!

'This script will allow user to edit the sDate field as described here:
'http://community.actinic.com/showthread.php?t=44629
'@author    Fergus Weir - teclan
'@date    06/04/09
'@version    1.2

ON ERROR RESUME NEXT
siteFolder = pickFolder(0)
mydb = siteFolder + "\ActinicCatalog.mdb"

sData = 2048
ProductPercent = 0
nCount = 0
'create ADO objects
Set OBJdbConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObJect("ADODB.Command")
'Open db Connection

OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & mydb


'Alert user to current sData Value
set rsData = CreateObject("ADODB.Recordset")
rsData.open "SELECT sData FROM setupInfo WHERE nID = 365",OBJdbConnection,1,1
If NOT rsData.EOF then
    sCurrentData = rsData("sData")
End If
rsData.close()


'Get the number of products from database
set rsProducts = CreateObject("ADODB.Recordset")
rsProducts.Open "SELECT [product reference] FROM Product",OBJdbConnection,1,1
If NOT rsProducts.EOF then
     ProdCount = rsProducts.RecordCount
End If
'tidy up
rsProducts.Close

'get percentage value for sData
ProductPercent = (ProdCount/100)*10
'Check if user wants to set value to 10% of prod num
strAutoset = MsgBox("You have " & ProdCount & " products in your database" & vbcrlf & "Do you want to auto set your sData to 10% of number of products? " & ProductPercent,4)

If (strAutoset = vbYes) then
    sData = CInt(ProductPercent)

Else
    sData = InputBox("Your current sData value is: " & sCurrentData &  vbcrlf & "Please enter the new value for the sData field: Default is 2048")
End If


strSQL ="UPDATE setupInfo SET sData= " & sData  & " WHERE nID = 365"

'Execute update
objCommand.CommandText = strSQL
objCommand.ActiveConnection = OBJdbConnection
objCommand.Execute()


OBJdbConnection.Close
MsgBox ("The sData field has been reset to " & sData)

Function pickFolder(strStartDir)
    Dim SA, F
    Set SA = CreateObject("Shell.Application")
    Set F = SA.BrowseForFolder(0, "Choose the folder that contains the Actinic database you wish to work on.", 0, strStartDir)
    If (Not F Is Nothing) Then
          PickFolder = F.Items.Item.path
    End If
    Set F = Nothing
    Set SA = Nothing
End Function

Note: Same disclaimer applies - even to you Grant

[TraceyHand]

hmmm...is the product count (as it affects the performance) just taken as the number of actual products in the database or is this performance 'fix' also affected by number of permutations/combinations too?

[fergusw]

The last version simply takes the number of products in the database. The KB states

The value of 'sData' by default is 2048
Change the value of 'sData' to be 10% of the number of products in the site i.e. if you have 2000 products (including duplicates) then set 'sData' to 200.

hence I just made it work out 10% of the total number of products, including duplicates, hidden products etc. This code is only supposed to be an easy way to amend the sData value. I'll leave it up to the individual to do the "thinking" bit about what the value should be

[TraceyHand]

Sorry, Fergus. That question wasn't for you or in relation to your fix, it was directed at Ben/Actinic.
Just wondering if it's just product count that is in play in this instance or products and combinations/permutations.

[Duncan Rounding]

Fergus, sorry to be a pain but...

The vbs won't work for me because I have 'My Documents' on a separate drive. Is it possible to change this to be able to select a drive as well?

[fergusw]

Duncan,
Just change the line

siteFolder = pickFolder("C:\")

to

siteFolder = pickFolder(0)

Robert's your mother's brother.