Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: Table join optimalization

    Hello everybody,

    i need a help with optimalization of COUNT function. Both tables have more than 3 mil. rows.

    SELECT count(*)
    FROM VIEW_1 t2, SP t22
    WHERE t2.ID=t22.ID

    The statement takes more that 1,5 min. and if I use just
    SELECT t2.ID
    FROM VIEW_1 t2, SP t22
    WHERE t2.ID=t22.ID
    it takes about 400ms.

    BUT I really need a number how many rows is same :/

    Could anybody help me, please?!

    Thanks!

    Regards,
    Ondrej

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You are making a classic mastake that many programmers have made. It might take 400ms to return the FIRST ROW, however it will take your 1.5 minutes to get all the rows back.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    do you have indexes on ID columns?

  4. #4
    Join Date
    Jan 2003
    Posts
    41
    Quote Originally Posted by madafaka
    do you have indexes on ID columns?
    Yes, of course ... columns are indexed as primary key

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    check explain plan for your select, if tables are full accessed try
    Code:
    SELECT count(1) 
    FROM VIEW_1 t2, SP t22 
    WHERE t2.ID=t22.ID
    Last edited by madafaka; 11-07-05 at 12:38.

  6. #6
    Join Date
    Dec 2001
    Posts
    80
    Use
    SELECT count(t2.id)
    FROM VIEW_1 t2, SP t22
    WHERE t2.ID=t22.ID

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by madafaka
    SELECT count(1)
    FROM VIEW_1 t2, SP t22
    WHERE t2.ID=t22.ID
    Quote Originally Posted by matthewlau
    Use
    SELECT count(t2.id)
    FROM VIEW_1 t2, SP t22
    WHERE t2.ID=t22.ID
    Both of these suggestions will perform no faster or slower than the original. COUNT(*) is perfectly fine.

Posting Permissions

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