Speed ENOVIA SmarTeam Searches
Depending on how your users like to search in SmarTeam, evaluating the statistics and manipulating the indices on your SmarTeam databases may improve performance. Examples of fields that might benefit from additional indices could be Part/Drawing Number, Description, Material, or other frequently searched fields. Naturally, this will vary depending on what attributes are commonly used to find objects and files. For instance, it’s possible to reduce the time required for a search by a factor of more than 100 when an appropriate index is created on a database that has grown unacceptably slow due to its size.
Use database tools to help you determine which indices might be helpful. You can use SQL Profiler (in SQL Server) to capture queries that are running against your database so that they can be analyzed using the Index Tuning Wizard (SQL 2000) or the Database Tuning Advisor (SQL 2005). These tools can suggest new indexes that may improve performance, but perform a reality check on their recommendations to see if they make sense. If the additional indices don’t help, you can always remove them later.
Most database engines (SQL Server and Oracle included) keep statistics on the queries that are being run against databases to optimize how the queries are executed. These statistics need to be updated periodically to make sure things are performing properly. With SQL Server, running the sp_updatestats stored procedure can do this. For individual tables this can be done by adding a table name such as sp_updatestats dbo.TN_DOCUMENTATION’).
ENOVIA SmarTeam’s DBRepairing and Data Model Designer tools will rebuild indices and collect statistics to help optimize database performance. Just keep in mind that these tools may undo any indexing changes you’ve made using the methods described above, and replace them with SmarTeam’s standard set of indices so you may need to put your custom indices back after making data model changes. To address this, you can create one or more maintenance plans or scheduled jobs to automate index rebuilding and statistics updating. These scheduled tasks should be inserted with your existing backup and disaster recovery automations.
Tags: database engines, ENOVIA SmarTeam, Index Tuning Wizard, indice, Oracle, PDM, search, speed, SQL
Read more posts by bethb
