Solved

Export to Excel from View CDT

Forum|Forum|4 years ago
December 15, 2021
10 replies
0 views

Hi,

I have CDT which is created from a View. The view takes around 8 sec. I have process model which uses Export DSE to Excel smart service to export the content of view to an excel. The process model executes fine and generates the excel when run in debug mode (Start process for debugging).

When the process triggers on a timer ( timer event) it fails to execute Export DSE to Excel node and raises error " Error accessing data store entity: <<xxx_V_xxxxx>>. Please contact a system administrator for additional details. (APNX-1-4505-026))

I have checked the system error logs, following logs are logged.

Error accessing data store entity: <<xxx_V_xxxx>>. Please contact a system administrator for additional details.

An error occurred while retrieving the data. Details: Unexpected error executing query (type: [xxxxxx], query: [queryentity expression], order by: [[Sort[id asc]]]

Caused by: org.hibernate.QueryTimeoutException: could not execute query
Caused by: java.sql.SQLTimeoutException: (conn=484012) Query execution was interrupted (max_statement_time exceeded).

It seems, the query is timeing out. I am unable to find why this is running fine while debuging but fails while triggered by a timer. It returns same data on both case. The timer trigger was during out off office time, this should run quicker.

Is there any other way to implement this so it will not be impacted by the performance of the view. I am thinking to copy the view to a table and then export the data from the table CDT to excel.

Regards,

Surjit

Best answer by csteward

It is strange to me that the same export runs fine in debug mode but times out when started via timer.. You may want to consider tuning or applying indexes to your view to resolve timeout issues however. In some situations for more complex SQL, we will have SQL Jobs which update a table from a view, then the table is utilized for export. Depends on your use case, how stale the data can be at export time / how often to refresh, etc.

cstewardAnswer

It is strange to me that the same export runs fine in debug mode but times out when started via timer.. You may want to consider tuning or applying indexes to your view to resolve timeout issues however. In some situations for more complex SQL, we will have SQL Jobs which update a table from a view, then the table is utilized for export. Depends on your use case, how stale the data can be at export time / how often to refresh, etc.

S

surjitg0001Author

Thanks Chris, Not sure why it was working in debug mode.

Alternatively, I have replaced the view with a staging table which I populated using a stored procedure. I had used somesql l function to make it parametrised view (which was making the view to sloww), but in stored procedure, I handled this more efficiently and stored procedure was much faster to return the dataset and insert to the table.

F

felic0001

Glad I found out about this article, it's very well structured. slope game

C

canliy0001

I'm searching for a response for a similar inquiry. My group is intending to utilize Appian. Has your group have an answer for this issue now? canlı tv

mikes0011

Brainy

The original question here represents an unusual case that I've never run into despite implementing similar things many times. IMHO when based on a well-structured view it usually won't be an issue. It certainly shouldn't be a blocker towards considering adoption of an Appian system.

The Expression Guru