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 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: