Export to Excel from View CDT

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


  Discussion posts and replies are publicly visible

Parents
  • 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.

  • 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.

Reply
  • 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.

Children
No Data