Wild Card/Generic retrieval

Wed, 10 Sep 2003 14:36:43 -0500

This is a multi-part message in MIME format.

------_=_NextPart_001_01C377D2.DD5FAFDB
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

What IS an issue is 4 sec. for SQL vs. 53 sec for Quiz.  Why is Quiz so =
slow?

-----Original Message-----
From: Deskin, Bob [mailto:Bob.Deskin@cognos.com]
Sent: Wednesday, September 10, 2003 2:25 PM
To: 'Darren Reely'
Cc: powerh-l@lists.swau.edu
Subject: RE: Wild Card/Generic retrieval

Thanks Darren for confirming that pattern matching is still quite =
resource intensive relative to the other PowerHouse functions. While we =
can't say exactly how much more intensive without testing various =
volumes, I'm not surprised at these results. What is interesting is how =
close the other function tests are.

Bob=20

-----Original Message-----=20
From: Darren Reely [ mailto:darren.reely@latticesemi.com]=20
Sent: Wednesday, September 10, 2003 2:57 PM=20
Cc: powerh-l@lists.swau.edu=20
Subject: Re: Wild Card/Generic retrieval=20

I found this interesting enough to 'play' a little with it. I choose the =

main table in our Oracle test system containing 1,482,455 rows.  I=20
altered the queries for my known data and limited them to counting.=20

In this scenario it is obvious to avoid MATCHPATTERN. I've never been=20
impressed with its performance. Most of the options proposed came in at=20
around 53 seconds, MATHCPATTERN added 2 minutes to those times!=20

Not listed here is a combination of the Oracle SQL select as a cursor=20
for a new version of the quiz file. It took 2 minutes 47 seconds.=20

At the end of each select statement are the timings.=20

Oracle SQL - approximately 4 seconds=20

select count(*)=20
from lot_step_summ=20
where lot_id like '%A';=20

OR=20

select count(*)=20
from (select * from lot_step_summ where lot_id like '%A');=20

  COUNT(*)=20
----------=20
    145878=20

Powerhouse times=20

set report nolimit=20
access lot_step_summ in mfg=20
;select lot_step_summ if MATCHPATTERN(TRUNCATE(lot_id),"@A")  ;; 2=20
minutes 53 seconds=20
;select lot_step_summ If 0 =3D index(lot_id,"A") - =
size(truncate(lot_id))=20
  ;; 53 seconds fetches too many records, no surprise=20
;select lot_step_summ if 'A' =3D REVERSE(TRUNCATE( lot_id ))[1:1]  ;; 53 =

seconds=20
select if "A" =3D lot_id[ SIZE(TRUNCATE(lot_id)) :=20
1]                           ;; 56 seconds=20
define mycount  num =3D 1=20
final footing mycount subtotal=20
go=20

09/10/2003                            mfg                             =20
PAGE   1=20

   MYCOUNT=20
  (SUBTOTAL)=20

    145878=20

Darren=20

=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =
=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D=20
Mailing list: powerh-l@lists.swau.edu=20
Subscribe: "subscribe" in message body to =
powerh-l-request@lists.swau.edu=20
Unsubscribe: "unsubscribe <password>" in message body to =
powerh-l-request@lists.swau.edu=20
http://lists.swau.edu/mailman/listinfo/powerh-l=20
This list is closed, thus to post to the list you must be a subscriber.=20

Join us at Cognos' biggest event of the year Enterprise 2003, The Cognos =
Business Forum.  Taking place in over 25 cities around the world, it's =
an opportunity for Business and IT leaders to learn about strategies for =
driving performance. Visit http://www.cognos.com/enterprise03 for more =
details.=20

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.

------_=_NextPart_001_01C377D2.DD5FAFDB
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>RE: Wild Card/Generic retrieval</TITLE>

<META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR></HEAD>
<BODY>
<DIV>What=20
IS an issue is 4 sec. for SQL vs. 53 sec for Quiz.&nbsp; Why is Quiz so=20
slow?</DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
 <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft>-----Original Message----- From: Deskin, Bob=20
 [mailto:Bob.Deskin@cognos.com] Sent: Wednesday, September =
10, 2003=20
 2:25 PM To: 'Darren Reely' Cc:=20
 powerh-l@lists.swau.edu Subject: RE: Wild Card/Generic=20
 retrieval </DIV>
 Thanks Darren for confirming that pattern matching =
is still=20
 quite resource intensive relative to the other PowerHouse functions. =
While we=20
 can't say exactly how much more intensive without testing various =
