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!!!!!!!!!!!

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:


Clive

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.

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