Slow search on 9.1

MikeLockwood · ‎Mar 24, 2010

We upgraded to 9.1 this past weekend. Relatively smooth but a few major issues. Right now the system is screaming fast for everything except some searches and Renames. Tech support call filed, huge amount of requested info provided.

Right now, a search for a single drawing, using NUMBER field only, no wildcards, takes 10-40 seconds.an Advanced Search that includes that drawing with multiple criteria and wildcards takes less than one second. All in Oracle checks out. Oracle returns all requests instantly. All indexes are up to date. Statistics are up to date.

A search to Oracle of for the NUMBER (only) for all WTDocuments created in the past 7 days comes back in 0.1 seconds. The same in Windchill takes 50 seconds. Reducing the search results to only the NUMBER field makes it a little faster but not much. I'm still betting a cup of coffee that somehow our old friend (???) FAST ESP has something to do with this, even though we think we have that connection fully turned off.

Another note -Our Tomcat log is absolutely full of this type of error: 2010-03-24 11:47:43,229 ERROR [TP-Processor9] com.ptc.core.components.tags.components.DescribeTableTag - Couldn't find ti for type:

Ideas?

MikeLockwood · ‎Mar 24, 2010

Please see attached.

MorganBelais · ‎Mar 24, 2010

Hello,

I already opened a call for this issue.

SELECT ... FROM EPMDocument A0,EPMDocumentMaster A0B WHERE
(A0.idA3masterReference=A0B.idA2A2) AND ((UPPER(A0B.name) =
'P000002160_CO_000.DRW')) AND (A0.markForDeleteA2 = 0);
SELECT ... FROM EPMDocument A0,EPMDocumentMaster A0B WHERE
(A0.idA3masterReference=A0B.idA2A2) AND ((UPPER(A0B.documentnumber) =
'P000002160_CO_000.DRW')) AND (A0.markForDeleteA2 = 0);
SELECT ... FROM EPMDocument A0,EPMDocumentMaster A0B WHERE
(A0.idA3masterReference=A0B.idA2A2) AND ((UPPER(A0B.cadname) =
'P000002160_CO_000.DRW')) AND (A0.markForDeleteA2 = 0);

This is the issue as the number is allways stored in uppercase, so the
query should not be using upper statement.
If you look at the documentnumber field, there is no index for UPPER...

Hope it helps,
Morgan

Morgan Belais
IS Manufacturing

Richemont International SA - Chemin de la Papeterie, 1
CH-1290 VERSOIX GENEVA
Tél: +41 (0) 22 775 39 73
Fax: +41 (0) 22 775 38 01
Natel : +41 (0) 79 388 81 57
E-mail: morgan.belais@richemont.com

"Lockwood,Mike,IRVINE,R&D" <mike.lockwood@alconlabs.com>
24/03/2010 21:21
Please respond to
"Lockwood,Mike,IRVINE,R&D" <mike.lockwood@alconlabs.com>

To
"-" <->
cc

Subject
[solutions] - RE: Slow search on 9.1

Please see attached.

avillanueva · ‎Mar 25, 2010

Could this be the difference? When you use the basic search, it is
searching (typically) all object types. So it goes through change
objects, WTParts, WTDocs, EPMDocs, etc, looking for NUMBER. Then it
merges the results. When you use advanced search, it only looks at one
object type. Compare the basic search when you limit the object to one
type. Does it match the advanced search times?

achukwuka · ‎Mar 25, 2010

Good point Antonio. The all object types is a more expansive search as the system will go through all object types looking for the occurrence of the search string. Limiting the object type to the specific object you want to search for will improve the performance. Also you might need to investigate if there is an index that will improve performance for you.

Alexius C. Chukwuka
IT Analyst, PDP Systems
John Deere Power Systems
Product Engineering Center
*Voice: 319-292-8575
*Mobile: 319-429-5336
*FaxFax:319-292-6282
*E-Mail: -

CONFIDENTIALITY. This electronic mail and any files transmitted with it may contain information proprietary to Deere & Company, or one of its subsidiaries or affiliates, and are intended solely for the use of the individual or entity to whom they are addressed, shall be maintained in confidence and not disclosed to third parties without the written consent of the sender. If you are not the intended recipient or the person responsible for delivering the electronic mail to the intended recipient, be advised that you have received this electronic mail in error and that any use, dissemination, forwarding, printing, or copying of this electronic mail is strictly prohibited. If you have received this electronic mail in error, please immediately notify the sender by return mail.

patrick.chin1 · ‎Mar 25, 2010

Hey Mike,

There is a few indexes you have to check specifically for 9.1. I upgraded Psion to 9.1 way back in January 2009.

Solution Details - TPI 147026

Check if these index already exist:

create index SHAREDCONTAINERMAP$ptc1 on SHAREDCONTAINERMAP(idA3A5,markForDeleteA2);
create index SHAREDCONTAINERMAP$ptc2 on SHAREDCONTAINERMAP(branchIdA3B5,markForDeleteA2);
create index SHAREDCONTAINERMAP$ptc3 on SHAREDCONTAINERMAP(idA3H5,markForDeleteA2);

