Robert Ensinger

NSQL Portlet Tuning, Optimization & Best Practices

Discussion created by Robert Ensinger on Feb 12, 2010
Latest reply on Mar 9, 2010 by RafaLarios
      There are some very good threads on SQL optimization and tuning , but I’d like to start a thread on NSQL Portlet Best Practices as it relates to portlet load times. Once one is satisfied with the performance of their SQL, when they begin to convert it to NSQL to be used in a portlet, what are the things they should consider that can affect performance?       1)     Execution Order: Are there any execution order basics or guidelines one should be aware of?                  a.     If a user applies a filter, is the SQL generated app side first then sent to the database?                  b.       Are there any actions that are app side that are more expensive (do NSQL Lookups require more NSQL compile/generation time?)       2)     Element Placement: Are there any good guidelines for when to put an element in the select and when to simply use a parameter in the WHERE clause?                  a.       Example: Logic says if it’ll never be used as a column use a WHERE clause parameter, but I've witnessed end performance that doesn't jive with this and doesn't make me ready to make this a ‘rule’ unless I’m doing something wrong.       3)     NSQL Constructs: Any guidelines or best practices where in the NSQL you put elements such as @FILTER@, etc?                  a.       Kind of to the execution order statement, I’ve seen portlets where the @FILTER@ is placed directly after parameters in the WHERE clause with SQL AND statements placed afterwards. Does this mean anything to the NSQL compiler, such as “evaluate only what’s above for FILTER replacement, everything below is carried over as isâ€??       4)     System Parameters: Guidelines, Recommendations, or any “gotchasâ€? to keep in mind when using system parameters?                  a.       I’ve seen wide variance in load time between consuming the USER_ID in the WHERE clause vs setting a default filter for the Resource in the portlet (Resource ID). What has worked faster in one portlet has not in another.     Finally, If one  thought  their SQL was efficient but  are not seeing the same out in the portlet, what is the process for getting at that compiled SQL to evaluate? Turn on SQL Trace and capture from the trace?     Thanks!  Message Edited by rensinger on 02-12-2010 10:57 AM [left] Message Edited by rensinger on 02-12-2010 10:58 AM [left]