Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    50

    Unanswered: Query running slow when using >= or <=

    Hi All,

    I have a query.

    SELECT MIN(f.pdate)
    FROM FUNDS f
    WHERE f.fundno = 44
    AND f.user = 'X'
    AND f.pdate >= '1 Jan 2004'

    It is using the correct index but still performing slow.
    I know the problem is with the '>' part of the query.
    Is there anyway i can speed this up

    Thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Whats the index on? and whats the execution plan like. Ideally you would have an index on the three columns though the ordering of the columns would depend on the data distribution.

    Alan

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    A point that might help - You're using ANDs which means that all tests must be successful for the row to be selected, so put the test that is most likely to fail first.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Jun 2003
    Posts
    50
    Index is on pdate,fundno,user (Primary Key)

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Dont forget cis_groupie that the ordering of criteria in the where clause has no effect on the execution plan unless you have the ordered_predicates hint on.

    Alan

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK if your index is on the right columns then there are a few possibilities to consider.
    1. It is having to go through a lot of records before it hits the right user/fundno combo so maybe an index with fundno/user before pdate would help.
    2. Maybe your index has suffered a lot of deletes i.e. lots of wasted space. Compare the size of the index to the size of the table, this should give you a clue as to if this is the case. If it is rebuild the index.
    3.Is the database under heavy load?

    I suspect point 1 is the most likely.

    Alan

  7. #7
    Join Date
    Jun 2003
    Posts
    50
    Option 1 hit the nail on the head.
    Thanks

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    also, you are comparing a data to a string.
    I suggest converting the string with a to_date

    keep in mind right now you would only be matching those dates
    that have a time of midnight
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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