Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

Building the Case for SQL Tuning Training

By Mark Robbins posted Mon August 06, 2018 02:58 PM

  

Over the last few articles I have talked about SQL optimisation and how support teams can make a difference if they know how to optimise the SQL.

I gave an overview of the SQL Tuning areas that I think support teams should know about. In my last article I talked about the business impact of poor queries.

Support teams are often under resourced and it can be difficult to persuade people to invest in SQL optimisation training.

Talking to managers about technical terms, like explain plans, indexes and SQL Warning log messages, may not convince them to invest in the training.

Consider taking a different approach – think about the impact of SQL related problems.

At a next coffee break do a little research/thinking:

  • How many “poor performance” meetings have you/your team been in and how much did that cost?
  • How much additional value could have been delivered if the support staff were not investigating performance problems/meetings?
  • How many users have suffered delays because of the poor performance?
  • How much did the delays cost ?
  • How much more efficient could the users be if the system was quicker?
  • How many slow SQL warnings are being logged?

You may be surprised by the cost of existing performance problems.




#IBMChampion
#AssetandFacilitiesManagement
#Maximo
#IBMChampion
2 comments
42 views

Permalink

https://community.ibm.com/community/user/blogs/mark-robbins/2018/08/06/building-the-case-for-sql-tuning-training

Comments

Mark Robbins
Wed August 22, 2018 04:20 PM

If you talk to larger customers then it is apparent that there is a need to archive data.

I'm involved in designing/building solutions for customers with large databases. IBM and others offer various products.

"WORKORDER can aggregate different tables together"
The Maximo architecture means that data needs to be stored in columns on just that one table. It often frustrates DBAs that the workorder table is so large but that also allows the workorder details to be loaded into memory in a single select statement.

Other selects are required to retrieve the other details e.g. job plans/assets

I don't expect the table structure to significantly change in the coming releases.

If enough people raise RFEs/highlight performance problems to IBM then there may be a greater focus on measures to improve the performance e.g. adding support for data partitioning/function based indexes (FBI).

https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=44977 - Adding Function Based Indexes to Maximo

SoCal Sun
Wed August 08, 2018 11:05 PM

Is IBM working towards giving us better database performance so the the effort is on both ends? WORKORDER table is really a beast and it gives us the worst performance. I wish there were fewer rows, data shifted to other tables. WORKORDER can  aggregate different tables together. There seem to a lot of indexes, some I think, could be merged. Queries are optimized, but there's just a lot of rows and performance just gets worse over time.