Re: [U2] Universe; SQL; Indexes?? (10.3.7 on Windows)
Try and be very specific what it is you are doing and why. For example - why are you using SQL? Are you doing SQL queries via the TCL prompt or using a client tool on Windows? What is the query? What do you mean by 'optimize the performance'...Etc., etc. UV has two query languages and a single database engine. Meaning, there is should be no difference in respect to the utilisation of indices. Clearly, if you have having to do a SQL query that requires unnest or join operation - then indices may not be significant in improving performance. Try adding the EXPLAIN keyword to your query give some clue to what (if any) query optimisation is going on...including of indices. Being unfamiliar with SQL can also be part of the issue...it's extremely powerful and can be difficult to master. Hence not for the faint hearted - especially if the query is complex and/or your table/schema is also complex. You can perform queries which can have a profound impact on the system performance, if incorrectly structured. Indexes aren't the answer to every optimisation issue. There needs to be a balance between cost versus benefit. It depends on the nature and utilisation of the file in question. Therefore: one really needs to optimise one's own SQL skills...not an easy ask...then optimise your query in order maximise expected query performance. Or perhaps avoid SQL, if it is not really necessary to utilise it. Regards, David -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Allen Egerton Sent: Saturday, 28 July 2012 1: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 ** IMPORTANT MESSAGE * This e-mail message is intended only for the addressee(s) and contains information which may be confidential. If you are not the intended recipient please advise the sender by return email, do not use or disclose the contents, and delete the message and any attachments from your system. Unless specifically indicated, this email does not constitute formal advice or commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 124) or its subsidiaries. We can be contacted through our web site: commbank.com.au. If you no longer wish to receive commercial electronic messages from us, please reply to this e-mail by typing Unsubscribe in the subject line. ** ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Universe; SQL; Indexes?? (10.3.7 on Windows)
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
Re: [U2] Universe; SQL; Indexes?? (10.3.7 on Windows)
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] 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
