Long SQL-query processing times

[_lukas_]

Hi, we are experiencing very long processing times on a SQL query performed by sqlSelect(sqlParam, sqlQuery) in javascript.
Edit: The query is always returning 1 row.

The problem occurs intermittently and most of the times the query takes just 1 second to process. At worst it takes more than 1 minute. It does not seem to be a problem on the DB-side. We are using Oracle on the DB side and the nlserver is running on Linux Redhat 7.

Could this be a matter of garbage collection in the javascript engine?
Or are there any libraries that needs to be preloaded to avoid this? Perhaps some settings in the serverConf.xml?

Any help would be great, thanks in advance.

[LaurentLam · Accepted solution]

Hello, using SQLSelect from the jsAPI is too avoid in most cases:
as this is considered as SQL injection, ACC does not take it in consideration in the cnx pool for handling requests and preventing deadlocks (cf dbcnx attribute in the serverConf file).
Related to the JS engine memory it can also creates issue depending of the amount of data retrieved by the SQL request. That's why we usually put an arbitrary max of 10000 rows when using a queryDef (that is ran with the CNX pool) and paginate over it.

If you still need use the SQLSelect() function, I recommend you to select prior the data you want to process in the JS engine with a query activity and then use the vars.tablename in the FROM of your SQLselect in order to access the data.

For performance reason, you can also put a query activity with a query "1 equal to 0" and declare all the required fields in the enrichment section of the activity by putting a default value of 0 for integers and '' for string:
It will generate an empty table with all columns. You can then use a SQL activity (carefull to take a workflow from an old neolane version with the activity in it) and insert into the vars.tableName all the data you want... But carefull to use the "with nolock" in your queries in order to avoid creating locks on the table that AC would then badly handled.
After this, you can do whatever you want with your JS activity on this table as all needed fields are already present inside and that this table will only be accessed by your workflow.
Carefull: this advanced method for querying the database should be used only when perf are really important and that you can optimize the SQL request better than AC does it natively (specially with join)

[_lukas_]

Hi and thanks for the reply! Do you know if the recommendation about avoiding this is mentioned anywhere in the documentation? Should I also avoid using sqlExec to make update/inserts?

What did you mean by the cf attribute?
I can only find this:
<dbcnx NChar="" bulkCopyUtility="" dbSchema="" encrypted="" login="" maxRetries="2"
password="" provider="" server="" timezone="" unicodeData="" useTimestampTZ=""/>

My select is only returning one row so I doubt there will be an issue with memory in my case, but good to know. The javascript is triggered by a call to soaprouter.jsp so I dont have the option to use a query activity in a workflow. The only reason I am using SQL directly is that I am not very familiar with the queryDef syntax when it comes to more advanced grouping, outer joins and stuff like that.

[LaurentLam]

You're absolutely right the max db connection pool seems to be an outdated parameter from V6.02 I used to check before.

Regarding your trigger on soaprouter.jsp, you could perhaps use the NLWS.xtkWorkflow.PostEvent() function in order to call a workflow (Call another workflow in Adobe Campaign | Blog by Florian Courgey)
If your trigger is about to retieve and sort data in a jsp page, I would say to use then a workflow that will aggregate / calculate every needed data in a single schema (that would run every X times per day) and then you could build a jssp page to display the figures (if you do not want to use the report feature from ACC)

Ps: Regarding the queryDef syntax, you can use the ctrl + F4 option in query activity to display its code... But I'm afraid that with many joins, you should better use SQL injection for perf reasons if you have to retrieve data with complexe joins

[_lukas_]

Thanks for the advice! Unfortunately it is no option for us to call a workflow since the number of calls triggering the js is(/could be) very large. Do you have any link to the documentation that says that we should avoid the sqlSelect?

Yeah, the ctrl + f4 thing is a hidden gem, took a while before I discovered it

[LaurentLam]

Hello,
as every best practice, this is not documented
Just don't forget that if you need to specify "sqlinjection = true" ("false" by default) in order to permit it, it is an indicator

[_lukas_]

No, we have not allowed sqlinjections in the serverConf

Btw, I noticed that I get this error message in the webmdl-log when the delay occurs:
log WEB-530007 Error relaying '/nl/jsp/soaprouter.jsp' to URL 'http://localhost:8080/nl/jsp/soaprouter.jsp'. (iRc=-59). Could this be caused by a too small connection pool? As pointed out here

We have 50 in the maxCnx-attribute in the serverConf /serverConf/shared/dataStore/dataSource/pool@maxCnx=50

and these settings in the tomcat config:
/Server/Service/Connector@maxThreads=125
/Server/Service/Connector@acceptCount=200