Wild Card/Generic retrieval
Deskin, Bob
Bob.Deskin@Cognos.COM
Wed, 10 Sep 2003 16:11:58 -0400
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C377D7.C9EF6810
Content-Type: text/plain;
charset="iso-8859-1"
Well, QUIZ is retrieving each individual row from the database and then
applying the selection. The SQL is letting the database do all of the
selection before returning anything.
How about trying this:
declare x cursor for select select * from lot_step_summ where lot_id like
'%A'
access x
define mycount num = 1
final footing mycount subtotal
go
or something similar.
Bob
-----Original Message-----
From: Edis, Robert [mailto:REdis@Blistex.com]
Sent: Wednesday, September 10, 2003 3:37 PM
To: Powerh-L (E-mail)
Subject: RE: Wild Card/Generic retrieval
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
-----Original Message-----
From: Darren Reely [ mailto:darren.reely@latticesemi.com
<mailto:darren.reely@latticesemi.com> ]
Sent: Wednesday, September 10, 2003 2:57 PM
Cc: powerh-l@lists.swau.edu
Subject: Re: Wild Card/Generic retrieval
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
altered the queries for my known data and limited them to counting.
In this scenario it is obvious to avoid MATCHPATTERN. I've never been
impressed with its performance. Most of the options proposed came in at
around 53 seconds, MATHCPATTERN added 2 minutes to those times!
Not listed here is a combination of the Oracle SQL select as a cursor
for a new version of the quiz file. It took 2 minutes 47 seconds.
At the end of each select statement are the timings.
Oracle SQL - approximately 4 seconds
select count(*)
from lot_step_summ
where lot_id like '%A';
OR
select count(*)
from (select * from lot_step_summ where lot_id like '%A');
COUNT(*)
----------
145878
Powerhouse times
set report nolimit
access lot_step_summ in mfg
;select lot_step_summ if MATCHPATTERN(TRUNCATE(lot_id),"@A") ;; 2
minutes 53 seconds
;select lot_step_summ If 0 = index(lot_id,"A") - size(truncate(lot_id))
;; 53 seconds fetches too many records, no surprise
;select lot_step_summ if 'A' = REVERSE(TRUNCATE( lot_id ))[1:1] ;; 53
seconds
select if "A" = lot_id[ SIZE(TRUNCATE(lot_id)) :
1] ;; 56 seconds
define mycount num = 1
final footing mycount subtotal
go
09/10/2003 mfg
PAGE 1
MYCOUNT
(SUBTOTAL)
145878
Darren
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
<http://lists.swau.edu/mailman/listinfo/powerh-l>
This list is closed, thus to post to the list you must be a subscriber.
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
<http://www.cognos.com/enterprise03> for more details.
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.
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.
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_01C377D7.C9EF6810
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: Wild Card/Generic retrieval</TITLE>
<META content="MSHTML 5.50.4926.2500" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=369430820-10092003><FONT face=Arial color=#0000ff size=2>Well,
QUIZ is retrieving each individual row from the database and then applying the
selection. The SQL is letting the database do all of the selection before
returning anything.</FONT></SPAN></DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial color=#0000ff size=2>How
about trying this:</FONT></SPAN></DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=369430820-10092003><FONT color=#0000ff size=2><FONT
face=Arial>declare x cursor for select <FONT color=#000000>select *
</FONT></FONT><FONT color=#000000><FONT face=Arial><FONT size=2>from
lot_step_summ</FONT></FONT></FONT><FONT color=#000000><FONT face=Arial><FONT
size=3> </FONT><FONT size=2>where lot_id like
'%A'</FONT></FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial size=2>access
x</FONT></SPAN></DIV>
<DIV><SPAN class=369430820-10092003><FONT size=2><FONT face=Arial>define
mycount num = 1</FONT><FONT face=Arial><FONT size=3> <BR></FONT><FONT
size=2>final footing mycount subtotal</FONT></FONT><FONT face=Arial><FONT
size=3> <BR></FONT><FONT size=2>go</FONT><FONT size=3>
</FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial size=2>or something
similar.</FONT></SPAN></DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=369430820-10092003><FONT face=Arial
size=2>Bob</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Edis, Robert
[mailto:REdis@Blistex.com]<BR><B>Sent:</B> Wednesday, September 10, 2003 3:37
PM<BR><B>To:</B> Powerh-L (E-mail)<BR><B>Subject:</B> RE: Wild Card/Generic
retrieval<BR><BR></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=205473519-10092003>What
IS an issue is 4 sec. for SQL vs. 53 sec for Quiz. Why is Quiz so
slow?</SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Deskin, Bob
[mailto:Bob.Deskin@cognos.com]<BR><B>Sent:</B> Wednesday, September 10, 2003
2:25 PM<BR><B>To:</B> 'Darren Reely'<BR><B>Cc:</B>
powerh-l@lists.swau.edu<BR><B>Subject:</B> RE: Wild Card/Generic
retrieval<BR><BR></FONT></DIV>
<P><FONT size=2>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.</FONT></P>
<P><FONT size=2>Bob</FONT> </P>
<P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From:
Darren Reely [<A
href="mailto:darren.reely@latticesemi.com">mailto:darren.reely@latticesemi.com</A>]</FONT>
<BR><FONT size=2>Sent: Wednesday, September 10, 2003 2:57 PM</FONT>
<BR><FONT size=2>Cc: powerh-l@lists.swau.edu</FONT> <BR><FONT
size=2>Subject: Re: Wild Card/Generic retrieval</FONT> </P><BR>
<P><FONT size=2>I found this interesting enough to 'play' a little with it.
I choose the </FONT><BR><FONT size=2>main table in our Oracle test system
containing 1,482,455 rows. I </FONT><BR><FONT size=2>altered the
queries for my known data and limited them to counting.</FONT> </P>
<P><FONT size=2>In this scenario it is obvious to avoid MATCHPATTERN. I've
never been </FONT><BR><FONT size=2>impressed with its performance. Most of
the options proposed came in at </FONT><BR><FONT size=2>around 53 seconds,
MATHCPATTERN added 2 minutes to those times!</FONT> </P>
<P><FONT size=2>Not listed here is a combination of the Oracle SQL select as
a cursor </FONT><BR><FONT size=2>for a new version of the quiz file. It took
2 minutes 47 seconds.</FONT> </P>
<P><FONT size=2>At the end of each select statement are the timings.</FONT>
</P>
<P><FONT size=2>Oracle SQL - approximately 4 seconds</FONT> </P>
<P><FONT size=2>select count(*)</FONT> <BR><FONT size=2>from
lot_step_summ</FONT> <BR><FONT size=2>where lot_id like '%A';</FONT> </P>
<P><FONT size=2>OR</FONT> </P>
<P><FONT size=2>select count(*)</FONT> <BR><FONT size=2>from (select * from
lot_step_summ where lot_id like '%A');</FONT> </P><BR>
<P><FONT size=2> COUNT(*)</FONT> <BR><FONT size=2>----------</FONT>
<BR><FONT size=2> 145878</FONT> </P><BR>
<P><FONT size=2>Powerhouse times</FONT> </P>
<P><FONT size=2>set report nolimit</FONT> <BR><FONT size=2>access
lot_step_summ in mfg</FONT> <BR><FONT size=2>;select lot_step_summ if
MATCHPATTERN(TRUNCATE(lot_id),"@A") ;; 2 </FONT><BR><FONT
size=2>minutes 53 seconds</FONT> <BR><FONT size=2>;select lot_step_summ If 0
= index(lot_id,"A") - size(truncate(lot_id)) </FONT><BR><FONT size=2>
;; 53 seconds fetches too many records, no surprise</FONT> <BR><FONT
size=2>;select lot_step_summ if 'A' = REVERSE(TRUNCATE( lot_id ))[1:1]
;; 53 </FONT><BR><FONT size=2>seconds</FONT> <BR><FONT size=2>select if "A"
= lot_id[ SIZE(TRUNCATE(lot_id)) : </FONT><BR><FONT
size=2>1]
;; 56 seconds</FONT> <BR><FONT size=2>define mycount num = 1</FONT>
<BR><FONT size=2>final footing mycount subtotal</FONT> <BR><FONT
size=2>go</FONT> </P>
<P><FONT
size=2>09/10/2003
mfg
</FONT><BR><FONT size=2>PAGE 1</FONT> </P>
<P><FONT size=2> MYCOUNT</FONT> <BR><FONT size=2>
(SUBTOTAL)</FONT> </P>
<P><FONT size=2> 145878</FONT> </P><BR>
<P><FONT size=2>Darren</FONT> </P><BR>
<P><FONT size=2>= = = = = = = = = = = = = = = = = = = = = = = = = = =
=</FONT> <BR><FONT size=2>Mailing list: powerh-l@lists.swau.edu</FONT>
<BR><FONT size=2>Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu</FONT> <BR><FONT size=2>Unsubscribe:
"unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu</FONT> <BR><FONT size=2><A target=_blank
href="http://lists.swau.edu/mailman/listinfo/powerh-l">http://lists.swau.edu/mailman/listinfo/powerh-l</A></FONT>
<BR><FONT size=2>This list is closed, thus to post to the list you must be a
subscriber.</FONT> </P>
<P><FONT size=2>Join us at Cognos' biggest event of the year<I> Enterprise
2003, The Cognos Business Forum</I>. 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<U> <A target=_blank
href="http://www.cognos.com/enterprise03">http://www.cognos.com/enterprise03</A></U>
for more details. </FONT></P>
<P><FONT 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></P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
<BR>
<P><FONT SIZE=2 FACE="Arial">Join us at Cognos' biggest event of the year<I> Enterprise 2003, The Cognos Business Forum</I>. 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<U> http://www.cognos.com/enterprise03</U> for more details. </FONT></P>
<BR>
<P><FONT SIZE=2 FACE="Arial">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></P>
------_=_NextPart_001_01C377D7.C9EF6810--