stored procedure query vs dbaccess
On IDS 11.10 (HP-UX), a two-table join returned in ~1 second from dbaccess but took 10+ minutes inside a stored procedure; the SPL plan reversed the join order and scanned 163,335 rows of sa_sale. Hard-coding literal dates instead of the procedure's parameters made it fast again, pointing to the optimizer not using parameter values when building the SPL plan. Replies suggested checking that UPDATE STATISTICS had been run the recommended way (with a link to an IBM technote) and, failing that, using optimizer directives to force the good plan. No confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
Sorry for the long post.
running IDS 11.10.FC2W2 on HPUX.
We are having a problem with a query that runs in dbaccess in about 1 second,
but takes a several minutes (>10) when in a stored procedure.
Here is the explain file from the dbaccess query:
SELECT NVL(SUM(sale_qty * sale_value), 0)
FROM sa, sa_sale
WHERE sa.sa_id = sa_sale.sa_id
AND sa.cust_num = "3002483"
AND inv_date >= "08/01/2008"
AND inv_date <= "08/31/2008"
Estimated Cost: 75
Estimated # of Rows Returned: 1
1) tecsys.sa: INDEX PATH
(1) Index Keys: cust_num ship_num (Serial, fragments: ALL)
Lower Index Filter: tecsys.sa.cust_num = '3002483'
2) tecsys.sa_sale: INDEX PATH
(1) Index Keys: sa_id inv_date
Lower Index Filter: (tecsys.sa.sa_id = tecsys.sa_sale.sa_id AND
tecsys.sa_sale.inv_date >= 08/01/2008 )
Upper Index Filter: tecsys.sa_sale.inv_date <= 08/31/2008
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 sa
t2 sa_sale
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 290 58 290 00:00:01 28
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 45 158954 45 00:00:03 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 45 3 00:00:04 75
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 45 00:00:04
Here is the stored procedure and sqexplain file for the sp.
CREATE PROCEDURE usp_dal_promcondsalesall(
parm_condition_base CHAR(30),
parm_customer_number CHAR(10),
parm_from_date DATE,
parm_to_date DATE)
RETURNING DECIMAL(12,3) AS ret_amount;
DEFINE p_sale_qty LIKE sa_sale.sale_value;
SET ISOLATION TO DIRTY READ;
SELECT NVL(SUM(sale_qty * sale_value), 0)
INTO p_sale_qty
FROM sa, sa_sale
WHERE sa.sa_id = sa_sale.sa_id
AND sa.cust_num = parm_customer_number
AND inv_date >= parm_from_date
AND inv_date <= parm_to_date;
RETURN p_sale_qty;
END PROCEDURE;
Procedure: mdixon.usp_dal_promcondsalesall
Statement id: 4
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 sa_sale
t2 sa
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 163335 1 163335 00:00:02 1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 45 120 163335 00:13:50 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 45 1 00:13:52 2
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 0 45 00:13:52
It looks like the optimizer is scanning the two tables in a different order
I tried removing the NVL function call, but that made no difference. If I
change the sp to use literal dates of "08/01/2008" and "08/31/2008" it runs in
a couple of seconds. Is there something obvious (or not obvious) we are
overlooking.
Yes OTC, we've run update statistics on the tables and sp. ;)
Any help greatly appreciated. Thanks in advance
ANTHONY JUDISH wrote: > > Yes OTC, we've run update statistics on the tables and sp. ;) Yes, but are you sure that the statistics you have are what you think they are? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com
I *hate* doing this but I will now interpret what OTC said:
Did you run the right kind of "update statistics" command?
Here is the recommended way:
http://www-01.ibm.com/support/docview.wss?uid=swg21137764
Take care.
Clifton M. Bean
Informix DBA / AIX System Admin
Currency Technics & Metrics
Main (972) 812-1411 x244
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Obnoxio The Clown
Sent: Tuesday, October 28, 2008 2:41 PM
To: ids@iiug.org
Subject: Re: stored procedure query vs dbaccess [13814]
ANTHONY JUDISH wrote:
>
> Yes OTC, we've run update statistics on the tables and sp. ;)
Yes, but are you sure that the statistics you have are what you think
they are?
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Clifton Bean wrote: > I *hate* doing this but I will now interpret what OTC said: I hate it when you do it, too. :o) Seek enlightenment, grasshopper. Mystery is the way to zen. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com
Maybe optimizer hints in the select of the stored procedure are in order.I
mean force the optimizer to follow the plan you kwnow is better... from time
to time one have to that...
J.
2008/10/28 ANTHONY JUDISH <ajudish@lextron-inc.com>
> Hi all,
>
> Sorry for the long post.
> running IDS 11.10.FC2W2 on HPUX.
>
> We are having a problem with a query that runs in dbaccess in about 1
> second,
> but takes a several minutes (>10) when in a stored procedure.
>
> Here is the explain file from the dbaccess query:
>
> SELECT NVL(SUM(sale_qty * sale_value), 0)
>
> FROM sa, sa_sale
>
> WHERE sa.sa_id = sa_sale.sa_id
>
> AND sa.cust_num = "3002483"
>
> AND inv_date >= "08/01/2008"
>
> AND inv_date <= "08/31/2008"
>
> Estimated Cost: 75
> Estimated # of Rows Returned: 1
>
> 1) tecsys.sa: INDEX PATH
>
> (1) Index Keys: cust_num ship_num (Serial, fragments: ALL)
>
> Lower Index Filter: tecsys.sa.cust_num = '3002483'
>
> 2) tecsys.sa_sale: INDEX PATH
>
> (1) Index Keys: sa_id inv_date
>
> Lower Index Filter: (tecsys.sa.sa_id = tecsys.sa_sale.sa_id AND
> tecsys.sa_sale.inv_date >= 08/01/2008 )
>
> Upper Index Filter: tecsys.sa_sale.inv_date <= 08/31/2008
> NESTED LOOP JOIN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 sa
> t2 sa_sale
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 290 58 290 00:00:01 28
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 45 158954 45 00:00:03 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 45 3 00:00:04 75
>
> type rows_prod est_rows rows_cons time
> -------------------------------------------------
> group 1 1 45 00:00:04
>
> Here is the stored procedure and sqexplain file for the sp.
>
> CREATE PROCEDURE usp_dal_promcondsalesall(>
> parm_condition_base CHAR(30),
>
> parm_customer_number CHAR(10),
>
> parm_from_date DATE,
>
> parm_to_date DATE)
>
> RETURNING DECIMAL(12,3) AS ret_amount;
>
> DEFINE p_sale_qty LIKE sa_sale.sale_value;
>
> SET ISOLATION TO DIRTY READ;>
> SELECT NVL(SUM(sale_qty * sale_value), 0)
>
> INTO p_sale_qty
>
> FROM sa, sa_sale
>
> WHERE sa.sa_id = sa_sale.sa_id
>
> AND sa.cust_num = parm_customer_number
>
> AND inv_date >= parm_from_date
>
> AND inv_date <= parm_to_date;
>
> RETURN p_sale_qty;
>
> END PROCEDURE;
>
> Procedure: mdixon.usp_dal_promcondsalesall
> Statement id: 4
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 sa_sale
> t2 sa
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 163335 1 163335 00:00:02 1
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 45 120 163335 00:13:50 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 45 1 00:13:52 2
>
> type rows_prod est_rows rows_cons time
> -------------------------------------------------
> group 1 0 45 00:13:52
>
> It looks like the optimizer is scanning the two tables in a different order
> I tried removing the NVL function call, but that made no difference. If I
> change the sp to use literal dates of "08/01/2008" and "08/31/2008" it runs
> in
> a couple of seconds. Is there something obvious (or not obvious) we are
> overlooking.
>
> Yes OTC, we've run update statistics on the tables and sp. ;)
>
> Any help greatly appreciated. Thanks in advance
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Strange, I always thought Mystery was the way to government spending.
Someone needs to fix that road sign :)
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Obnoxio The Clown
Sent: Tuesday, October 28, 2008 12:52 PM
To: ids@iiug.org
Subject: Re: stored procedure query vs dbaccess [13816]
Clifton Bean wrote:
> I *hate* doing this but I will now interpret what OTC said:
I hate it when you do it, too. :o)
Seek enlightenment, grasshopper. Mystery is the way to zen.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.