Topic
2 replies Latest Post - ‏2014-03-03T23:56:06Z by RainerRoss
RainerRoss
RainerRoss
11 Posts
ACCEPTED ANSWER

Pinned topic Omnifind - poor performance

‏2014-03-02T14:07:34Z |

Hi,

I've installed OmniFind 1.2 on my new Power720 7+ with V7R1, SMP, the latest PTF-Level for the Database and OmniFind. With the attached program I've created a file 'employee' with 10,000 rows. I've tried serveral SQL statements and the performance difference is great.

statement                                                                                                                                                                                                     runtime     

select empid,skill from mylib.employee where contains(skill,'java' ) = 1  fetch first 10 rows only                                              31ms
   
with x as
(select a.empid,a.skill,row_number() over () as line from mylib.employee a where contains(skill,'java' ) = 1 )                       17066ms
select * from x fetch first 10 rows only                                             

is there a solution for this poor performance?

thanks

    

Attachments

Updated on 2014-03-02T14:16:58Z at 2014-03-02T14:16:58Z by RainerRoss
  • krmilligan
    krmilligan
    445 Posts
    ACCEPTED ANSWER

    Re: Omnifind - poor performance

    ‏2014-03-03T22:52:46Z  in response to RainerRoss

    Do you get the same poor performance running the SQL without a Table Expression?

    select a.empid,a.skill,row_number() over () as line from mylib.employee a where contains(skill,'java' ) = 1

    • RainerRoss
      RainerRoss
      11 Posts
      ACCEPTED ANSWER

      Re: Omnifind - poor performance

      ‏2014-03-03T23:56:06Z  in response to krmilligan

      select a.empid,a.skill,row_number() over () as line from mylib.employee a where contains(skill,'java' ) = 1   runtime  35ms

      I changed my statement, changed file and index to keepinmem(*yes) -  the runtime is 31ms

      select * from     
      (select empid, skill,row_number() over (order by empid asc) as rownum from mylib.employee  where contains(skill,'java and rpg') = 1 ) as cooltable
       fetch first 10 rows only

      a lot of thanks

      Updated on 2014-03-04T13:51:02Z at 2014-03-04T13:51:02Z by RainerRoss