Dave: Thanks. But I did that too (I also showed the REQUIRE.INDEX keyword can't come at the end of the sentence).
>2 Demo (0)-> select GLPOST REQUIRE.INDEX WITH INDEX_2 LIKE "0605..." > >Index can not be used processing this query. >2 Demo (0)-> Bill >-----Original Message----- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of Dave Davis >Sent: Wednesday, August 15, 2007 1:13 PM >To: u2-users@listserver.u2ug.org >Subject: RE: [U2] UD: Using indexes in UniQuery > >Place the REQUIRE.INDEX option after "select GLPOST" and before the >first WITH. You can't place it after the criteria in UniData. > >REQUIRE.INDEX works in ecltype U but not ecltype P. > >You can also use "NO.INDEX" to force it NOT to use the index. Try: > >select GLPOST NO.INDEX WITH INDEX_2 LIKE "0605..." > >It might be saying you can't use the index because it is interpreting >0605 as a pattern instead of a literal. > >LIKE "'0605'..." instead of "0605..." > >As for the queries taking longer, are the files sized correctly? > > >-----Original Message----- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of Bill Haskett >Sent: Wednesday, August 15, 2007 3:35 PM >To: u2-users@listserver.u2ug.org >Subject: [U2] UD: Using indexes in UniQuery > >I forgot to add: > >UD v7.1.9 >SORT.TYPE = 2 (forced to do this because of improper sorting in >SORT.TYPE 0) > >Bill > > _____ > >From: Bill Haskett [mailto:[EMAIL PROTECTED] >Sent: Wednesday, August 15, 2007 12:25 PM >To: 'u2-users@listserver.u2ug.org' >Subject: UD: Using indexes in UniQuery > > >I'm having difficulty ensuring the indexes are used in >UniQuery. In D3, >if I do the >following: > >26 Demo (0)-> SELECT GLPOST WITH INDEX_2 = "0605]" > >[4041] 21791 items selected. > >...it would always use the index if one existed. I knew the index was used >because >the selected message didn't include the " out of {n} items." string appended >to the >end (e.g. "[404] 21791 items selected out of 884083 items."). > >On an old P3 server, running D3, with 50 people on it, 512Mb memory (shared by >Linux), this took about 9 seconds. On a new Intel Xeon server, running >UniData, with >5 people on it, 2Gb memory, Windows 2K3, this took about 19 seconds. > >In UniData, I notice some selects are taking significantly longer than in D3. >I've >properly indexed the file, I can test that indexing works by using a BASIC >program >than does the usual SETINDEX, READXFWD, READXBCK, etc. The index looks >like: > >22 Demo (0)-> LIST.INDEX GLPOST >Alternate Key Index Details for File GLPOST Page 1 > >File.................. GLPOST >Alternate key length.. 45 >Node/Block size....... 4K >OV blocks............. 1 (0 in use, 0 overflowed) >Indices............... 2 (0 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.... >INDEX_1 V Txt Yes Yes Yes Yes S >YRMO_PSTD >: JRNL >INDEX_2 V Txt Yes Yes Yes Yes S OCONV( >CLIENTNO, > > 'MR%4' ) >: YRMO > _PSTD : >OCONV( A > CCTNO, >'MR%6' ) > : OCONV( >@ID, 'M > R%8' ) > >2 Demo (0)-> LIST GLPOST INDEX_2 >LIST GLPOST INDEX_2 12:14:43 Aug 15 2007 1 >GLPOST INDEX_2................. > >829572 026020040700407000829572 >377626 014020030300407000377626 >583056 060520030800307000583056 >500884 077020000500407000500884 >952830 095020050100513000952830 > > >When I try the REQUIRE.INDEX keyword it seems to fail. > >2 Demo (0)-> select GLPOST WITH INDEX_1 LIKE "200702..." REQUIRE.INDEX >In E:\DataTrust\DTA\BP\BP\_TCL.SHELL at line 1023 select GLPOST WITH >INDEX_1 LIKE >"200702..." REQUIRE.INDEX >In E:\DataTrust\DTA\BP\BP\_TCL.SHELL at line 1023 >--------------------------------------------------------^ >In E:\DataTrust\DTA\BP\BP\_TCL.SHELL at line 1023 syntax error > >2 Demo (0)-> select GLPOST REQUIRE.INDEX WITH INDEX_2 LIKE "0605..." > >Index can not be used processing this query. >2 Demo (0)-> > >Does anyone know how to make sure indexes are used automatically by UniQuery, >or how >to make them work period? I can't seem to find anything in the UDT.OPTIONS >manual. > >Thanks. > >Bill Haskett ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
