Index question
Hi, I have a lookup function that works with two tables. The function has been placed as a calculated attribute (call to the function) within a user application. When a user selects table A (1 million rows) and choses the calculated attribute with a count(*), the function does a unique lookup in table B of 280,000 rows and returns a code of char2. Table B has been indexed (unique) and statistics have been run. The process takes 4.5 minutes to complete. My question is as follows. Can I expect the process to complete much faster if table B is reduced to say 70,000 rows? My colleagues say yes, but I'm curious as to how much faster, since the table has a unique index on it. Thank you, Tony
--0__=88BBFA88DFE070DB8f9e8a93df938690918c88BBFA88DFE070DB Content-type: multipart/alternative; Boundary="1__=88BBFA88DFE070DB8f9e8a93df938690918c88BBFA88DFE070DB" --1__=88BBFA88DFE070DB8f9e8a93df938690918c88BBFA88DFE070DB Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Hi Tony, If u r sure that the 2nd query is using the unique index .. if you redu= ce the # of rows, the benefit could be minimal (I think) ..the reason bein= g that the time saved would be in the # of index pages it needs to scan t= o reach the right record .. u'll hv to test it of course, but in my opini= on, you won't see too much of a gain .. Thanx much, Rajib Sarkar Advisory Software Engineer DB2/UDB Regional Advanced Support IBM Data Management Group http://www.ibm.com/software/data/db2/udb/support/ From his neck down a man is worth a couple of dollars a day, from his n= eck up he is worth anything that his brain can produce. -- T. Edison = "Demeis, Tony" = <Tony.Demeis@moh. = gov.on.ca> = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect Index question [5130] = 06/09/2005 01:00 = PM = = = = = Hi, I have a lookup function that works with two tables. The function has been placed as a calculated attribute (call to the function) within a user application. When a user selects table A (1 million rows) and choses the calculated attribute with a count(*), the function does a unique lookup in table B= of 280,000 rows and returns a code of char2. Table B has been indexed (unique) and statistics have been run. The process takes 4.5 minutes to complete. My question is as follows. Can I expect the process to complete much faster if table B is reduced to say 70,000 rows? My colleagues say yes, but I'm curious as to how much faster, since the= table has a unique index on it. Thank you, Tony = --1__=88BBFA88DFE070DB8f9e8a93df938690918c88BBFA88DFE070DB Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <p>Hi Tony,<br> If u r sure that the 2nd query is using the unique index .. if you redu= ce the # of rows, the benefit could be minimal (I think) ..the reason b= eing that the time saved would be in the # of index pages it needs to s= can to reach the right record .. u'll hv to test it of course, but in m= y opinion, you won't see too much of a gain ..<br> <br> Thanx much,<br> <br> Rajib Sarkar<br> Advisory Software Engineer<br> DB2/UDB Regional Advanced Support<br> IBM Data Management Group<br> <a href=3D"http://www.ibm.com/software/data/db2/udb/support/">http://ww= w.ibm.com/software/data/db2/udb/support/</a><br> <br> <br> From his neck down a man is worth a couple of dollars a day, from his n= eck up he is worth anything that his brain can produce. -- T. Edison<br= > <br> <img src=3D"cid:10__=3D88BBFA88DFE070DB8f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "Demeis, Tony&= quot; <Tony.Demeis@moh.gov.on.ca>">"Demeis, Tony" <T= ony.Demeis@moh.gov.on.ca><br> <br> <br> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D88BBFA8= 8DFE070DB8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">"Demeis, Tony" <Tony.Demeis@moh.go= v.on.ca></font></b><font size=3D"2"> </font><br> <font size=3D"2">Sent by: forum.subscriber@iiug.org</font> <p><font size=3D"2">06/09/2005 01:00 PM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBFA88DFE070DB8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D88BBFA88DFE070DB8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBFA88DFE070DB8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D88BBFA88DFE070DB8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBFA88DFE070DB8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D88BBFA88DFE070DB8f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">Index question [5130]</font></td></tr> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D88BBFA88DFE0= 70DB8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D88BBFA88DFE070DB8f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>Hi,<br> I have a lookup function that works with two tables.<br> The function has been placed as a calculated attribute (call to the<br>= function) within a user application.<br> <br> When a user selects table A (1 million rows) and choses the calculated<= br> attribute with a count(*), the function does a unique lookup in table B= of<br> 280,000 rows and returns a code of char2.<br> Table B has been indexed (unique) and statistics have been run.<br> The process takes 4.5 minutes to complete.<br> <br> My question is as follows. Can I expect the process to complete m= uch faster<br> if table B is reduced to say 70,000 rows?<br> My colleagues say yes, but I'm curious as to how much faster, since the
--0__=09BBFA8FDF9D358E8f9e8a93df938690918c09BBFA8FDF9D358E Content-type: multipart/alternative; Boundary="1__=09BBFA8FDF9D358E8f9e8a93df938690918c09BBFA8FDF9D358E" --1__=09BBFA8FDF9D358E8f9e8a93df938690918c09BBFA8FDF9D358E Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable It would help to see the query plan produced. Could you run with set explain? = "Demeis, Tony" = <Tony.Demeis@moh. = gov.on.ca> = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect Index question [5130] = 06/09/2005 03:00 = PM = = = = = Hi, I have a lookup function that works with two tables. The function has been placed as a calculated attribute (call to the function) within a user application. When a user selects table A (1 million rows) and choses the calculated attribute with a count(*), the function does a unique lookup in table B= of 280,000 rows and returns a code of char2. Table B has been indexed (unique) and statistics have been run. The process takes 4.5 minutes to complete. My question is as follows. Can I expect the process to complete much faster if table B is reduced to say 70,000 rows? My colleagues say yes, but I'm curious as to how much faster, since the= table has a unique index on it. Thank you, Tony = --1__=09BBFA8FDF9D358E8f9e8a93df938690918c09BBFA8FDF9D358E Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <p>It would help to see the query plan produced. Could you run with se= t explain?<br> <br> <br> <img src=3D"cid:10__=3D09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "Demeis, Tony&= quot; <Tony.Demeis@moh.gov.on.ca>">"Demeis, Tony" <T= ony.Demeis@moh.gov.on.ca><br> <br> <br> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBFA8= FDF9D358E8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">"Demeis, Tony" <Tony.Demeis@moh.go= v.on.ca></font></b><font size=3D"2"> </font><br> <font size=3D"2">Sent by: forum.subscriber@iiug.org</font> <p><font size=3D"2">06/09/2005 03:00 PM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D09BBFA8FDF9D358E8f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">Index question [5130]</font></td></tr> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBFA8FDF9D= 358E8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBFA8FDF9D358E8f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>Hi,<br> I have a lookup function that works with two tables.<br> The function has been placed as a calculated attribute (call to the<br>= function) within a user application.<br> <br> When a user selects table A (1 million rows) and choses the calculated<= br> attribute with a count(*), the function does a unique lookup in table B= of<br> 280,000 rows and returns a code of char2.<br> Table B has been indexed (unique) and statistics have been run.<br> The process takes 4.5 minutes to complete.<br> <br> My question is as follows. Can I expect the process to complete m= uch faster<br> if table B is reduced to say 70,000 rows?<br> My colleagues say yes, but I'm curious as to how much faster, since the= <br> table has a unique index on it.<br> <br> <br> Thank you,<br> Tony<br> <br> <br> </tt><br> </body></html>= --1__=09BBFA8FDF9D358E8f9e8a93df938690918c09BBFA8FDF9D358E-- --0__=09BBFA8FDF9D358E8f9e8a93df938690918c09BBFA8FDF9D358E Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=09BBFA8FDF9D358E8f9e8a93df938690918c09BBFA8FDF9D358E Content-type: image/gif; name="pic00002.gif" Content-Disposition: inline; filename="pic00002.gif" Content-ID: <20__=09BBFA8FDF9D358E8f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGT
Demeis, Tony said: > Hi, > I have a lookup function that works with two tables. > The function has been placed as a calculated attribute (call to the > function) within a user application. > > When a user selects table A (1 million rows) and choses the calculated > attribute with a count(*), the function does a unique lookup in table B of > 280,000 rows and returns a code of char2. > Table B has been indexed (unique) and statistics have been run. > The process takes 4.5 minutes to complete. What takes 4.5 minutes? I'd imagine all your time is spent doing the count(*) of table A (if I've understood you correctly.) Perhaps a code fragment would help. > My question is as follows. Can I expect the process to complete much > faster > if table B is reduced to say 70,000 rows? > My colleagues say yes, but I'm curious as to how much faster, since the > table has a unique index on it. If the difference is measurable, then there is something wrong with your indexing strategy. :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche A smile is a gift that is free to the giver and precious to the recipient. But giving someone the finger is free too, and I find it more personal and sincere.
Have you tried creating a functional index on table B. CREATE INDEX <indexname> ON tableB ( function<whatever field> ) <Asc/desc>; It might help. Thank you, Kannan Thirugnanam -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Obnoxio The.... Sent: Friday, June 10, 2005 2:06 AM To: ids@iiug.org Subject: Re: Index question [5134] Demeis, Tony said: > Hi, > I have a lookup function that works with two tables. > The function has been placed as a calculated attribute (call to the > function) within a user application. > > When a user selects table A (1 million rows) and choses the calculated > attribute with a count(*), the function does a unique lookup in table B of > 280,000 rows and returns a code of char2. > Table B has been indexed (unique) and statistics have been run. > The process takes 4.5 minutes to complete. What takes 4.5 minutes? I'd imagine all your time is spent doing the count(*) of table A (if I've understood you correctly.) Perhaps a code fragment would help. > My question is as follows. Can I expect the process to complete much > faster > if table B is reduced to say 70,000 rows? > My colleagues say yes, but I'm curious as to how much faster, since the > table has a unique index on it. If the difference is measurable, then there is something wrong with your indexing strategy. :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche A smile is a gift that is free to the giver and precious to the recipient. But giving someone the finger is free too, and I find it more personal and sincere. ***** Jackson Hewitt Email Disclaimer ***** The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. The sender's business entity is not liable for any loss or damage arising in any way from this message or its attachments. Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email.