Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
3 replies Latest Post - ‏2012-11-14T13:29:37Z by SystemAdmin
SystemAdmin
SystemAdmin
1061 Posts
ACCEPTED ANSWER

Pinned topic Does Adding Clustered Indexes to SQL Server Impact SA?

‏2012-10-22T19:46:56Z |
Hello, everyone - the DBA supporting System Architect (11.4.1) for me has suggested we set up "clustered indexes" on the encyclopedia tables to improve performance. I haven't been able to find any information about this, pro or con, in the online help for SA. Does anyone here have any suggestions or insight about whether this will have any impact on SA, specifically if it will somehow break the UI? I'm guessing everything will be fine... but I don't want to find out I'm wrong the hard way!

Thanks,
TJ, the non-DBA
Updated on 2012-11-14T13:29:37Z at 2012-11-14T13:29:37Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1061 Posts
    ACCEPTED ANSWER

    Re: Does Adding Clustered Indexes to SQL Server Impact SA?

    ‏2012-10-23T15:36:13Z  in response to SystemAdmin
    Good question - being a non-DBA myself, I would probably create two encyclopedias where one has a cluster index and the other doesnt. Populate each encyclopedia with the same data of about 1000 test objects. Then try exploring them, drag/drop/pasting large numbers of them in workspaces, and editing several of them at a time to see differences in UI lag time. We'd also need to know if it messes up during the encyclopedia verify and repair process.
  • JJanze
    JJanze
    10 Posts
    ACCEPTED ANSWER

    Re: Does Adding Clustered Indexes to SQL Server Impact SA?

    ‏2012-10-25T08:00:18Z  in response to SystemAdmin
    I am a Oracle DBA and from what I know it should be no problem applying indexes to table without causing trouble to the application. I did it in the past for much more complex PDM/PLM applications (like e.g. Teamcenter). You may have to figure out which tables and columns make sense to be indexed and how you configure the index (in terms of size and such that). Fortunately But you have your DBA of choice near to you :-)

    It should be no difference to say/apply the same principles when we talking about SQL Server, too.

    Independent from that I think the approach suggested by Joseph (jvogel-11) has the advantage to ensure that everything is working fine (which I except as said) and second you will be able to compare the performance increase/decrease when indexes had been applied (which helps in tuning a lot).

    It would be great if you can share the outcomes in the forum. Would be in interesting to see the impact of applying indexes to the DB of RSA.

    Best regard
    Janes
  • SystemAdmin
    SystemAdmin
    1061 Posts
    ACCEPTED ANSWER

    Re: Does Adding Clustered Indexes to SQL Server Impact SA?

    ‏2012-11-14T13:29:37Z  in response to SystemAdmin
    FYI, I did experiment in our test environment before turning the dba loose on our production encyclopedias. I opened some of the more complicated explorer diagrams; a few other diagrams, performed some simple searches, opened the matrix viewer, ran one of the DoDAF Word reports, and performed a verify and repair - both before and after he added clustered indexes. I didn't see any real difference in either how the SA interface worked, or its performance. But he's happy, and that is what matters. ;)