Query Optimizer Weirdness in Stored Procedure
(Environment: Sun Enterprise/450, SunOS 5.6, Informix 7.30UC5)
Greetings All! Has anyone out there encountered this same problem, and
figured out a solution? I have a single table with over 10 million
records in it. I am trying to retrieve a small number of records from
it based on a "LIKE" expression against a indexed non-unique varchar()
column - assume it's a name of some sort. The user can type in the
first "n" letters of a search string, and then my program appends a "%"
to the end of the search string. The resulting WHERE clause is
something like "... WHERE varchar_column LIKE 'INFORMIX%'". If I
perform the query using "dbaccess", I get extremely fast response -
sub-second in every case. However, when I perform the *identical* query
in a stored procedure, it runs for 15 minutes (and works, too).
Investigating the "sqexplain.out" information gives me a clue: since the
optimizer cannot determine whether the search literal (the target of the
"LIKE" function) BEGINS with a wildcard or not, it assumes that it
could, and optimizes accordingly, instead performing a serial scan on
those 10 million records. In effect, I wish Informix had a "BEGINS
WITH" function instead of the "LIKE" function, so the stored procedure
query optimizer could take advantage of my index on the name column.
Any suggestions?
Rich
--
Richard C. Auslander
Database Manager
AirFlash, Inc.
1733 Woodside Rd., Suite #110
Redwood City, CA 94061
(650) 556-7928
www.airflash.com
In article <388DFFF0.113BE5D4@airflash.com>,
Richard Auslander <rich@airflash.com>wrote:
>(Environment: Sun Enterprise/450, SunOS 5.6, Informix 7.30UC5)
>
>Greetings All! Has anyone out there encountered this same problem, and
>figured out a solution? I have a single table with over 10 million
>records in it. I am trying to retrieve a small number of records from
>it based on a "LIKE" expression against a indexed non-unique varchar()
>column - assume it's a name of some sort. The user can type in the
>first "n" letters of a search string, and then my program appends a "%"
>to the end of the search string. The resulting WHERE clause is
>something like "... WHERE varchar_column LIKE 'INFORMIX%'". If I
>perform the query using "dbaccess", I get extremely fast response -
>sub-second in every case. However, when I perform the *identical*
>query in a stored procedure, it runs for 15 minutes (and works, too).
>
>Investigating the "sqexplain.out" information gives me a clue: since
>the optimizer cannot determine whether the search literal (the target
>of the "LIKE" function) BEGINS with a wildcard or not, it assumes that
>it could, and optimizes accordingly, instead performing a serial scan
>on those 10 million records. In effect, I wish Informix had a "BEGINS
>WITH" function instead of the "LIKE" function, so the stored procedure
>query optimizer could take advantage of my index on the name column.
>Any suggestions?
>--
>Richard C. Auslander
>Database Manager
Rich, here it is more than a week later and I see nobody responded (at
least I see no replies in deja.com). My expedient diagnosis is that,
apparently, a stored procedure is not the tool for this job. Sad,
surprising, but (it seems) true.
By now, I presume you have given up your quest for a workaround and
abandoned the SPL in favor of a PREPAREd statement with a "?"
placeholder for the matching expression that you have pieced together.
Since you surely are already using a PREPAREd statement to execute the
procedure and retrieve returned rows, this should be a very small
change for you.
BTW, I have seen new optimizer directives (P. 4-145 in the Syntax
Guide, version 9.2) that allow you to specify an index to use. They
look like specialized comments. I do not have 7.3 so I have not had the
chance to play with these, nor to find out if they will have any effect
in a stored procedure. Nor can I be certain if they can force the
optimizer to use an index when it would rather not.
Please let us know what you did.
--
+----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+
|------------------- Bulletin Board Announcement ----------------------|
| Congregants will please note that the bowl at the back of the church |
| bearing the sign "For the Sick" is for monetary contributions only. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Before you buy.
Guys,
Happily, my experience is contrary to yours. Could an "Update Statistics
for Procedure" help you?
Anyway, to confirm , I ran a little test , details of which I've attached.
In essense, SP's behaviour seems to be "Optimize what's possible during
creation, the rest optimize at run-time".
The test procedure runs 3 queries against a table "subscriber_so" that has
an index (resource_id, so_key). Resource_id is numeric, So_key a varchar.
The Details follow - I added comments in the sqexplain.out file.
set explain on;drop PROCEDURE "informix".junk_qry;
CREATE PROCEDURE "informix".junk_qry(
l_resource_id LIKE subscriber_so.resource_id,
l_so_key LIKE subscriber_so.so_key)
RETURNING INTEGER;
DEFINE l_record_id LIKE subscriber_so.record_id;
DEFINE l_qry_key LIKE subscriber_so.so_key;
LET l_qry_key = TRIM(l_so_key) || '%';
FOREACH que_curs FOR
SELECT record_id
INTO l_record_id
FROM subscriber_so
WHERE resource_id = l_resource_id
AND so_key LIKE l_qry_key
RETURN l_record_id WITH RESUME;
END FOREACH;
RETURN 99990 WITH RESUME;
FOREACH que1_curs FOR
SELECT record_id
INTO l_record_id
FROM subscriber_so
WHERE resource_id = l_resource_id
AND so_key = l_so_key
RETURN l_record_id WITH RESUME;
END FOREACH;
RETURN 99991 WITH RESUME;
FOREACH que2_curs FOR
SELECT record_id
INTO l_record_id
FROM subscriber_so
WHERE so_key LIKE l_qry_key
RETURN l_record_id WITH RESUME;
END FOREACH;
END PROCEDURE;
EXECUTE PROCEDURE "informix".junk_qry(20000,'SOKey');
================== Begin Procedure CREATION ==============================
----------
Procedure: informix.junk_qry
select x0.record_id from "informix".subscriber_so x0 where ((x0.resource_id
= ? ) AND (x0.so_key LIKE ? ) )
----------
Procedure: informix.junk_qry
select x0.record_id from "informix".subscriber_so x0 where ((x0.resource_id
= ? ) AND (x0.so_key = ? ) )
QUERY:
------
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.subscriber_so: INDEX PATH
(1) Index Keys: resource_id so_key
Lower Index Filter: (informix.subscriber_so.resource_id = '<VAR>'
AND informix.subscriber_so.so_key = '<VAR>' )
----------
Procedure: informix.junk_qry
select x0.record_id from "informix".subscriber_so x0 where (x0.so_key LIKE
? )
================== End of Procedure CREATION ==============================
================== Begin Procedure EXECUTION ==============================
----------
Procedure: informix.junk_qry
select x0.record_id from "informix".subscriber_so x0 where ((x0.resource_id
= ? ) AND (x0.so_key LIKE ? ) )
QUERY:
------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.subscriber_so: INDEX PATH
(1) Index Keys: resource_id so_key
Lower Index Filter: (informix.subscriber_so.resource_id = 20000 AND
informix.subscriber_so.so_key LIKE 'SOKey%' )
----------
Procedure: informix.junk_qry
select x0.record_id from "informix".subscriber_so x0 where (x0.so_key LIKE
? )
QUERY:
------
Estimated Cost: 3
Estimated # of Rows Returned: 2
1) informix.subscriber_so: SEQUENTIAL SCAN
Filters: informix.subscriber_so.so_key LIKE 'SOKey%'
================== End Procedure EXECUTION ==============================
Cheers,
Rudy
Jacob Salomon wrote:
> In article <388DFFF0.113BE5D4@airflash.com>,
> Richard Auslander <rich@airflash.com>wrote:
>
> >(Environment: Sun Enterprise/450, SunOS 5.6, Informix 7.30UC5)
> >
> >Greetings All! Has anyone out there encountered this same problem, and
> >figured out a solution? I have a single table with over 10 million
> >records in it. I am trying to retrieve a small number of records from
> >it based on a "LIKE" expression against a indexed non-unique varchar()
> >column - assume it's a name of some sort. The user can type in the
> >first "n" letters of a search string, and then my program appends a "%"
> >to the end of the search string. The resulting WHERE clause is
> >something like "... WHERE varchar_column LIKE 'INFORMIX%'". If I
> >perform the query using "dbaccess", I get extremely fast response -
> >sub-second in every case. However, when I perform the *identical*
> >query in a stored procedure, it runs for 15 minutes (and works, too).
> >
> >Investigating the "sqexplain.out" information gives me a clue: since
> >the optimizer cannot determine whether the search literal (the target
> >of the "LIKE" function) BEGINS with a wildcard or not, it assumes that
> >it could, and optimizes accordingly, instead performing a serial scan
> >on those 10 million records. In effect, I wish Informix had a "BEGINS
> >WITH" function instead of the "LIKE" function, so the stored procedure
> >query optimizer could take advantage of my index on the name column.
> >Any suggestions?
> >--
> >Richard C. Auslander
> >Database Manager
>
> Rich, here it is more than a week later and I see nobody responded (at
> least I see no replies in deja.com). My expedient diagnosis is that,
> apparently, a stored procedure is not the tool for this job. Sad,
> surprising, but (it seems) true.
>
> By now, I presume you have given up your quest for a workaround and
> abandoned the SPL in favor of a PREPAREd statement with a "?"
> placeholder for the matching expression that you have pieced together.
> Since you surely are already using a PREPAREd statement to execute the
> procedure and retrieve returned rows, this should be a very small
> change for you.
>
> BTW, I have seen new optimizer directives (P. 4-145 in the Syntax
> Guide, version 9.2) that allow you to specify an index to use. They
> look like specialized comments. I do not have 7.3 so I have not had the
> chance to play with these, nor to find out if they will have any effect
> in a stored procedure. Nor can I be certain if they can force the
> optimizer to use an index when it would rather not.
>
> Please let us know what you did.
> --
> +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+
> |------------------- Bulletin Board Announcement ----------------------|
> | Congregants will please note that the bowl at the back of the church |
> | bearing the sign "For the Sick" is for monetary contributions only. |
> +----------------------------------------------------------------------+
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.