The SQL statement sort of looks like:
SELECT x.Date, x.Portfolio, x.Symbol, y.DateOrig, y.Price
FROM Tablex x, Tabley y
WHERE
x.KeyField = y.KeyField
AND x.Portfolio = 'TEST';
Portfolio is a D-Type field in Tablex, and when I index it, I'm using
the NO.NULLS option.
Selection behavior is what I would expect with native query language,
(RETRIEVE), when setting up Portfolio as an index or removing it.
However, it seems to make no difference to SQL queries whether the index
is present or not, and that doesn't make sense to me.
On 7/27/2012 11:42 AM, David L. Wasylenko wrote:
> Show us the relent dictionary items, the SQL statement, and the "LIST.INDEX
> {filename} ALL" output
>
> You should also try native SELECT commands using the index to see if
> performance is as expected.
> SELECT ... FROM CUSTOMER WHERE I_ZIP = "12345"; vs
> LIST CUSTOMER WITH I_ZIP = "12345"
>
> Also, try including "REQUIRE.INDEX" and then "NO.INDEX" at the back end of
> your command to see the difference.
> The 'REQUIRE.INDEX' will fail if the selected index could not be used:
>
> REQUIRE.INDEX
> ________________________________________________________________
> Use in a RetrieVe command to specify that secondary key
> indexes must be used to process the sentence. If indexes
> cannot be used, an error message appears and the sentence does
> not proceed.
>
> The 'NO.INDEX' will bypass the use of an index, showing the resulting
> performance change:
>
> NO.INDEX
> _________________________________________________________________
> Use in a RetrieVe command to specify that secondary key
> indexes not be used if they exist. This is useful when indexes
> are not up to date or built.
>
> Finally, the order of occurrence of the selection criteria can impact the use
> of secondary indices.
>
>
> ... david ...
>
> David L. Wasylenko
> President, Pick Professionals, Inc
> w) 314 558 1482
> d...@pickpro.com
>
>
> -----Original Message-----
> From: u2-users-boun...@listserver.u2ug.org
> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Allen Egerton
> Sent: Friday, July 27, 2012 10:35 AM
> To: .U2 List
> Subject: [U2] Universe; SQL; Indexes?? (10.3.7 on Windows)
>
> I'm using SQL to query Universe 10.3.7 data files, (ANSi-compliant and
> non-compliant formats) and indexing the files to try to optimize the
> performance seems in some cases to have little or no effect.
>
> I'm pretty clear on index behavior with Universe's native query language, but
> SQL is somewhat unfamiliar to me, and I'm wondering why I'm not getting the
> behavior I expect.
>
> Can anyone either point me to reference material on this, or offer thoughts
> based on experience?
>
> Thank you.
>
> --
> Allen Egerton; aeger...@pobox.com
> _______________________________________________
> 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
