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