When you traverse your data via hash (normal record placement) the system has the benefit of the read ahead cache buffer, so you have less disk reads. When using an index it now must jump around reading the data losing the read ahead benefit.
I would look at the way the data is hashing and try to improve performance by minimizing overflow. But be aware an oversized file is slower than a just-fit sized file. David A. Green (480) 813-1725 DAG Consulting -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jeff Butera Sent: Sunday, March 21, 2010 1:03 PM To: U2 Users List Subject: [U2] Index slowing performance? Unidata 7.1.8 on Solaris 9 I've got a subroutine which performs a query, parses some data and then returns a result to the calling process. The routine was consistently taking 20 milliseconds - not too bad but certainly not great - I have other routines that usually take under 10 milliseconds. After re-reading my code, I realize the query the routine was executing wasn't indexed so I created an index on the field used in the query and made no changes to the subroutine at all. Now when I execute my code it consistently takes 33-35 milliseconds. So I'm trying to explain why creating an index would slow performance instead of increase it. The file in question is small - only 239 records to query against - so I understand why it doesn't benefit from an index as a file with 1,000,000 records might. But I'm not clear why performance would degrade. Any insight appreciated. For those who like details: File.................. H08.CR.EVAL.QUES Alternate key length.. 20 Node/Block size....... 4K OV blocks............. 1 (0 in use, 0 overflowed) Indices............... 2 (2 D-type) Index updates......... Enabled, No updates pending Index-Name...... F-type K-type Built Empties Dups In-DICT S/M F-no/VF-expr.... XCEQ.TYPE D Txt Yes No Yes Yes S 3 XCEQ.ORDER D Num Yes No Yes Yes S 4 and the query is: SELECT H08.CR.EVAL.QUES WITH XCEQ.TYPE EQ "foo" BY XCEQ.ORDER Jeff Butera, Ph.D. Manager of ERP Systems Hampshire College jbut...@hampshire.edu 413-559-5556 "Life is a giant weenie roast and I'm the biggest weenie." Rose, The Golden Girls _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
