Re: [U2] Unidata index/query ?
On 06/12/13 15:08, jeffrey Butera wrote: Unidata 7.3.3 on RedHat: I have a table with numerous indicies built: 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. Can't speak for UniData, but on *older* versions of UV, this is what would be expected. UV only used one index, and even if further select fields were indexed it would run a full scan of the records selected by the first index. However, this was fixed in UV quite a long time ago. I'd be surprised if it wasn't fixed in modern UD too, either. If you're just doing a select, then I'd try the following (dunno if it's available in UD, don't know the exact correct syntax for UV) SELECT WITH TERM EQ 2013F TO 1 SELECT WITH TYPE EQ FINCRSE TO 2 SELECT INTERSECT 1 2 TO 0 Does that take 10 seconds? Something's bizarrely wrong if so. The other thing, does SELECT have an explain clause that'll tell you what it's doing? If none of this works, just create an i-descriptor of the two fields concatenated, and select on that. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Unidata index/query ?
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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 NumYes Yes Yes Yes S 8 XCCE.STATUS D TxtYes No Yes Yes S 17 XCCE.COURSE.NAME D TxtYes No Yes Yes S 14 XCCE.TERM D TxtYes No Yes Yes S 12 XCCE.TYPE D TxtYes No Yes Yes S 22 XCCE.SUBJECT D TxtYes 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 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
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 dgr...@dagconsulting.com 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 NumYes Yes Yes Yes S 8 XCCE.STATUS D TxtYes No Yes Yes S 17 XCCE.COURSE.NAME D TxtYes No Yes Yes S 14 XCCE.TERM D TxtYes No Yes Yes S 12 XCCE.TYPE D TxtYes No Yes Yes S 22 XCCE.SUBJECT D TxtYes 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 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Unidata index/query ?
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 dgr...@dagconsulting.com 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 NumYes Yes Yes Yes S 8 XCCE.STATUS D TxtYes No Yes Yes S 17 XCCE.COURSE.NAME D TxtYes No Yes Yes S 14 XCCE.TERM D TxtYes No Yes Yes S 12 XCCE.TYPE D TxtYes No Yes Yes S 22 XCCE.SUBJECT D TxtYes 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 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Unidata index/query ?
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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 dgr...@dagconsulting.com 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 NumYes Yes Yes Yes S 8 XCCE.STATUS D TxtYes No Yes Yes S 17 XCCE.COURSE.NAME D TxtYes No Yes Yes S 14 XCCE.TERM D TxtYes No Yes Yes S 12 XCCE.TYPE D TxtYes No Yes Yes S 22 XCCE.SUBJECT D TxtYes 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 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 ___ 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
Re: [U2] Unidata index/query ?
Maybe I am just old school but I would put another WITH in the line after the AND. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Friday, December 06, 2013 7: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 NumYes Yes Yes Yes S 8 XCCE.STATUS D TxtYes No Yes Yes S 17 XCCE.COURSE.NAME D TxtYes No Yes Yes S 14 XCCE.TERM D TxtYes No Yes Yes S 12 XCCE.TYPE D TxtYes No Yes Yes S 22 XCCE.SUBJECT D TxtYes 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 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
