Slow Performing StringValue Query during "Add to Workspace" Operation in Windchill PDMLink

• Oracle
  • Adding objects to workspace from CREO is slow
• SQL Server
  • Unable to add objects to workspace from CREO
  • "Add to Workspace" operation hangs
  • Tempdb in MS SQL Server increases tremendously filling the partition up to its maximum size
  • MS SQL Server hangs due to full Tempdb
• Poorly performing SQL:

SELECT 'wt.iba.value.StringValue',A0.classnamekeyA5,A0.idA3A5,A0.hierarchyIDA6,A0.classnamekeyA6,A0.idA3A6,A0.classnamekeyA4,A0.idA3A4,CONVERT(varchar,A0.createStampA2,120),A0.markForDeleteA2,CONVERT(varchar,A0.modifyStampA2,120),A0.idA2A2,A0.updateCountA2,CONVERT(varchar,A0.updateStampA2,120),A0.value,A0.value2,A0.idA3A6 r
FROM StringValue A0
  WHERE ((((A0.idA3A6 IN (?,?,?,?,?)) 
    AND (A0.idA3A4 IN (SELECT item FROM #wtarray2899313225)))))

SELECT'wt.iba.value.StringValue',A0.classnamekeyA5,A0.idA3A5,A0.hierarchyIDA6,A0.classnamekeyA6,A0.idA3A6,A0.classnamekeyA4,A0.idA3A4,CONVERT(varchar,A0.createStampA2,120),A0.markForDeleteA2,CONVERT(varchar,A0.modifyStampA2,120),A0.idA2A2,A0.updateCountA2,CONVERT(varchar,A0.updateStampA2,120),A0.value,A0.value2,A0.idA3A6 r
FROM StringValue A0 
  WHERE ((((A0.idA3A6 IN(@P0,@P1,@P2,@P3,@P4)) 
    AND (A0.idA3A4 IN (SELECT item FROM #wtarray_NUMBER_0_200)))))

SELECT 'wt.iba.value.StringValue',A0.classnamekeyA5,A0.idA3A5,A0.hierarchyIDA6,A0.classnamekeyA6,A0.idA3A6,A0.classnamekeyA4,A0.idA3A4,CONVERT(varchar,A0.createStampA2,120),A0.markForDeleteA2,CONVERT(varchar,A0.modifyStampA2,120),A0.idA2A2,A0.updateCountA2,CONVERT(varchar,A0.updateStampA2,120),A0.value,A0.value2,A0.idA3A6 r 
FROM StringValue A0 
  WHERE ((((A0.idA3A6 IN (@P0,@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9)) 
    AND (A0.idA3A4 IN (SELECT item FROM #wtarray_NUMBER_0_1000)))))

• Stack trace shows the query comes from wt.epm.download.cache.familytable.AttributeValueCacheHandler.init() method:

"ajp-bio-8010-exec-19" Id=2127 RUNNABLE (in native)
      Blocked (cnt): 1862; Waited (cnt): 464
  at java.net.SocketInputStream.socketRead0(Native Method)
  at java.net.SocketInputStream.read(SocketInputStream.java:152)
  at java.net.SocketInputStream.read(SocketInputStream.java:122)
  at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1782)
  at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4838)
  at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150)
  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402)
  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
  at wt.pds.AbstractResultCursor.executeQuery(AbstractResultCursor.java:559)
  at wt.pds.AbstractResultCursor.executeQuery(AbstractResultCursor.java:523)
  at wt.pds.BasicResultCursor.getNextResultSet(BasicResultCursor.java:282)
  at wt.pds.BasicResultCursor.advance(BasicResultCursor.java:234)
  at wt.pds.BasicResultCursor.next(BasicResultCursor.java:82)
  at wt.pds.SQLDatabasePds.query(SQLDatabasePds.java:853)
  at wt.pom.PersistentObjectManager.query(PersistentObjectManager.java:1111)
  at wt.fc.StandardPersistenceManager._query(StandardPersistenceManager.java:1983)
  at wt.fc.StandardPersistenceManager._find(StandardPersistenceManager.java:2007)
  at wt.fc.StandardPersistenceManager.find(StandardPersistenceManager.java:678)
  at sun.reflect.GeneratedMethodAccessor226.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:606)
  at wt.services.ServiceFactory$ServerInvocationHandler.invoke(ServiceFactory.java:399)
  at com.sun.proxy.$Proxy7.find(Unknown Source)
  at wt.epm.query.pom.impl.POMProcessor.execute(POMProcessor.java:1694)
  at wt.epm.query.pom.impl.AbstractEvaluator.execute(AbstractEvaluator.java:41)
  at wt.epm.query.pom.impl.StatementSpecEvaluator.execute(StatementSpecEvaluator.java:41)
  at wt.epm.query.pom.impl.POMProcessor.executeInteranal(POMProcessor.java:274)
  at wt.epm.query.pom.impl.POMProcessor.execute(POMProcessor.java:328)
  at wt.epm.query.AbstractQuery.execute(AbstractQuery.java:258)
  at wt.epm.query.Query.execute(Query.java:65)
  at wt.epm.query.Query.execute(Query.java:145)
  at wt.epm.download.cache.familytable.AttributeValueCacheHandler.init(AttributeValueCacheHandler.java:150)
  at wt.epm.download.cache.familytable.CellValueCacheHandler.getCellValues(CellValueCacheHandler.java:37)
  at wt.epm.download.cache.familytable.CellCacheHandler.getCellValues(CellCacheHandler.java:117)
  at wt.epm.download.NDFamilyTableMember.getCellValues(NDFamilyTableMember.java:101)
  at sun.reflect.GeneratedMethodAccessor904.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:606)
  at wt.epm.util.proxy.accessor.AccessorRecord.invokeWithOutTrace(AccessorRecord.java:44)
  at wt.epm.util.proxy.accessor.InterceptorAccessorRecord.invoke(InterceptorAccessorRecord.java:38)
  at wt.epm.util.proxy.ProxyRecord$2.invoke(ProxyRecord.java:447)
  at com.sun.proxy.$Proxy273.getCellValues(Unknown Source)
  at com.ptc.windchill.uwgm.proesrv.util.X12NeutralDataHelper.createEpmInstanceNeutralData(X12NeutralDataHelper.java:252)
  at com.ptc.windchill.uwgm.proesrv.rrc.RRCFamilyTableHelper.processNeutralData(RRCFamilyTableHelper.java:999)
  at com.ptc.windchill.uwgm.proesrv.rrc.RRCFamilyTableHelper.setFTInfo(RRCFamilyTableHelper.java:321)
  at com.ptc.windchill.uwgm.proesrv.rrc.RRCFamilyTableHelper.setFamilyTableInfo(RRCFamilyTableHelper.java:236)
  at com.ptc.windchill.uwgm.proesrv.rrc.RequestResultCache.processFamilyTables(RequestResultCache.java:1628)
  at com.ptc.windchill.uwgm.proesrv.rrc.RequestResultCache.postProcess(RequestResultCache.java:671)
  at com.ptc.windchill.uwgm.proesrv.ProeServiceRequestDispatcher.postScanRequests(ProeServiceRequestDispatcher.java:61)
  at com.ptc.windchill.uwgm.proesrv.StandardRequestDispatcher.scanRequests(StandardRequestDispatcher.java:1035)
  at com.ptc.windchill.uwgm.proesrv.UwgmTransactionExecuterImpl.execute(UwgmTransactionExecuterImpl.java:94)
  at com.ptc.windchill.uwgm.proesrv.UwgmTransactionExecuterImpl.executeTransaction(UwgmTransactionExecuterImpl.java:56)
  at com.ptc.windchill.uwgm.proesrv.ProeService.executeTransaction(ProeService.java:111)
  at com.ptc.windchill.uwgm.proesrv.ProeService.execute(ProeService.java:87)
  at com.ptc.windchill.uwgm.soap.impl.uwgm.BinaryRequestHandler.processRequest(BinaryRequestHandler.java:670)
  at com.ptc.windchill.uwgm.soap.impl.uwgm.BinaryRequestHandler.getCacheDescriptor(BinaryRequestHandler.java:519)