volumes, I'm=20
 not surprised at these results. What is interesting is how close the =
other=20
 function tests are.
 Bob 
 -----Original Message----- From:=20
 Darren Reely [<A=20
 =
href=3D"mailto:darren.reely@latticesemi.com">mailto:darren.reely@lattices=
emi.com</A>]=20
 Sent: Wednesday, September 10, 2003 2:57 PM =
 <FONT=20
 size=3D2>Cc: powerh-l@lists.swau.edu Subject: Re: Wild=20
 Card/Generic retrieval 
 I found this interesting enough to 'play' a little =
with it. I=20
 choose the main table in our Oracle test =
system=20
 containing 1,482,455 rows.&nbsp; I altered =
the queries=20
 for my known data and limited them to counting. 
 In this scenario it is obvious to avoid =
MATCHPATTERN. I've=20
 never been impressed with its performance. =
Most of the=20
 options proposed came in at around 53 =
seconds,=20
 MATHCPATTERN added 2 minutes to those times! 
 Not listed here is a combination of the Oracle SQL =
select as a=20
 cursor for a new version of the quiz file. =
It took 2=20
 minutes 47 seconds. 
 At the end of each select statement are the =
timings.=20
 
 Oracle SQL - approximately 4 seconds 
 select count(*) from=20
 lot_step_summ where lot_id like '%A'; =

 OR 
 select count(*) from =
(select * from=20
 lot_step_summ where lot_id like '%A'); 
 &nbsp; COUNT(*) ----------=20
 &nbsp;&nbsp;&nbsp; 145878 
 Powerhouse times 
 set report nolimit access=20
 lot_step_summ in mfg ;select lot_step_summ =
if=20
 MATCHPATTERN(TRUNCATE(lot_id),"@A")&nbsp; ;; 2 minutes=20
 53 seconds ;select lot_step_summ If 0 =3D=20
 index(lot_id,"A") - size(truncate(lot_id)) &nbsp; ;;=20
 53 seconds fetches too many records, no surprise <FONT=20
 size=3D2>;select lot_step_summ if 'A' =3D REVERSE(TRUNCATE( lot_id =
))[1:1]&nbsp;=20
 ;; 53 seconds select if "A" =3D=20
 lot_id[ SIZE(TRUNCATE(lot_id)) : <FONT=20
 =
size=3D2>1]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;=20
 ;; 56 seconds define mycount&nbsp; num =3D =
1=20
 final footing mycount subtotal <FONT=20
 size=3D2>go 
 <FONT=20
 =
size=3D2>09/10/2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 =
mfg&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 PAGE&nbsp;&nbsp; 1 
 &nbsp;&nbsp; MYCOUNT &nbsp;=20
 (SUBTOTAL) 
 &nbsp;&nbsp;&nbsp; 145878 
 Darren 
 =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =
=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D=20
 Mailing list: powerh-l@lists.swau.edu =
 <FONT=20
 size=3D2>Subscribe: "subscribe" in message body to=20
 powerh-l-request@lists.swau.edu Unsubscribe: =

  "unsubscribe &lt;password&gt;" in message body to=20
  powerh-l-request@lists.swau.edu</FONT> <BR><FONT size=3D2><A=20
  href=3D"http://lists.swau.edu/mailman/listinfo/powerh-l"=20
  =
target=3D_blank>http://lists.swau.edu/mailman/listinfo/powerh-l</A></FONT=
>=20
  <BR><FONT size=3D2>This list is closed, thus to post to the list you =
must be a=20
  subscriber.</FONT> </P>
  <P><FONT size=3D2>Join us at Cognos' biggest event of the year<I> =
Enterprise=20
  2003, The Cognos Business Forum</I>.&nbsp; Taking place in over 25 =
cities=20
  around the world, it's an opportunity for Business and IT leaders to =
learn=20
  about strategies for driving performance. Visit<U> <A=20
  href=3D"http://www.cognos.com/enterprise03"=20
  target=3D_blank>http://www.cognos.com/enterprise03</A></U> for more =
details.=20
  </FONT></P>
  <P><FONT size=3D2>This message may contain privileged and/or =
confidential=20
  information.&nbsp; If you have received this e-mail in error or are =
not the=20
  intended recipient, you may not use, copy, disseminate or distribute =
it; do=20
  not open any attachments, delete it immediately from your system and =
notify=20
  the sender promptly by e-mail that you have done so.&nbsp; Thank=20
  you.</FONT></P></BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C377D2.DD5FAFDB--