Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2016
    Posts
    3

    Unanswered: Query Optimization

    Hi All ,
    Need help in optimizing a query :

    There are two tables with below fields:
    Table 1
    AdminId
    Policy
    Locationid
    CreateTimestamp

    Table 2
    AdminId
    Policy
    CreateTimestamp
    Detail1
    Detail2
    .
    Detail50

    Table 2 is a big table will lots of data. For every rec in Table1 there can be thousands of rows in Table2.
    Requirement is to get the count last one year data from table 2 which have location = NY .
    I wrote below query but its not optimized and m not able to run it.
    Please suggest how to optimize this query. Thanks in advance!

    Select count(*) from Table2 T2 , Table1 T1 where T1.adminId = T2.adminId and T1.policy = T2.policy and T1.location like ('℅NY℅') and T2.createTimestamp > ( current Timestamp - 1 year)

  2. #2
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    Sometimes it is not the query that is the problem. Your query looks very compact and to the point. The only questions I have are what does the data T1.location look like? (using "LIKE '%NY%") is not a good idea unless there are few rows in the table. Second question, do you have an index on T2 (adminid, policy, creatTimestamp)?

    Andy

  3. #3
    Join Date
    Apr 2016
    Posts
    3
    Quote Originally Posted by ARWinner View Post
    Sometimes it is not the query that is the problem. Your query looks very compact and to the point. The only questions I have are what does the data T1.location look like? (using "LIKE '%NY%") is not a good idea unless there are few rows in the table. Second question, do you have an index on T2 (adminid, policy, creatTimestamp)?

    Andy
    Hi Andy,

    It is necessary to use a wildcard for locationid as its a 15 byte field and 'NY' comes somewhere in the middle. I also tried using SUBSTR in place of LIKE but its not solving the purpose . Adminid n policy are the primary keys in both the tables.
    Whenever I am trying to run this query in production env using QMF I am getting below error:
    Unsuccessful execution because DB2 limit exceeded. Resource name ASUTIME , limit = 0000000009

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You're hitting the RLF limit, you can talk to your DBA. Explain your SQL and see what the current access path is. I don't know anything about your app or size of table 1. Maybe you should talk to someone about why STATE isn't its own field. If the requirement is high enough can you put an index on expression for this combo column to pull out the state field?
    Dave

Tags for this Thread

Posting Permissions

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