Does sql query from CSP that causes web pages to timeout get logged into SQL Statements ?

Question

Question

Phuc Nguyen · Oct 27, 2020

Hello,

I have a csp that occasionally will show Timeout error.

I am looking at the SQL Statements tab of the SQL Explorer:

The Average time is around 1.4, what would cause the CSP page to timeout if the query average time is 1.4 while the CSPGateway Server Response Timeout and Queued Request Timeout are default to 60 seconds? Does sql query from CSP that causes web pages to timeout get logged into SQL Statements ?

Thanks

Discussion (9)1

Log in or sign up to continue

Phuc Nguyen · Oct 29, 2020

We found the query that is causing CSP to timeout: "group by xyz" , the sorting was taking too long even with resultset just a handful.
adding the ID helps avoid timeout "ID like '%%%' group by xyz"

0 0

score 0 · Answer 1 · 2020-10-28T07:31:33-04:00

Phuc,

Assuming the SQL is being run synchronously from within the CSP page, then the page will not be able to send it's response to the client while it waits for the SQL to complete. Are you saying that the average response time of the query is 1.4 minutes? If so then I think you need to see if you can speed up your query, call for the data asynchronously, or change your CSPGateway configuration so it has a longer timeout.

score 0 · Answer 2 · 2020-10-28T12:11:46-04:00

Phuc Nguyen · Oct 28, 2020

Average Time column from Intersystems SQL Statements tab is in seconds

0 0

score 0 · Answer 3 · 2020-10-28T14:21:46-04:00

In that case I can't see how a 1.4 sec query could trigger any sort of timeout from a CSPGateway perspective. I think you have something else going on - maybe an uncaught error in your code?

score 1 · Answer 4 · 2020-10-28T11:30:32-04:00

I presume 1.4 in your case means 1.4 seconds to run the query that's causing the hyperevent error? Not always but sometimes I find hyperevent errors in synchronous processes having some sort of infinite loop somewhere in the code.

In the past to determine which part of csp is causing an issue I've used ^%SYS.MONLBL which is mainly used to log the amount of time each line in a routine (mac/int) takes to execute but since csp gets compiled to .cls and .cls gets compiled to .int you can add the .int part of the csp to monitor the csp.

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

score 0 · Answer 5 · 2020-10-28T13:03:19-04:00

Thanks... giving this a try but not sure why MONLBL is always returning '(routine not found)' when entering any routine name. Using wildcard * is accepted but after executing csp, detail report written is empty. Will try other report and see...

Looks like csp.INT is not detected for monitoring:

score 0 · Answer 6 · 2020-10-29T04:56:06-04:00

Robert Cemper · Oct 29, 2020

What do you mean by csp.INT ??

0 0

score 0 · Answer 7 · 2020-10-29T13:05:22-04:00

Phuc Nguyen · Oct 29, 2020

The compiled class for the csp file.

0 0

score 0 · Answer 8 · 2020-10-29T13:09:34-04:00

Robert Cemper · Oct 29, 2020

Ah! csp.pagename.CLS

0 0