Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2016
    Posts
    2

    Lightbulb Answered: Optimize SQL Query Statement

    Hello,

    I want to know which of the following queries are more efficient:
    ---------------------------------------------------------------------------------------------------------------------------
    UPDATE contracts rc1
    SET rc1.THIRD_PARTY_ERROR_TYPE = 'CLOUD_KEY_DUPLICATE', rc1.REMEDIATION_STATUS = NULL
    WHERE rc1.PARTNER_LICENSE_ID IS NOT NULL
    AND rc1.THIRD_PARTY_ERROR_TYPE IN ('DUPLICATE_RECORD')
    AND rc1.RECORD_TYPE IN ('GENERATED','REMEDIATED')
    AND rc1.PARTNER_LICENSE_ID IN (SELECT DISTINCT rc2.PARTNER_LICENSE_ID
    FROM contracts rc2
    WHERE rc2.THIRD_PARTY_ERROR_TYPE IN ('CLOUD_KEY')
    AND rc2.THIRD_PARTY_ERROR_TYPE IS NOT NULL)
    AND rc1.PARTNER_LICENSE_ID NOT IN (SELECT DISTINCT rc3.PARTNER_LICENSE_ID
    FROM contracts rc3
    WHERE rc3.RECORD_STATUS IN ('SyncSuccess', 'SyncSuccessReviewRequired','SyncSucessOnSecondRes ponse')
    AND rc3.THIRD_PARTY_ERROR_TYPE IS NOT NULL)
    AND rc1.REMEDIATION_STATUS IS NOT NULL
    AND rc1.REMEDIATION_STATUS NOT LIKE 'Need%'
    AND rc1.REMEDIATION_STATUS NOT LIKE 'EvalReady%';
    ---------------------------------------------------------------------------------------------------------------------------
    UPDATE contracts rc1
    SET rc1.THIRD_PARTY_ERROR_TYPE = 'CLOUD_KEY_DUPLICATE', rc1.REMEDIATION_STATUS = NULL
    WHERE rc1.PARTNER_LICENSE_ID IS NOT NULL
    AND rc1.THIRD_PARTY_ERROR_TYPE IN ('DUPLICATE_RECORD')
    AND rc1.RECORD_TYPE IN ('GENERATED','REMEDIATED')
    AND rc1.PARTNER_LICENSE_ID IN (SELECT DISTINCT rc2.PARTNER_LICENSE_ID
    FROM contracts rc2
    WHERE rc2.THIRD_PARTY_ERROR_TYPE IN ('CLOUD_KEY')
    AND rc2.THIRD_PARTY_ERROR_TYPE IS NOT NULL
    AND rc1.PARTNER_LICENSE_ID NOT IN (SELECT DISTINCT rc3.PARTNER_LICENSE_ID
    FROM contracts rc3
    WHERE rc3.RECORD_STATUS IN ('SyncSuccess', 'SyncSuccessReviewRequired','SyncSucessOnSecondRes ponse')
    AND rc3.THIRD_PARTY_ERROR_TYPE IS NOT NULL) )
    AND rc1.REMEDIATION_STATUS IS NOT NULL
    AND rc1.REMEDIATION_STATUS NOT LIKE 'Need%'
    AND rc1.REMEDIATION_STATUS NOT LIKE 'EvalReady%';
    ---------------------------------------------------------------------------------------------------------------------------

    Any other improvement I can make to the query?

    Thanks

  2. Best Answer
    Posted by dav1mo

    "I often times find that using an EXISTS subselect is faster than using the IN subselect construct that you have in both queries. Does, also, mean that you would be turning your non-correlated subselects into correlated. Though depending on how many rows qualify for your subselect it may still be exponentially faster."


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Gut feel says that the most efficient query would be:
    Code:
    SELECT DISTINCT rc2.PARTNER_LICENSE_ID 
       FROM contracts rc2
       WHERE rc2.THIRD_PARTY_ERROR_TYPE IN ('CLOUD_KEY')
          AND rc2.THIRD_PARTY_ERROR_TYPE IS NOT NULL
    Without schema and sample data, the gut feel is the best that I can do.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Product specific question, with a non-ANSI SQL syntax.

    Which dbms are you using?

  5. #4
    Join Date
    Nov 2003
    Posts
    2,914
    Provided Answers: 8
    Quote Originally Posted by JarlH View Post
    Product specific question, with a non-ANSI SQL syntax.
    There is nothing non-standard in the query.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #5
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by shammat View Post
    There is nothing non-standard in the query.
    The syntax (UPDATE ... FROM) is not ANSI SQL compliant, it's product specific.

    Also, in general, different products optimize in different ways, so optimizing questions are better asked in a product specific section of this forum.

  7. #6
    Join Date
    Apr 2016
    Posts
    2

    Optimize SQL Query Statement

    Pat Phelan:

    This both queries returns the same result. But I want to know which one will be better to use to manage huge data. Because I know some methods or some ways to use things in the query may improve or decrease performance.

    JarlH:
    The database look like a mix of object-oriented and NoSQL and NewSQL. But the database have a lots of redundancy and we are not using the database with fully potential. Because in this project they don't use stored procedures, function, triggers, or views. In the solution I'm working right now we have lots of database statements in the source code and a lot of that code can be running directly in the data base.

    This solution is and integration of jsp, java 1.6.0_45, and MariaDB 10.0

    Code:
    UPDATE contracts rc1
    SET rc1.THIRD_PARTY_ERROR_TYPE = 'CLOUD_KEY_DUPLICATE', rc1.REMEDIATION_STATUS = NULL 
    WHERE rc1.PARTNER_LICENSE_ID IS NOT NULL 
    AND rc1.THIRD_PARTY_ERROR_TYPE IN ('DUPLICATE_RECORD') 
    AND rc1.RECORD_TYPE IN ('GENERATED','REMEDIATED') 
    AND rc1.PARTNER_LICENSE_ID IN (SELECT DISTINCT rc2.PARTNER_LICENSE_ID 
                                                       FROM contracts rc2
                                                       WHERE rc2.THIRD_PARTY_ERROR_TYPE IN ('CLOUD_KEY')
                                                       AND rc2.THIRD_PARTY_ERROR_TYPE IS NOT NULL) 
    AND rc1.PARTNER_LICENSE_ID NOT IN (SELECT DISTINCT rc3.PARTNER_LICENSE_ID 
                                                              FROM contracts rc3
                                                              WHERE rc3.RECORD_STATUS IN ('SyncSuccess', 'SyncSuccessReviewRequired','SyncSucessOnSecondRes ponse')
                                                              AND rc3.THIRD_PARTY_ERROR_TYPE IS NOT NULL) 
    AND rc1.REMEDIATION_STATUS IS NOT NULL 
    AND rc1.REMEDIATION_STATUS NOT LIKE 'Need%' 
    AND rc1.REMEDIATION_STATUS NOT LIKE 'EvalReady%';
    Code:
    UPDATE contracts rc1
    SET rc1.THIRD_PARTY_ERROR_TYPE = 'CLOUD_KEY_DUPLICATE', rc1.REMEDIATION_STATUS = NULL 
    WHERE rc1.PARTNER_LICENSE_ID IS NOT NULL 
    AND rc1.THIRD_PARTY_ERROR_TYPE IN ('DUPLICATE_RECORD') 
    AND rc1.RECORD_TYPE IN ('GENERATED','REMEDIATED') 
    AND rc1.PARTNER_LICENSE_ID IN (SELECT DISTINCT rc2.PARTNER_LICENSE_ID 
                                                       FROM contracts rc2
                                                       WHERE rc2.THIRD_PARTY_ERROR_TYPE IN ('CLOUD_KEY')
                                                       AND rc2.THIRD_PARTY_ERROR_TYPE IS NOT NULL
                                                       AND rc1.PARTNER_LICENSE_ID NOT IN (SELECT DISTINCT rc3.PARTNER_LICENSE_ID 
                                                                                                                 FROM contracts rc3
                                                                                                                 WHERE rc3.RECORD_STATUS IN ('SyncSuccess', 'SyncSuccessReviewRequired','SyncSucessOnSecondRes ponse')
                                                                                                                 AND rc3.THIRD_PARTY_ERROR_TYPE IS NOT NULL) ) 
    AND rc1.REMEDIATION_STATUS IS NOT NULL 
    AND rc1.REMEDIATION_STATUS NOT LIKE 'Need%' 
    AND rc1.REMEDIATION_STATUS NOT LIKE 'EvalReady%';
    Also I'm asking because I didn't know to much about this MariaDB and what benefits have.

  8. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by e.vega View Post
    Also I'm asking because I didn't know to much about this MariaDB and what benefits have.
    I'd have asked this in the MySQL section, to get better attention, by people who know the product.

  9. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I often times find that using an EXISTS subselect is faster than using the IN subselect construct that you have in both queries. Does, also, mean that you would be turning your non-correlated subselects into correlated. Though depending on how many rows qualify for your subselect it may still be exponentially faster.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •