Presented by: Michael Brockmann Maik Hölter Olaf Reimann

1. Module: Collaborative Application ArchitecturesProject GCC09:Performance analysis in hybrid data structures Presented by: Michael Brockmann Maik Hölter Olaf Reimann

2. Agenda • Introduction • Project problems • Business scenario “Maintenance” • Business scenario “Working hours assignment” • Results • Business scenario “Employee management” • Results • Conclusion 2/16

3. Project team • Michael Brockmann, Student of Business Computing, 7th Semester • Maik Hölter, Student of Business Computing, 7th Semester • Olaf Reimann, Student of Business Computing, 7th Semester Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 3/16

4. Project goal • Performance tests in developed business scenarios based on known weak spots of NSF, considered ones are: • Index building • Time/date-views • Readers-fields • Data of two or more databases aggregated in one view • Possible workaround with NSFDB2 Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 4/16

5. NSFDB2 in IBM Lotus Notes/Domino 7 • With release 7 Domino uses DB2 as an alternate data store for Notes data • Specific sets of data are pushed from NSF to DB2 in form of DB2 Access Views • A Notes view can be built based on a SQL query in form of QueryViews • Notes data is stored as a BLOB in DB2 Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 5/16

6. Domino Server.Load • Domino Server.Load is included as part of the Administrator Client • You can run a script (a simulated workload) in your own environment to obtain server capacity and response metrics • It is possible to run built-in scripts such as custom scripts • To simulate workloads for the defined business scenarios, custom scripts were used for each scenario Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 6/16

7. Project problems • Domino Server.Load was not able to address QueryViews until: • IBM Lotus Domino/Notes Release 7.0.1, available since February 2006and • A Notes.ini command line for HTTP access and debugging of QueryViews was added • A short Help-Documentation of Server.Load leads to several small problems • Due to a small number of input-variables not all preferable test runs and consequential statements could be made • A lot of software debugging had to be done before the performance tests could start Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 7/16

8. Scenario “Maintenance” • A companies IT-department has several service tasks to do • Each task is stored as Notes data and is due at a specific date • Problem: • In NSF realized with date-views containing @TODAY or @NOW functions in selection formulas • Such views with unstable functions are weak spots for building view indices in NSF • Solution: • Data is stored in DB2 • A SQL statement returns only data matching the date-restrictions Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 8/16

9. Scenario “Working hours assignment” • Employees have to assign their working hours and activities in a NSF for each working-day • To prevent unauthorized access each document is protected by readers-fields • Problem: • View’s indices are globally held without any user-information, so displayed documents have to be calculated on every view-call depending on the current user • With a huge amount of data this technique becomes a weak spot of index-building in NSF • Solution: • Data is stored in DB2 • A SQL statement returns only data matching the user’s name Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 9/16

10. Results – Faster than NSF? Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 10/16

11. Interesting points • SELECTS without WHERE are faster than SELECTS with WHERE • But most SQL statements work with WHERE clauses • NSFDB2 becomes faster with a higher workload and therewith more valuable • Highest performance boost by limiting the result sets • Indexing needed to get nearly as fast as NSF Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 11/16

12. Scenario “Employee management” • Problem: • Employee data is managed in a NSF so far • Everybody may see the name and phone number of all employees, but only HR may see the salary • Difficult to realize, a challenge for security management • Solution: • Salary data is stored in a different database • With SQL-JOIN or –UNION data from both databases is aggregated in one view Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 12/16

13. Results • Very “performance-hungry” • Tests executed with half of the users and half of the documents • UNION is much faster than a JOIN • LEFT/RIGHT OUTER JOINS behave similar • But comparing JOINS makes less sense • UNION and UNION ALL have almost identical performance Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 13/16

14. Conclusion • NSFDB2 solutions are better than NSF solutions in some cases • That is too weak to make a clear decision for NSFDB2 when implemented to circumvent NSF weak spots • But aggregated data from different NSF databases in one view makes the security management much easier • Now shifted from document-level to view-level Introduction Project problems Maintenance Working hrs. assignment Results Employee management Results Conclusion 14/16

15. Thank you and goodbye Thank you for your interest… … and goodbye! 15/16

16. Questions ? 16/16