#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    Index not used in subquery


    This takes 0.001 seconds to execute and it uses index seek

    Code:
    SELECT * FROM CUSTOMER WHERE ID IN (1008,1122)
    Now I have a stored procedure U_VIP which returns the same ID as example one (1008,1122), and it takes only 0.001 second to execute

    Code:
    SELECT ID FROM U_VIP    //returns (1008,1122)
    Now when I combine them, it takes around half-a-second to execute and index is not used

    Code:
    SELECT * FROM CUSTOMER WHERE ID IN (SELECT ID FROM U_VIP)
    I've simplified the example above, in actual application the performance is impacted by much higher magnitude. How to force Firebird to use index in this case?

    **Using Firebird 2.1
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Use inner join.
    Code:
    SELECT 
        CUSTOMER.* 
    FROM 
        CUSTOMER INNER JOIN U_VIP ON U_VIP.ID = CUSTOMER.ID
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0
    Yes, INNER JOIN does work for index seek, but consider this:

    SELECT
    CUSTOMER.*
    FROM
    CUSTOMER
    WHERE
    (:AREAID = 0 OR CUSTOMER.ID IN (SELECT ID FROM U_VIP(:AREAID)))

    I need to conditionally filter the customer ID based upon input parameter :AREAID, how can I achieve this using JOIN method?

    Something like this:
    SELECT
    CUSTOMER.*
    FROM
    CUSTOMER
    [IF :AREAID > 0]
    INNER JOIN (SELECT ID FROM U_VIP(:AREAID)) ON U_VIP.ID = CUSTOMER.ID
    [END IF]
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    One way is to rewrite your stored procedure to return all Customers' IDs when the parameter AREAID is 0.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by mIRCata
    Use inner join.
    Code:
    SELECT 
        CUSTOMER.* 
    FROM 
        CUSTOMER INNER JOIN U_VIP ON U_VIP.ID = CUSTOMER.ID
    An inner join is not necessarily a replacement for a sub-select. They might return completely different results.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo