Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Location
    Columbus, OH
    Posts
    2

    Unanswered: Performance problem searching on TimeStamp

    Hi,

    I am a MS SQL administrator that is required to connect to a remote DB2 database to pull information via a linked server. I am not sure of the version of DB2 (I think 7?) and have no idea what it is running on.

    I run multiple queries and when I search on a date or time field I get back sub-second responses. However if I search on a Timestamp field it is taking me 5 to 13 minutes to recieve a response. I am at a severe disadvantage to the fact that I can find out almost nothing about the system I have to pull from.

    The actual statement that I am running against db2 looks like this.

    select a.claim_nbr, case_nbr from mcdb.case a, mcdb.contact b
    where (b.update_tstamp > TIMESTAMP(current date , (current time - 5000.0 seconds)))
    and (a.case_nbr = b.case_nbr) and (a.case_nbr is not null)

    I have narrowed the problem down to the timestamp field. Is there a performance consideration, or a better way of performing this that I am unaware of?

    Thanks!
    Radar

  2. #2
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96

    column manipulation in where clause is expensive

    In your query , have lot of column manipulation , avoid column manipulation in where clause, as much as posible, that make much difference.

    Hope it may help.

    Lekharaju Ennam

  3. #3
    Join Date
    May 2004
    Location
    Columbus, OH
    Posts
    2
    I guess that is part of my issue, trying to figure out the best way to pull the data. I am not aware if there is a better way to manipulate it.

    What I need to pull are all records that have been updated within the past 5000 seconds. Is there a better way to look at this in db2?

    Thanks, Again

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Do you have access to the db2 command processor? If yes, please run "DESCRIBE TABLE mcdb.contact" and "DESCRIBE INDEXES FOR TABLE mcdb.contact SHOW DETAIL" and post the output here.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would try"

    current timestamp - 5000 seconds

    You will need an index on contact.update_tstamp
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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