CA Clarity Tuesday Tip:  Writing Efficient NSQL Queries

Discussion created by Shawn_Moore Employee on Apr 13, 2011
Latest reply on Apr 14, 2011 by Michael
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 4/12/2011

This has been a busy week for me, so I'm going to cover a question that came in from Lynn, since I can cover it fairly readily.

The Question:

"I think you had this somewhere, but I can't find it. What are some good ways to ensure our custom SQL query based portlets are optimized? Particularly, when you have security in place that a user can only see data to which they have access, Clarity can be very slow in displaying the data, as it is filtering through security for one reason. However, if these NSQL portlets are custom built, how can we ensure the queries are written in an optimal way?"

The Answer:

This can be tricky, because the security is added dynamically, however here is a set of steps that should lead to more efficient queries.

1) Write your query outside of Clarity using regular SQL 1st. (This of course won't have any added security, but you can easily weed out problems with the base structure of your query.)

a) One you write your query and are satisfied with the resulting data, time your query with a few variables changed. This will give a rough measurement with some different parameters. If you notice that the query is slow, you'll definitely want to optimize it.

b) (more advanced) Examine the number of reads your query generates using an Oracle AWR or SQL Server Performance report. Millions of reads or more are excessive and can impact system performance overall. Sometimes queries are fast, but they consume high DB resources. This is especially true on very powerful servers, which can render results with much speed.

2) If you need to tune your query, you can run an execution plan to examine what the database engine is doing. This will help you find missing joins (i.e. if you list a table in your from clause, make sure you are joining to it) or situtations where indexes could be used.

In MS SQL Server, you can type <ctrl>+L or right click the mouse and choose "Display Estimated Execution Plan".  

In Oracle, you can prepend the text, "explain plan for " to your query and then run it in SQL Plus, Toad, Oracle SQL Developer, etc.

Look at the indexes for the tables your query accesses and make sure your where clause statements are accessing indexed fields. Also, look for full table scans or areas that the plan shows a high utilization and then see if you can match that what your query is doing. The execution plan will provide some clues, but they are not easy to read (I'm still trying to master them. ;) )

3) One you have created your NSQL for your custom query, you can also run a SQL Trace on your portlet to see how the security elements translate into the final query. Then you can take the query from SQL Trace output, plug in the parameters and run the actual query in your query tool. Again you can then perform some additional analysis using the database reports or execution plans.
(I may write a followup article on this technique that we use in support on a daily basis.)

4) Utilize your DBA when in doubt. Using the SQL statement based on the query from #1 or #3, run the query by your dba to see if there are constructs you are using which may not be efficient.

I could definitely expand on this topic further, but this hopefully provides some general ideas and areas to look into.