How to improve performance of any application that uses legacy Database like HP NonStop Tandem that can only run SQL and not stored procedures?

Question

One of my web application is getting data from a Legacy System called Tandem(HP NonStop), which can only run SQLs and not Stored Procedures. Also that system is not in my control and all I can do is read data from that using a SQL queryies.

Application's front-end(Javascript) is using REST Web Service to talk to back-end to retrieve data.

I wanted to know how other developers are handling this kind of scenario and what measures or tools are used to improve the performance when retrieving data from slow/legacy database.

Do you actually have a performance problem, or are you imagining one? Legacy doesn't necessarily mean slow, and stored procedures are not necessarily needed for effective database performance. — Kayaman, Aug 07 '17 at 17:56
Yes I have a performance problem when joining Multiple Tables. Though, the fields I am joining are part of the Table's Index they tend to produce results at a slow pace. When asked about this the Database Admin responded back saying they have added few extra CPUs to improve results. But, we don't see much of a difference when running SELECT query. So, I am interested in learning how others are handling such a scenario without tweaking the actual database because that is not in UI developers control. Any help? — Thulasi, Aug 07 '17 at 18:08
Yeah, talk to the DBA. If you have slow queries, he can tell you why they're slow. No use asking us. — Kayaman, Aug 07 '17 at 18:19

score 1 · Answer 1 · edited Dec 10 '19 at 21:50

You can use VQP (Visual Query Planner) or regular explain plan from SQL/MX to indicate where the query is experiencing high "cost".

From there, you can create a "query shape" plan, and place this control statement into your code prior to performing the SQL that executes the join.

control query shape nested_join
(nested_join
  (split_top_pa
     (scan
        (TABLE 'X', path '\\<system>.$<volume>.<subvol>.<index on table to join>'
        )
     ),split_top_pa
     (scan
        (TABLE 'X', path '\\<system>.$<volume>.<subvol>.<table to join to maintbl>'
        )
     ),INDEXJOIN
  ),split_top_pa
  (scan
     (TABLE 'H', path '\\<system>.$<volume>.<subvol>.maintbl'
     )
  )
);

score 0 · Answer 2 · edited Oct 30 '17 at 00:12

It looks like your application is sending a REST message to the NonStop application with a SQL query.

This may force the NonStop Server process to submit the query for compilation. Please check if some sort of query caching can either be turned on or implemented by the server process.

NonStop SQL does have decent join performance, have the DBA look at the plan.

NonStop SQL supports stored procedures written in Java.

How to improve performance of any application that uses legacy Database like HP NonStop Tandem that can only run SQL and not stored procedures?

2 Answers2