Performance question

achiola · #1 (**permalink**) 08-05-11, 16:59

Hi...
I try run this query in db2 for iSeries 5.4

This query run in 4 seconds ~

Code:

select 	*
from 	pd812dta.f4211
where 	sdIVD 	= 	111217

but this, run in 2 minutes ~

Code:

select 	*
from 	pd812dta.f4211
where 	sdIVD 	= 	((year( '2011-08-05' )-1900)*1000) + ( dayofyear( '2011-08-05'))

any idea? the field sdIVD store a julian date...
TIA
Abel.

przytula_guy · #2 (**permalink**) 08-08-11, 01:56

probably because of the operation on the index. try explain and see

achiola · #3 (**permalink**) 08-08-11, 08:03

thanks for your replay.

achiola · #4 (**permalink**) 08-09-11, 08:08

The visual explain said:

The first qry ( sdIVD = 111217 ) make a index seek, the second a table scan...:X Any idea???

Marcus_A · #5 (**permalink**) 08-09-11, 08:18

DB2 may not use an index if a function is applied to the predicate.

stiruvee · #6 (**permalink**) 08-09-11, 09:13

Same question was asked by you two months ago and answered.

Question about Performance in a Qry

achiola · #7 (**permalink**) 08-09-11, 09:32

Thanks, but NOT is a problem of casting a field... read all post.

stiruvee · #8 (**permalink**) 08-09-11, 09:50

What is the data type for the field sdIVD? Any change in query performance when you force type cast right side in expression to data type of the field sdIVD?

achiola · #9 (**permalink**) 08-09-11, 10:00

nop.. same results, the field sdIVD is numeric(8,0) and ever the query optimizer make a table scan when use a fx in the right side. Thanks for your replay.

tonkuma · #10 (**permalink**) 08-09-11, 10:02

Marcus_A wrote

Quote:

DB2 may not use an index if a function is applied to the predicate.

I found the description on manual "IBM i Database Performance and Query Optimization 7.1"
---> Creating an index strategy ---> Coding for effective indexes

Quote:

Avoid arithmetic expressions
Do not use an arithmetic expression as an operand to compare to a column in a row selection predicate.
The optimizer does not use an index on a column compared to an arithmetic expression. While this
technique might not cause the column index to become unusable, it prevents any estimates and possibly
the use of index scan-key positioning. The primary thing that is lost is the ability to use and extract any
statistics that might be useful in the optimization of the query.

tonkuma · #11 (**permalink**) 08-09-11, 10:09

How about add an extra condition, like...
" AND sdIVD > 0"

or

" AND sdIVD BETWEEN 1 AND 200366"

achiola · #12 (**permalink**) 08-09-11, 10:20

Quote:

Originally Posted by tonkuma

How about add an extra condition, like...
" AND sdIVD > 0"

or

" AND sdIVD BETWEEN 1 AND 200366"

In both cases make a table scan... thanks.

achiola · #13 (**permalink**) 08-09-11, 16:11

My solution was.

Code:

SET PATH='TEMPORAL';
SET SCHEMA='TEMPORAL';

 CREATE FUNCTION GRE2JUL ( FECHA date )
     RETURNS TABLE ( sdDOCO NUMERIC(8,0) )
     LANGUAGE SQL    
     DISALLOW PARALLEL
BEGIN
      declare FECHA_JUL numeric(6,0);
	set FECHA_JUL = ((year( FECHA  ) - 1900)*1000) + ( dayofyear(FECHA));
   	RETURN
	 	select sdDOCO
		from pd812dta.f4211 
		where sdIVD = FECHA_JUL;
  END;

and....

Code:

select 	*  from 	table( TEMPORAL.GRE2JUL ( CAST('2011-08-09' AS DATE) ) ) as x;

Thanks to all...

tonkuma · #14 (**permalink**) 08-09-11, 17:13

If the table function showed good performance,
how about to put the expression in another subquery?

For example:

Code:

SELECT *
 FROM  (SELECT (YEAR(fecha) - 1900) * 1000 + DAYOFYEAR(fecha)
         FROM  (SELECT CAST('2011-08-09' AS DATE)
                 FROM  sysibm.sysdummy1
               ) p(fecha)
       ) gre2jul(fecha_jul)
     , pd812dta.f4211
 WHERE sdIVD = fecha_jul
;

achiola · #15 (**permalink**) 08-09-11, 17:38

Hi Tonkuma. Thanks for your replay.
My first option was this, but, with a subquery not change nothing and make a tablescan...
The performance of table fx is very good, respond in 0-1~ seconds.

Internet Services

Web Development

Digital Marketing

Consumer Tech