July 18th, 2013, 03:00 AM
Index not used in subquery
This takes 0.001 seconds to execute and it uses index seek
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
SELECT * FROM CUSTOMER WHERE ID IN (1008,1122)
Now when I combine them, it takes around half-a-second to execute and index is not used
SELECT ID FROM U_VIP //returns (1008,1122)
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?
SELECT * FROM CUSTOMER WHERE ID IN (SELECT ID FROM U_VIP)
**Using Firebird 2.1
July 18th, 2013, 03:50 AM
Use inner join.
CUSTOMER INNER JOIN U_VIP ON U_VIP.ID = CUSTOMER.ID
July 18th, 2013, 04:11 AM
Yes, INNER JOIN does work for index seek, but consider this:
(: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:
[IF :AREAID > 0]
INNER JOIN (SELECT ID FROM U_VIP(:AREAID)) ON U_VIP.ID = CUSTOMER.ID
July 18th, 2013, 04:56 AM
One way is to rewrite your stored procedure to return all Customers' IDs when the parameter AREAID is 0.
July 18th, 2013, 05:59 AM
An inner join is not necessarily a replacement for a sub-select. They might return completely different results.
Originally Posted by mIRCata
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions: