I'm concluding that D3 doesn't support 2 simultaneous indexes. I deleted both indexes, timed a SELECT with 2 WITH's. Took about 85 seconds. Did a single SELECT WITH and then another SELECT WITH with the results and it went down to around 40 seconds. Re-indexed the DATE field and the first SELECT WITH was immediate but the second totalled to around 40 seconds again. Tried with 2 indexes and it went back to around 85 seconds.
I optimized by deleting the CUST index and kept the DATE (it produced more items for one CUST than for one DATE, both were immediate) and then readv'd within a subroutine to get the specific CUST. Thus the DATE index remained. The users seemed happy with this approach. It ended up being around 15 seconds at best. Thanks. ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <u2-users@listserver.u2ug.org> Sent: Friday, October 21, 2005 2:43 AM Subject: Re: [U2] Double Indexes > UV _should_ optimise 2-up secondary index selections (Glenn put a > really good whitepaper on the list about indexes once - I can't find > my copy now), use LIST/EXPLAIN to see if your indices are in use... My > 2mil record test file uses 2 indices without a struggle returning a > result in about a second or so. > >LIST SUPPORD IF REC.TYPE = "O" AND IF ORD.DATE = "11/04/04" EXPLAIN > Single-variable predicates processed in index: > F6 = 'O' AND F3 = '13251' > Optimizing query block 0 > Driver source: SUPPORD > Access method: select list 0 with 249 ids > UniVerse/SQL: Press any key to continue or 'Q' to quit > > > -- > Or try MERGE.LIST to see if that improves performance: > SELECT FILE WITH DATE = "10.18" TO 1 > SELECT FILE WITH CUST = "12345" TO 2 > MERGE.LIST 1 INTERSECT 2 > [EMAIL PROTECTED] wrote: ----- > >To: > >From: "Mark Johnson" > >Sent by: [EMAIL PROTECTED] > >Date: 21/10/2005 13:50 > >Subject: [U2] Double Indexes > > > >I'm wondering if a UD or UV system behaves similarly to a D3 problem > >I see. > > > >File contains 6,000,000 records. First field is DATE and 2nd field is > >CUST. > >Both are INDEXed. > > > >SELECT FILE WITH DATE = "10.18" > >quickly returns 11,000 items > >CLEARSELECT > >SELECT FILE WITH CUST = "12345" > >quickly returns 180,000 items > >CLEARSELECT > >SELECT FILE WITH DATE = "10.18" AND CUST = "12345" > >takes forever as if the indexes aren't there. > > > >I don't know if the double test in the SELECT statement ignores the > >INDEX on > >either (or the first) or is encumbered by taking the 11,000 quick > >DATE items > >and then hashingly goes through them looking for CUST. > > > >If I try > >SELECT FILE WITH DATE = "10.18" > >then > >SELECT FILE WITH CUST = "12345" > >it still takes longer than I would expect. > > > >Is a double INDEX a strange animal. Does U2 support indexing virtual > >fields, > >ie DATE.CUST? I don't think D3 can. > > > >Thanks in advance. > >Mark Johnson > >------- > >u2-users mailing list > >u2-users@listserver.u2ug.org > >To unsubscribe please visit http://listserver.u2ug.org/ > > > > ********************************************************************** > This email message and any files transmitted with it are confidential > and intended solely for the use of addressed recipient(s). If you have > received this email in error please notify the Spotless IS Support > Centre (+61 3 9269 7555) immediately, who will advise further action. > This footnote also confirms that this email message has been scanned > for the presence of computer related viruses. > ********************************************************************** > ------- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
