Topic
5 replies Latest Post - ‏2013-10-03T00:09:08Z by DGawron
DunnoJack
DunnoJack
32 Posts
ACCEPTED ANSWER

Pinned topic SQL Call vs SQL Statement builder for large queries

‏2013-09-26T16:54:42Z |

While developing an application (WEF 7) I was connecting to a  development database that only held a few thousand records per table. Now that the application is complete, and moved into a more 'live' environment, the database that it connects to has upwards of 20k records per table. I'm experiencing some noticeable slowdowns compared to what I had seen while developing.

So, I was looking for ways to reduce this sluggishness and increase the performance. I've added the various loading indicator builders to the page, to alert the user that something is loading and not just hung up, but also wanted to look into the SQL calls to make sure they are only calling and returning small 'subsets' of results, instead of all 20k per fetch. 

I've mainly been using the SQL Call builder, with the 'Paged XML DataRetriever' set for 'Transform Result' along with a split paging builder being used in the consumer. Does this setting actually modify the query that is performed on the database to include some sort of 'limit' or does it just manipulate the results that are returned from the database? (Turning on SQL logging does not show any modification to the query, but it might be modifying after the log was generated)

I also noticed that there are some "Performance Hints" options for the SQL Statement Builder. Would using this separate builder for the actual statement improve the performance? And how are these settings effecting the actual query that is performed on the database?

Any advice/hints/tips would be appreciated!

  • DGawron
    DGawron
    579 Posts
    ACCEPTED ANSWER

    Re: SQL Call vs SQL Statement builder for large queries

    ‏2013-10-01T16:20:41Z  in response to DunnoJack

    SQL Call will not modify the SQL statement when paging is enabled.  What happens at run-time is that a scrollable cursor is returned from executing the SQL query and the cursor is used to pull only the requested page of data.

    If you are seeing an difference in performance between the two environments then it may be related to the model actually pulling back all the rows during the first access even though you have enabled paging in SQL Call.  This can happen when you have a provider model (or a consumer) that transforms or otherwise tries to access a paged result as a plain old IXml object.  When a paged result is accessed via the IXml interface it has no choice but to pull in all the rows and turn itself into a complete XML result.  In more recent WEF releases an exception will be thrown by default when a model attempts to force a paged result to become a complete XML result.  However, this check can be disabled and maybe that is what you have done.  In the Advanced group of SQL Call make sure "Allow XML Conversion" is unchecked.  If this input is checked, then it's very likely the model is pulling back all the rows from the query.

    One other thought.  I noticed that you have an indirect reference as part of the SQL statement.  Be 100% sure that you are properly escaping the content of the string returned by that indirect reference.  If any of the data used to create the string comes from potentially tainted sources (user inputs, etc.) and you fail to properly validate/escape the data, then your model will have a potential SQL injection vulnerability.

    • DunnoJack
      DunnoJack
      32 Posts
      ACCEPTED ANSWER

      Re: SQL Call vs SQL Statement builder for large queries

      ‏2013-10-02T15:34:21Z  in response to DGawron

      Thanks for the behind the scenes info on how the SQL Call works!

      I've doubled checked  all of the consumer and provider transformations and can't see where it would be trying to return all of the results at once. My next test is going to be creating a new consumer model that will list the results using a bigger builders and then create one using smaller builders. I can then do some comparison testing and double check any settings that I might be missing with how it is currently configured. 

      Also, thank you for the advice on the potential SQL injection risk. I will double check that proper escaping is being done on the text field. Most of the values are coming from select list - integer values, so the user would have to hack the Post data to inject. This is an internal app, so we are not as concerned with that threat. Better to be safe now than sorry later though. 

      • mburati
        mburati
        2548 Posts
        ACCEPTED ANSWER

        Re: SQL Call vs SQL Statement builder for large queries

        ‏2013-10-02T18:24:12Z  in response to DunnoJack

        It's pretty trivial to generate alternate post data from what's in a select list these days, especially with browsers including built in debuggers for users to see exactly what's going on in the page (and debuggers to possibly help them alter it), so a select list won't even slow anyone down if they intend to send you something other than what you had intended.

        Your particular app and db may or may not be sensitive, but I felt I had to respond in case others are reading this thread.   It is dangerous to assume that because an application is internal that you don't need to worry about security as much.   If all of the data and all of the systems are already accessible to all of the people that have access to that network, then maybe, but if any piece of the infrastructure or information that the application has access to could be sensitive or could cause damage if altered, then an internal app may often need to be as secure as an external app.    There are  studies showing that while the sheer number of attacks may come from outsiders (mainly because the number of outsiders is often bigger) that the most damaging (and often hardest to detect) attacks often come from insiders with more knowledge of the applications (along with more authenticated and authorized access to such applications) and more knowledge of what would cause more damage to the internal systems or company, if they decide to turn on the company that previously trusted them with those internal apps.   You can use your favorite search engine to search for ""percent of attacks from internal""  to see a number of such studies.

         

        I hope that info helps,
        ..Mike Burati 
        The postings on this site are my own and do not necessarily represent the positions, strategies, or opinions of IBM.
        • DunnoJack
          DunnoJack
          32 Posts
          ACCEPTED ANSWER

          Re: SQL Call vs SQL Statement builder for large queries

          ‏2013-10-02T19:00:41Z  in response to mburati

          Thank you for the additional information.

          What would you recommend to fix the flaw in our design pattern (and potential SQL injection risk) for building dynamic search queries?

          Assume the following Search fields: (string)firstName, (string)lastName, (int)ynField, (int)field
          (All fields by default are blank, and do not need a value.)
           
          And typical Query:
          SELECT a.*, b.someField
          FROM someTable AS a
          INNER JOIN anotherTable AS b ON a.keyField = b.keyField
          WHERE a.firstName LIKE ?
          AND a.lastName LIKE ?
          AND a.ynField = ?
          AND b.field = ?
           
          Currently, our process is using an indirect reference to build the 'Where' clause. So, it can be an empty string that is returned, or any combination of values filled in. ie- maybe only search for last name and ynField
           
          How can we re-factor this using the builders to not have the indirect reference and potential SQL injection?
          • DGawron
            DGawron
            579 Posts
            ACCEPTED ANSWER

            Re: SQL Call vs SQL Statement builder for large queries

            ‏2013-10-03T00:09:08Z  in response to DunnoJack

            In recent WEF releases there is a builder named SQL Data Services that safely implements a dynamic WHERE clause (it uses positional parameters) as part of a generated search operation.  This builder essentially implements CRUD + search for a single table.  This may not work for you as-is since your sample SQL is joining two tables, but it may be possible to create a view that performs that join and then use SQL Data Services against the view.

            Consider this example.  The search SQL generated by the builder for the sample EMPLOYEE table will look like this:

            select EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM from SAMP.EMPLOYEE where (1 = ? or EMPNO LIKE ?) AND (1 = ? or EDLEVEL = ?) 

            The search operation was configured to search on any combination of the EMPNO and EDLEVEL columns.  At run-time the builder will take care of setting the correct values for any omitted search criteria.

            If SQL Data Services won't work for you, then you'll either need to use the current approach or fall-back to a hand coded JDBC LJO.  If you stick with the indirect ref approach, then ruthlessly validate and encode the user-supplied values.  The best policy is to never trust inputs, not even inputs from trusted internal users.