<HTML xmlns:eXclaimer="http://www.exclaimer.co.uk">
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-16">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-16"><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2668" name=GENERATOR></HEAD><BODY ><DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff size=2>There
are two issues here. First is how QUIZ deals with UNIQUE indexes and second the
fact that this is relational.</FONT></SPAN></DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff size=2>When
linking to a UNIQUE index, QUIZ assumes that there is only one record/row to
retrieve. So in the statement </FONT></SPAN></DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff size=2>ACCESS
A LINK TO B</FONT></SPAN></DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff size=2>where
B is retrieved via a unique index, for each A, QUIZ will attempt to retrieve
only one B record/row. And of course it is the first such record/row that you
see in your record complex.</FONT></SPAN></DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff size=2>Now on
to relational. While QUIZ knows that the index is unique from the database
schema, it retrieves the data as if it was an indexed file as opposed to
generating a SELECT with a JOIN. As well, the individual SELECTs are passed to
the database and it is the database that determines how to retrieve the data and
which row to return. If you specify VIAINDEX in the linkage, we'll pass down an
ORDERBY so that you get the first row in the index, however that would only be
meaningful with a repeating index and not in this case (sorry for the brief
tangent).</FONT></SPAN></DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff size=2>It is
well known that using cursors is typically more efficient that straight linkage.
In this case I suggest you do a performance comparison between the ACCESS
statement that you have and a DECLARE CURSOR using the appropriate
JOIN.</FONT></SPAN></DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=640050113-11082005><FONT face=Arial color=#0000ff
size=2>Bob</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com] <B>On Behalf
Of </B>fernando.olmos@hpa.com.au<BR><B>Sent:</B> August 10, 2005 7:49
PM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> Indexed or
Seq?<BR><BR></FONT></DIV><!-- Converted from text/rtf format -->
<P><SPAN lang=en-us><FONT face=Arial size=2>Hi everyone,</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face=Arial size=2>I've been given the task of
speeding up record complexes for a master/detail set of tables, where the
detail table</FONT> <FONT face=Arial size=2>has</FONT> <FONT face=Arial
size=2>over 3 million records. Don't worry, I am not going to ask the obvious,
but I was wondering if someone can help me determine how QUIZ look</FONT><FONT
face=Arial size=2>s</FONT> <FONT face=Arial size=2>up</FONT> <FONT face=Arial
size=2>data for where one the lookup field is the first field in the UNIQUE
index set.</FONT></SPAN></P>
<P><SPAN lang=en-us><FONT face=Arial size=2>Here are the QSHOWs for the two
tables in question ...</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>
Record:
INVOICED_DETAIL</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1> of
Database: #####</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>
Organization: RELATIONAL</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1> Null
Values Allowed:No</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>
Type:
ORACLE</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1> Hold
Cursor: Unspecified</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>
Open:
#####</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1> Record
Size: 558 Bytes</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>-- Record Contents
--</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>
Item
Type
Size Occ Offset</FONT></SPAN> <BR><SPAN lang=en-us><FONT
face="Courier New" size=1>r
ORDER_NO
INTEGER SIGNED
8
0</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>r
PRIMARY_ID
CHARACTER
2
8</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>r
SECONDARY_ID
CHARACTER
2 10</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>r
PROD_LVL0
CHARACTER
2 12</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>r
PROD_LVL1
CHARACTER
2 14</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>r
PROD_LVL2
CHARACTER
2 16</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>r
PROD_LVL3
CHARACTER
2 18</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>r
PROD_LVL4
CHARACTER
2 20</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>r
PRODUCT_CODE
VARCHAR
16
22</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>r
EVENT_ID
INTEGER SIGNED
4 38</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>..</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>etc</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>..</FONT></SPAN> </P><BR>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>-- Index Contents
--</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1> ** ORDER_KEY_ID is
a UNIQUE index **</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>
Segment
Type
Size Ord Offset</FONT></SPAN> <BR><SPAN lang=en-us><FONT
face="Courier New" size=1>
ORDER_NO
INTEGER SIGNED 8
A 0</FONT></SPAN> <BR><SPAN
lang=en-us><FONT face="Courier New" size=1>
PRIMARY_ID
CHARACTER
2 A 8</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>
SECONDARY_ID
CHARACTER
2 A 10</FONT></SPAN> <BR><SPAN
lang=en-us><FONT face="Courier New" size=1>
PROD_LVL0
CHARACTER
2 A 12</FONT></SPAN> <BR><SPAN
lang=en-us><FONT face="Courier New" size=1>
PROD_LVL1
CHARACTER
2 A 14</FONT></SPAN> <BR><SPAN
lang=en-us><FONT face="Courier New" size=1>
PROD_LVL2
CHARACTER
2 A 16</FONT></SPAN> <BR><SPAN
lang=en-us><FONT face="Courier New" size=1>
PROD_LVL3
CHARACTER
2 A 18</FONT></SPAN> <BR><SPAN
lang=en-us><FONT face="Courier New" size=1>
PROD_LVL4
CHARACTER
2 A 20</FONT></SPAN> <BR><SPAN
lang=en-us><FONT face="Courier New" size=1>
EVENT_ID
INTEGER SIGNED 4
A 22</FONT></SPAN> <BR><SPAN lang=en-us><FONT
face="Courier New" size=1>
PRODUCT_SEQ_NO
INTEGER SIGNED 4
A 26</FONT></SPAN> </P><BR>
<P><SPAN lang=en-us><FONT face=Arial size=2>Other indexes are all REPEATING
and do not reference any of the above indexes</FONT></SPAN> </P><BR><BR>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>
Record:
INVOICED_MASTER</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1> of
Database: #####</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>
Organization: RELATIONAL</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1> Null
Values Allowed:No</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>
Type:
ORACLE</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1> Hold
Cursor: Unspecified</FONT></SPAN>
<BR><SPAN lang=en-us><FONT face="Courier New" size=1>
Open:
#####</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1> Record
Size: 591 Bytes</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>-- Record Contents
--</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>
Item
Type
Size Occ Offset</FONT></SPAN> <BR><SPAN lang=en-us><FONT
face="Courier New" size=1>r
ORDER_NO
INTEGER SIGNED
8
0</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>..</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>etc</FONT></SPAN> <BR><SPAN lang=en-us><FONT face="Courier New"
size=1>..</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>-- Index Contents
--</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1> ** ORDER_NO_IM is
a UNIQUE index **</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>
Segment
Type
Size Ord Offset</FONT></SPAN> <BR><SPAN lang=en-us><FONT
face="Courier New" size=1>
ORDER_NO
INTEGER SIGNED 8
A 0</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face=Arial size=2>Again, there are other repeating
indexes, but they are not relevant to my query.</FONT></SPAN> </P><BR>
<P><SPAN lang=en-us><FONT face=Arial size=2>If I link the two tables using a
simple direct link, like the following ...</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face="Courier New" size=1>access invoiced_master in
hisdb &</FONT></SPAN> <BR><SPAN
lang=en-us> <FONT face="Courier New"
size=1>link order_no to order_no of invoiced_detail in hisdb</FONT></SPAN>
</P>
<P><SPAN lang=en-us><FONT face=Arial size=2>Will QUIZ use the UNIQUE index
defined in INVOICED_DETAIL to look its records directly, or because the
ORDER_NO column is not<B> explicitly</B> defined as a primary key (not unique
of course), will it use sequential searching?</FONT></SPAN></P>
<P><SPAN lang=en-us><FONT face=Arial size=2>I hope I made myself clear here.
If not please ask.</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face=Arial size=2>PS: this email contains rich text
formatting. Please forgive me if it appears in your mailbox all over the
place!</FONT></SPAN> </P>
<P><SPAN lang=en-us><FONT face=Arial size=2>Thanks guys.</FONT></SPAN>
</P></BLOCKQUOTE></DIV>
<DIV> </DIV>
<DIV> <FONT FACE="Arial" SIZE="2">This message may contain privileged and/or confidential information. If you have received this e-mail in error or are not the intended recipient, you may not use, copy, disseminate or distribute it; do not open any attachments, delete it immediately from your system and notify the sender promptly by e-mail that you have done so. Thank you.</FONT>
</DIV>
<DIV>
<P>
<FONT FACE="Arial" SIZE="2"> </FONT>
</P>
</DIV></BODY></HTML>