Also, the tables for both setup participants and in folders view cause a performance hit.I created a call with PTC C6862995, C6942305,and <u>SPR1726181:<u> </u></u>
Customer reported that "Find in List" tool does not work as intended. The customer would like it to search through multiple pages in a paged list.
The more items you show per page, the worst the performance gets which is why the "Find in Table" should search the entire results of the table not just displayed. Pretty stupid intended functionality according to PTC spec. Currently according to spec, it only searches what is displayed.
Here are some additional indexes that may help. You can use the oracle web based consoleoptimizer to check for additional suggestions:
create index FOLDERMEMBERLINK$ptc1 on FOLDERMEMBERLINK(IDA3A5,IDA3B5) compute statistics;
create index ITERFOLDERMEMBERLINK$ptc1 on ITERFOLDERMEMBERLINK(IDA3A5,BRANCHIDA3B5,latestA3B5) compute statistics;
create index WfProcess$ptc1 on WfProcess(idA2A2,classnameA2A2) compute statistics;
create index WFASSIGNEDACTIVITY$NAME1 on WFASSIGNEDACTIVITY(NAME) tablespace INDX compute statistics
create index WORKITEM$PTC2 on WORKITEM(STATUS) tablespace INDX compute statistics
create index WORKITEM$PTC1 on WORKITEM(IDA3A4) tablespace INDX compute statistics
Remember before applying any index to production, test if it exist already to the columns and test on the test server to see if there is any performance gain.

MikeLockwood · ‎Mar 25, 2010

We proved yesterday that a search for a single WTPart:
- With All applicable object types: 25 seconds
- With just Part: 1 second (no diffierence adding wildcards)
- With just Part, Document: 1 second (no diffierence adding wildcards)

- With just Part, Document, EPM Document: 1 second (no diffierence adding wildcards)

For now we've instructed users to manually select the objects they need to search for (big pain always to require users to do this type of thing after an upgrade that "improves" things).

Today, I'm going to one by one add each object back to what is being searched and see which one or more make the huge difference. I suspect that it won't be any - that instead there is something wrong with the list used via preferences that defines "All Applicable Object Types." I'm also curious what happens on a new install vs. an upgraded system and will attempt to test.

We're really hesitant to fool with the database indexes unless well-thought out and proven. Since all of this is searching just on the NUMBER field, which is by definition the primary key of each object, we can't yet understand how indexes will matter - but there may be more to learn on this.

kknake · ‎Mar 25, 2010

Mike, Antonio, Alexius and all CAD Admins

I watch your threads on this issue of searching and want to share a thought
on a solution ( forgive the form letter look )

Alexius I would like to hear your comments as I understand that ya'll
have deployed this solution in some parts of your company

Thanks

Kelly

Searching for 3D CAD data within a PLM system

One of the main issues with managing engineering CAD data is searching to
try to find what you are looking for. Until recently the only way to find a
CAD file was to do a metadata search. Searching with metadata is constrained
by the accuracy and consistency of the metadata and the search engine. Even
with the strictest of naming conventions more often than not drawings get
lost. When this happens your data set has duplication and duplication is
expensive.

Another issue with metadata searching is the search tool itself. Many times
they fail because of poor design and implementation. When your metadata
search engine fails your only recourse is a manual search and we all know
what happens with manual searching. After 10mins, redesign.

There is a solution, 3D search. With 3D search, a part or assembly is found
by its geometry and features as well as associated metadata. Take for
example a U Bolt. Was it named U Bolt, U_Bolt, ubolt or some encrypted name?
With 3D search you can do an initial metadata search with say ubolt. The
results will display all parts with ubolt in the name. To expand the search
to find all u bolts pick one of the search results from the initial metadata
search to do a 3D search. The result will display all similar geometry in
your entire dataset.

CADseek is a very high end search engine. Use CADseek as the front end to
your PLM system for searching. It is very intuitive and can be attached to
any number of data sets, PLM systems or CAD formats.

It is also a great tool to use during a migration project. If you are
migrating many data sets into one CADseek can help clean the data before the
migration. Similar if your company is merging with another CADseek can be
used to compare your existing data with the merged company's data before
integrating the data sets.

For more information click on www.3dcadseek.com

MikeLockwood · ‎Mar 25, 2010

All is good again in the world...

Turns out we had the preference "Indexable Global Searches" set to Yes. Makes all the difference in the world. See attached spreadsheet.

achukwuka · ‎Mar 25, 2010

Mike,

So you do not have FAST ESP configure yet on your system?

Thanks

Alexius C. Chukwuka
IT Analyst, PDP Systems
John Deere Power Systems
Product Engineering Center
*Voice: 319-292-8575
*Mobile: 319-429-5336
*FaxFax:319-292-6282
*E-Mail: -

CONFIDENTIALITY. This electronic mail and any files transmitted with it may contain information proprietary to Deere & Company, or one of its subsidiaries or affiliates, and are intended solely for the use of the individual or entity to whom they are addressed, shall be maintained in confidence and not disclosed to third parties without the written consent of the sender. If you are not the intended recipient or the person responsible for delivering the electronic mail to the intended recipient, be advised that you have received this electronic mail in error and that any use, dissemination, forwarding, printing, or copying of this electronic mail is strictly prohibited. If you have received this electronic mail in error, please immediately notify the sender by return mail.