Yes, Doug if an index is properly done it will make a huge difference. But there are times when using an index can be slower. And this is the point I'm making.
David A. Green (480) 201-7953 DAG Consulting -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Doug Averch Sent: Friday, December 06, 2013 9:13 AM To: U2 Users List Subject: Re: [U2] Unidata index/query ? Hi David: In Unidata indexes DO mean faster selects. I have file with millions of records with about 14 indexes and most every select on the index comes back in under a second. Our disks are 90,000 IO's per second with a 16GB main memory, without indexes to select and read through that data takes 5 to 10 minutes minimum. I have an open ticket with Rocket Software about indexes failing after 7.3.2. I had a similar problem and reduced my version from 7.3.4 to 7.3.2. Regards, Doug www.u2logic.com "XLr8Tools for Universe and Unidata programmers" On Fri, Dec 6, 2013 at 9:00 AM, David A. Green <[email protected]> wrote: > Jeffrey, > > And index is a B(Binary)+ tree, the plus means that it auto adjusts > and balances itself as it builds or shrinks. > > After UniData 5.2 UniQuery will use as many indices as it can. In your > case it is the equivalent of doing two UniQuery selects to two > different lists and then doing a MERGE.LIST. > > If you are always wanting a combined index lookup on those two values > you can create an I-Descriptor that combines them and index the I-Descriptor. > Then use the I-Descriptor in your select statements. > > Remember using an Index doesn't always mean faster selects. If your > index brings back a lot of records then you are processing them by > index pointer and the disk reads will be all over the file. Whereas > without the index you are reading the file going through it by groups > and it will minimize the disk reads. > > David A. Green > (480) 201-7953 > DAG Consulting > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of jeffrey > Butera > Sent: Friday, December 06, 2013 8:09 AM > To: U2 Users List > Subject: [U2] Unidata index/query ? > > Unidata 7.3.3 on RedHat: I have a table with numerous indicies built: > > File.................. H08.CR.COURSE.EVALS Alternate key length.. 20 > Node/Block size....... 4K OV blocks............. 1 (0 in use, 0 > overflowed) Indices............... 6 (6 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.... > XCCE.STUDENT.ID D Num Yes Yes Yes Yes S 8 > XCCE.STATUS D Txt Yes No Yes Yes S 17 > XCCE.COURSE.NAME D Txt Yes No Yes Yes S 14 > XCCE.TERM D Txt Yes No Yes Yes S 12 > XCCE.TYPE D Txt Yes No Yes Yes S 22 > XCCE.SUBJECT D Txt Yes No Yes Yes S 23 > > > Since these are data fields (nothing computed on-the-fly) and indexed, > queries should be fast. The table has approximately 737,000 records. > > This query runs in under 1 second: > > SELECT H08.CR.COURSE.EVALS WITH XCCE.TERM EQ '2013F' > > However this query takes 10+ seconds (or longer) - even with two > indexed > fields: > > SELECT H08.CR.COURSE.EVALS WITH XCCE.TERM EQ '2013F' AND XCCE.TYPE EQ > 'FINCRSE' > > I'm at a loss to explain the second, any insight appreciated. > > -- > Jeffrey Butera, PhD > Associate Director for Application and Web Services Information > Technology Hampshire College > 413-559-5556 > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
