Duplicate Queries in Word Slow to Refresh
In some Word documents containing a large number of queries and created prior to Oracle Smart View for Office release 11.1.2.5.520, performance may be an issue when refreshing the data in the document. In some cases, instead of re-using existing queries where appropriate, each Smart View data point in the Word document is treated as a separate query. The result is that Refresh operations can be very slow.
If the document contains many duplicate queries (that is, queries with the same name), the duplicate queries are displayed in the Document Contents pane in Word. For example, Document Contents might look like this:
The duplicate queries are SmartView14446361770
and SmartView14446541490
.
You can run the Visual Basic macro below to remove the duplicate queries:
Sub DeleteExtraQueries()
'
' DeleteExtraQueries Macro
'
Dim queriesName As Variant
Dim uniqueQueriesName As New Collection
Dim varName, newList As String
Set vars = ActiveDocument.Variables
For i = 1 To vars.Count
varName = vars.Item(i).Name
If Not StrComp(vars.Item(i).Name, "SV_QUERY_LIST", vbTextCompare) Then
List = vars.Item(i).Value
queriesName = Split(List, "<|>")
On Error Resume Next
For Each queryName In queriesName
uniqueQueriesName.Add queryName, queryName
Next
newList = ""
For Each queryName In uniqueQueriesName
newList = newList & queryName & "<|>"
Next
newList = Left(newList, Len(newList) - 3)
vars.Item(i).Value = newList
End If
Next
End Sub
After running the macro, the two queries, SmartView14446361770
and SmartView14446541490
, appear only once in Document Contents: