Long SQL-query processing times | Community
Skip to main content
LukasPe1
LukasPe1
November 3, 2021
Solved

Long SQL-query processing times

  • November 3, 2021
  • 1 reply
  • 2850 views

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.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by LaurentLam

    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)

    1 reply

    LaurentLam
    LaurentLamAccepted solution
    November 3, 2021

    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)

      LukasPe1
      LukasPe1Author
      November 3, 2021

      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
        LaurentLam
        November 3, 2021

        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

          Terms & ConditionsAccessibility statement

          Loading footer...