HP Service Manager (SM) 9.32: How to improve query or table join performance

Our exploration of HP Service Manager 9.32 continues today with a spotlight on cross-table  query. HP Service Manager 9.32 boasts several enhancements to the cross-table query feature that were designed to significantly improve system performance when handling certain ad-hoc SQL queries. 

man on computrer.jpg

 

Prior to HP Service Manager 9.32, a query running across two tables pulled the data from the database and then performed the join operation locally in memory. That operation on large volumes of data was inherently resource-intensive and seriously impacted performance. Now, with HP Service Manager 9.32, Service Manager attempts to parse and convert the query to pure SQL, which is then passed to the Relational Database Management System (RDBMS).  The RDBMS then executes the initial join operation, thereby minimizing in-memory processing, and returns the results of the query to HP Service Manager.

 

What the enhanced cross-table query feature means for you:

 

  • Improved Performance

Previously, ad-hoc SQL queries could run very slowly. This was especially true when searching a large volume of data, when searching two or more tables or if the query required grouping or sorting. The ability to run cross table queries more efficiently in HP Service Manager 9.32 makes loading data in various screens recognizably faster because so many screens rely on ad-hoc queries. In fact, due to the use of the cross table query feature, performance when loading the Inbox in HP Service Manager has improved by as much as 100 times!

 

  • Greater Productivity

The addition of the cross table join feature also helps to eliminate the need for any difficult JavaScript code optimizations when tailoring HP Service Manager 9.32. This allows administrators to focus on business logic, instead of spending valuable time optimizing code. This feature can also simplify the process of defining views, without having to perform JavaScript code optimizations, users no longer need to consider the potential performance impact of a written query. 

 

  • Reduced Maintenance Effort

With the new cross-table query mechanism, the duplication of data is also unnecessary. This effectively reduces the maintenance effort by making data modifications (for example, in master data) simpler and faster to perform.

 

Check out this video to see the new and improved cross-table query in action. We’ve demonstrated two use cases in which the power of the cross-table query feature is realized. The first scenario involves an IT Manager who wants to see
all incidents closed by people within his department in the last 30 days. The second use case describes an Incident Analyst, who wants to see interactions that have an attached incident whose status is “work in progress.” 

 

 

I also encourage you to learn more about the enhancements made to HP Service Manager 9.32 highlighted in these blog posts:

Let me know what you think in the comments below. As always, please visit hp.com/go/itsm for all things HP Service Manager and ITSM. And be sure to follow us on Twitter at @HPITSM 

 

Special thanks to Christopher Patti for his contributions to this article!

 

What have been your most challenging experiences reporting across tables in Service Manager?   We’d like to hear from you. 

Leave a Comment

We encourage you to share your comments on this post. Comments are moderated and will be reviewed
and posted as promptly as possible during regular business hours

To ensure your comment is published, be sure to follow the Community Guidelines.

Be sure to enter a unique name. You can't reuse a name that's already in use.
Be sure to enter a unique email address. You can't reuse an email address that's already in use.
Type the characters you see in the picture above.Type the words you hear.
Search
About the Author
A 25+ year veteran of HP, Yvonne is currently a Senior Product Manager of HP ITSM software including HP Service Anywhere and HP Service Man...


Follow Us
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation