Query performance

[Theju112]

Dear Scott and Team,

In one of our apps, We are running quite a number of huge data intensive SQL statements using pjs.query. Since there is a significant performance lag, we are working on converting the SQL statements to views to improve the performance. The issue is with a single screen that has around 11 grids each running its own SQL to fetch its data.

Please let us know what you think about the "view conversion" approach. Is there is something that we can do with the pjs.query to use the same SQL but improve the performance?

[Scott Klement]

You did not say which database you're using. Currently, we support IBM DB2 for i, Microsoft SQL Server, Oracle, MySQL, and MariaDB.

I would suggest trying your statement in an interactive tool for the database you're using. Does it perform the same as it does in pjs.query()? If the performance is the same (or very close), then pjs.query() is working as expected. In that case, you should analyze the statement and improve its performance using standard SQL performance techniques, such as a query analyzer to determine what is happening, creating indexes to improve performance, etc. SQL performance is a complex topic, and experts have written extensively about the subject.

On the other hand, if the statement runs well in an interactive tool but not so well in pjs.query(), then it could potentially be a problem in Profound.js. In that case, I would open a support ticket with Profound Logic.