Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Unanswered: Performance question

    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.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    probably because of the operation on the index. try explain and see
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    thanks for your replay.

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    The visual explain said:

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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 may not use an index if a function is applied to the predicate.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Same question was asked by you two months ago and answered.


    http://www.dbforums.com/db2/1667257-...ml#post6506479

  7. #7
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Thanks, but NOT is a problem of casting a field... read all post.

  8. #8
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    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?

  9. #9
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    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.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Marcus_A wrote
    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
    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.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about add an extra condition, like...
    " AND sdIVD > 0"

    or

    " AND sdIVD BETWEEN 1 AND 200366"

  12. #12
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Quote Originally Posted by tonkuma View Post
    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.

  13. #13
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    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...

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  15. #15
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •