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!
TJ, the non-DBA
Pinned topic Does Adding Clustered Indexes to SQL Server Impact SA?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-11-14T13:29:37Z at 2012-11-14T13:29:37Z by SystemAdmin
Re: Does Adding Clustered Indexes to SQL Server Impact SA?2012-10-23T15:36:13ZThis is the accepted answer. This is the accepted answer.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 2700057EAC10 Posts
Re: Does Adding Clustered Indexes to SQL Server Impact SA?2012-10-25T08:00:18ZThis is the accepted answer. This is the accepted answer.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.
Re: Does Adding Clustered Indexes to SQL Server Impact SA?2012-11-14T13:29:37ZThis is the accepted answer. This is the accepted answer.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. ;)