Results 1 to 5 of 5

Thread: Query problem

  1. #1
    Join Date
    Feb 2003
    Posts
    23

    Query problem

    I am trying to run a query

    select count (distinct a.stu_id ) as feeWaivers
    from rtrttbl a, rtpgtbl b, bdrttbl c
    where a.term_cyt = '20041'
    and a.stu_id = b.stu_id
    and a.term_cyt = c.term_cyt
    and a.term_cyt = b.term_cyt
    and a.stu_id = c.stu_id


    The query runs perfectly fine. However, when i replace the parameter value of a.term_cyt from 20041 to 20044 it runs forever. Infact it creates problem only for 20044 paramter.

    Both the term_cyt and stu_id are indexed in all the tables.

    What could be reason for this problem?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try query rtrttbl only with that number to see what happens.

  3. #3
    Join Date
    Feb 2003
    Posts
    23
    I queried all individual tables with 20044 and they returned valid reponse quite quickly.

    Infact, when i tried to see the estimation plan through query analyser. It gives a different execution plan for 20044 alone. While all other follow a similar plan.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Does these table have large number of rows for 20044?

    You can also try to add one more condition in WHERE clause

    select count (distinct a.stu_id ) as feeWaivers
    from rtrttbl a, rtpgtbl b, bdrttbl c
    where a.term_cyt = '20041'
    and a.stu_id = b.stu_id
    and a.term_cyt = c.term_cyt
    and a.term_cyt = b.term_cyt
    and a.stu_id = c.stu_id
    and b.term_cyt = c.term_cyt

  5. #5
    Join Date
    Feb 2003
    Posts
    23
    I think I got it. The problem was difference in char/varchar types in stu_id field in different tables.

Posting Permissions

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