Data Query taking to long to run

[rodrigo_pereira]

Hello experts,

I've a query in VA (7.1) that is taking to long to run (sometimes more than 3h ...) and after I get some errors in VA environment that I can only solve with a restart (error below).

I'm doing a simple join of a fact table with a dimension table and a where condition. The fact table has around 2GB and the Output table is always less than 2GB (around 1,6GB). 

Do you have any idea how I can trace the problem and solve it?

Do you think that with Star Schema the performance will be better? 

Do I need to eliminate the Query Builder step and do this query in the back end ETL?

Thank you very much,

Regards

Error Message after running: 

"The request could not be completed successfully due to a server error. Please contact your system administrator.

Server error: No services are available for: com.sas.svcs.jobexecution.client.JobExecutionServiceInterface. Is a required process not started?

[JuanS_OCS]

Hello @rodrigo_pereira,

as discussed on your topic from 1 month ago ( https://communities.sas.com/t5/SAS-Visual-Analytics/Errors-in-Visual-Analytics-Administrator-Data-Bu... ) I am proposing you to join both topics and move the topic to the Administration community, so you will get better audience.

I would like to mention a few things, I hope any of them might help:

- Did you check if all your SAS services are started? Could you please send us a screenshot of the services that are started and which ones stopped when this happens?

- Is your server on Windows or Linux? If Windows, ensure your service and user accounts can "Log on as Batch job".

- Perhaps you could try this. It won't harm anyway: http://support.sas.com/kb/52/315.html

- What version of VA do you have?

- How is your deployment looking like? Meaning: do you have 1 server? More than 1 for VA?

[rodrigo_pereira]

Hello Juan,

thank you for your answer.

We are using VA 7.1 with a cluster of 4 Linux servers: all the servers and installed services were UP.

We made a test and the same query that were taking more than 3 hours running in VA Query Builder, now takes just 2 min to run on DIS.

We made the decision to transfer all the queries to the back end server and use the autoload to transfer data to VA.

Anyway thank you for your answer and help.

Regards,

Rodrigo

[JuanS_OCS · Accepted solution]

Precisely, you took the best choice. Thanks for the update!

SAS VA is not designed for ETLs. Just load data into memory, and report this data, both with the highest performance available.

The Data Builder is provided for small cases... but I always recommend to not use it on daily job.

Perhaps next time you could help us and many other users in your situation, by providing your solution/workaround and making it as solution (or if anyone else does, then that one). 🙂