Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Criterion with OperatorId.IN_SET

    Hello,

    At this same example:
    http://forums.smartclient.com/showthread.php?t=25340

    I'm using a different criteria with a OperatorId.IN_SET.

    Apparently works fine, but looking at the logs I saw a huge SQL using a lot of "OR" instead of the IN clause.
    Well... this is weird, I'm concerned about some performance issues, how can I manage to use an IN clause?

    Also, why is it always generating a complementary "NOT... IS NULL" statement? This also concerns me about performance.

    Thank you!

    Here is the code:

    Code:
    AdvancedCriteria criteria = new AdvancedCriteria( OperatorId.AND, new Criterion[] {
    				new Criterion("clientVehicleOwnerId", OperatorId.EQUALS, data.getClient().getId()),
    				new Criterion("vehicleId", OperatorId.EQUALS, firstVehicleId)
    				,new Criterion("permission", OperatorId.IN_SET, data.getPermissions()) //permission array
    		});
    aaand here is the log:

    Code:
    "SELECT COUNT(HISTORY_ID) FROM HISTORY_ENTRY WHERE (((((CLIE_SQ_CLIENT = ?) AND NOT ((CLIE_SQ_CLIENT IS NULL))) AND (VEHI_SQ_VEHICLE = ?)) AND NOT ((VEHI_SQ_VEHICLE IS NULL))) AND (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((PERM_SQ_PERMISSION_HANDLE = ?) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) OR (PERM_SQ_PERMISSION_HANDLE = ?)) AND NOT ((PERM_SQ_PERMISSION_HANDLE IS NULL))))")
    Last edited by zatix; 6 Feb 2013, 05:11.

    #2
    This isn't a performance concern since the DB's query optimizer generally simplifies this into the same query plan.

    About the complimentary "not NULL" behavior, please see DataSource.strictSQLFiltering.

    Comment


      #3
      Originally posted by Isomorphic View Post
      This isn't a performance concern since the DB's query optimizer generally simplifies this into the same query plan.

      About the complimentary "not NULL" behavior, please see DataSource.strictSQLFiltering.
      Ok, but I still don't understand about the DataSource.strict SQL Filtering.
      Accordingly to the documentation:
      http://www.smartclient.com/smartgwt/javadoc/com/smartgwt/client/data/DataSource.html#getStrictSQLFiltering()

      "NOTE: On the server side, this property is only applicable if you are using the SQL DataSource; the other built-in types (Hibernate and JPA/JPA2) do not offer this mode."

      I'm using JPA2 DataSource, how can I manage to set this property? This is not clear enough yet. Could you give-me a small piece of example code? I would like it to not behave this way.

      Comment


        #4
        Take a second look at the docs -

        1. the automatic AND NULL / AND NOT NULLs are added to prevent the extremely counter-intuitive results of normal SQL queries

        2. strictSQLFiltering:true would turn this off, but we do not offer this mode for JPA/Hibernate

        Comment

        Working...
        X