Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: Need help with a performance tuning query

    Hi,
    I have a requirement that needs the following,
    There is a table T which has 10 columns,
    I will be passing values for 8 columns in the predicate and I need to find out the records from this table based on any 6 out of 8 columns that are matching with the values being passed in the predicate.
    There is a solution that I can use intersect operator with 8 queries on the same table with each query having one column from the 8 conditions. but I am not sure it will work out as I think this query has to run on the table that has millions of records and may have performance implications.

    Any suggestion is really appreciable. Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >There is a solution that I can use intersect operator with 8 queries on the same table with each query having one column from the 8 conditions.

    I don't understand what this means.
    Can you post small test case as an example?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    Thanks for your reply.
    I have a table Trade which has columns tradeDate,securityRef,custAccount,deliveryAgent,se ttlementRef,transType,settlementCurrencyRef,settle mentEntityRef

    when I receive a message(MDB message) with values for these eight columns
    ,I need to find out records matching any 6 out of eight columns(coming in the message) from Trade table.
    I thought of writing a query like,
    Select * from Trade where tradeDate=<<tradeDate coming in the message>>
    intersect
    Select * from Trade where securityRef=<<securityRef coming in the message>>
    intersect
    Select * from Trade where deliveryAgent=<<deliveryAgent coming in the message>>
    intersect
    Select * from Trade where settlementRef=<<settlementRef coming in the message>>
    ..
    ..
    like this for all eight columns.
    Since the Trade table has millions of records and nature of running this query is multiple times, I am not sure the query I am writing will work out with out any performance impact.
    Any suggestions would be really greatful. Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Thanks for the explanation.
    I now think I understand the scope of the problem.
    I may be over complicating it, but I don't see a trivial or efficient solution for now.

    There are 37 different combinations where at least 6 of 8 are columns are valid.
    Last edited by anacedent; 09-03-11 at 22:28.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2011
    Posts
    5
    Thanks for the reply.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by senthilmoha View Post
    I thought of writing a query like,
    Select * from Trade where tradeDate=<<tradeDate coming in the message>>
    intersect
    Select * from Trade where securityRef=<<securityRef coming in the message>>
    intersect
    Select * from Trade where deliveryAgent=<<deliveryAgent coming in the message>>
    intersect
    Select * from Trade where settlementRef=<<settlementRef coming in the message>>
    ..
    ..
    like this for all eight columns.
    I don't think it matches the "any 6 out of 8 conditions" requirement. I'd say it's more like "all of the 8 conditions".

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    Thanks for your reply. You are right, the query I have posted matches all 8 conditions. But I need a query that does 6 out of 8 matches. If you could help me with that, it is really greatful.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'll give you an example where 2 out of 3 conditions are used, and you can extrapolate:
    Code:
    Select * from Trade where 
    (tradeDate=? and securityRef=?) or 
    (tradeDate=? and deliveryAgent=?) or
    (securityRef=? and deliveryAgent=?)
    Obviously, you will also need to account for NULL parameters.

  9. #9
    Join Date
    Jan 2011
    Location
    Lansing, Michigan
    Posts
    2
    Depending on what app tier you're running, you could potentially use 0 length strings to do this.

    We do something similar at our company for the generic user search page. A user can input any of up to 15 fields to return a list of matches. They can put in all 15, or just 1. Performance in this case is a huge issue, as the user putting in the actual system ID results in almost immediate return, while the user putting in 1 or 2 date fields resulted in 1-3 minute returns.

    It's pretty ugly, but we ended up using a 0 length string for any varchar that was being passed in; i.e.:

    Code:
    select *
      from table
     where (:B1 IS NULL OR table_id = :B1)
        and (:B2 IS NULL OR table_col2 = :B2)
    etc. We're using oracle weblogic v10 on the app tier, and 11gR1 on the DB. The above still causes some performance issues (half the time the real version of this query causes an FTS) but the concept might point you in the right direction.

    Hope that helps.

  10. #10
    Join Date
    Sep 2011
    Posts
    5
    Thank you all for your reply.

Posting Permissions

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