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