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

    Join Date
    Oct 2010
    Posts
    7
    Rep Power
    0

    Paramitised queries prevent use of index


    I have this SQL statement;

    SELECT
    QUOTES.QUOTE_REF,
    SPARES.CODE,
    QUOTE_LINES.QTY,
    QUOTES.QUOTED,
    QUOTES.QUOTE_ID
    FROM
    SPARES
    INNER JOIN QUOTE_LINES ON QUOTE_LINES.SPARES_REF = SPARES.REF
    INNER JOIN QUOTES ON QUOTES.REF = QUOTE_LINES.QUOTE_REF
    WHERE
    SPARES.CODE like 'FAN 010%'
    ORDER BY
    QUOTES.QUOTED DESC

    It runs fast and uses this query plan;

    PLAN SORT (JOIN (SPARES INDEX (RDB$34),QUOTE_LINES INDEX (I_QUOTE_LINES_SPARES_REF),QUOTES INDEX (PK_QUOTES)))

    However, in my delphi application i would like to replace the criteria in the WHERE clause for a parameter. It is my understanding that it is good practice to use parameters because they protect against SQL injection and you can also utilise prepared queries.

    So i changed the SQL statement to;

    SELECT
    QUOTES.QUOTE_REF,
    SPARES.CODE,
    QUOTE_LINES.QTY,
    QUOTES.QUOTED,
    QUOTES.QUOTE_ID
    FROM
    SPARES
    INNER JOIN QUOTE_LINES ON QUOTE_LINES.SPARES_REF = SPARES.REF
    INNER JOIN QUOTES ON QUOTES.REF = QUOTE_LINES.QUOTE_REF
    WHERE
    SPARES.CODE like :CODE
    ORDER BY
    QUOTES.QUOTED DESC

    however, now the query runs really slow because it no longer uses the same query plan. The query plan it sugests is;

    PLAN SORT (JOIN (QUOTES NATURAL,QUOTE_LINES INDEX (I_QUOTE_LINES_QUOTE_REF),SPARES INDEX (RDB$33)))

    Why is it now using a NATURAL search on the QUOTES table?

    If i sugest the original query plan it states that RDB$34 cannot be used in this query plan.

    help!!!!!!!!!!!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    WHERE
    SPARES.CODE like 'FAN 010%'
    If you use like, Firebird has no way of knowing whether you will pass in a value that can use the index.

    Watch what happens to your plan if you use this where clause instead:
    Code:
    WHERE
    SPARES.CODE like '%FAN 010%'
    Clive
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    7
    Rep Power
    0

    Smile


    Yes, it's making a bit more sense now.

    The odd thing is, i'm not using the LIKE operator on the QUOTES table. I am in fact using it on the SPARES table.

    I have since changed my program to use a stored procedure instead;

    for SELECT
    QUOTE_REF,
    SPARES.CODE,
    QUOTE_LINES.QTY
    FROM
    SPARES
    INNER JOIN QUOTE_LINES ON QUOTE_LINES.SPARES_REF = SPARES.REF
    WHERE
    SPARES.CODE starting with :SE_CODE
    PLAN JOIN (SPARES INDEX (I_SPARES_CODE,RDB$34),QUOTE_LINES INDEX (I_QUOTE_LINES_SPARES_REF))
    into
    :iQuoteRef,
    :CODE,
    :QTY
    do begin
    ...

    I have refrained from using STARTING WITH in the past because it restricts the user from entering wild cards at the begining of the search criteria. But thinking about it, if i use a wildcard at the begining of the search string, then any index would be useless anyway and a NATURAL search would have to be performed. However on large tables with serveral hundreds of thousands of records this is really slow. But i guess the is no solution.

    Anyway, thanks.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I have refrained from using STARTING WITH in the past because it restricts.......
    But i guess the is no solution.
    Perhaps you are restricting the universe of solutions in which you are searching

    I do not know enough about your application to understand what is possible but the following
    options spring to mind immediately:

    1. Offer the user two options, one that uses STARTING WITH one LIKE; wrapped, of course, in appropriate end user instructions and explanations.

    2. Consider expanding the schema with some field that is a sub-string of the main field that can always use an index. This may fail Normalization rules but pass practical performance scrutiny. Alternatively, some category field(??) like 'FANS'.

    In other words, look beyond simply crafting the SQL to achieve your goal.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo