Re: Performance question CTM:People timing
Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundbergmailto:john.sundb...@kineticdata.com Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is normal -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to pin the underlying T table into memory. Ask the DBA over there to do that -Guilalume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG] on behalf of John Sundberg [john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Thanks all for the responses. We figured out our slowness. Turns out Oracle statistics had not been updated for 6+ months. Now with 140,000 -- it is near instantaneous on Oracle. -John On Mon, Aug 22, 2011 at 9:20 AM, Andrew C Goodall ago...@jcpenney.commailto:ago...@jcpenney.com wrote: Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the main query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.comhttp://www.jcp.com/ -Original Message- 2011/8/20 John Sundberg john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930tel:%28651%29%20556-0930-work (651) 247-6766tel:%28651%29%20247-6766-cell (651) 695-8577tel:%28651%29%20695-8577-fax john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.comhttp://www.wwrug.com/ ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.orghttp://www.arslist.org/ attend wwrug11 www.wwrug.comhttp://www.wwrug.com/ ARSList: Where the Answers Are The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.orghttp://www.arslist.org/ attend wwrug11 www.wwrug.comhttp://www.wwrug.com/ ARSList: Where the Answers Are -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101
Re: Performance question CTM:People timing
Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundberg Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is normal -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to pin the underlying T table into memory. Ask the DBA over there to do that -Guilalume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of John Sundberg [john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Thanks all for the responses. We figured out our slowness. Turns out Oracle statistics had not been updated for 6+ months. Now with 140,000 -- it is near instantaneous on Oracle. -John On Mon, Aug 22, 2011 at 9:20 AM, Andrew C Goodall ago...@jcpenney.com wrote: Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the main query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com -Original Message- 2011/8/20 John Sundberg john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are The information transmitted is intended only
Re: Performance question CTM:People timing
Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheaultmailto:guilla...@dcshq.com Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundbergmailto:john.sundb...@kineticdata.com Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is normal -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to pin the underlying T table into memory. Ask the DBA over there to do that -Guilalume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG] on behalf of John Sundberg [john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ARSlist: Where the Answers Are_
Re: Performance question CTM:People timing
I have done this in the past:: pin user (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault guilla...@dcshq.comwrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume -- *From:* Action Request System discussion list(ARSList) [ arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] *Sent:* Thursday, September 08, 2011 12:33 PM *To:* arslist@ARSLIST.ORG *Subject:* Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe *From:* Guillaume Rheault guilla...@dcshq.com *Sent:* Thursday, September 08, 2011 12:15 PM *Newsgroups:* public.remedy.arsystem.general *To:* arslist@ARSLIST.ORG *Subject:* Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume -- *From:* Action Request System discussion list(ARSList) [ arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] *Sent:* Thursday, September 01, 2011 2:19 PM *To:* arslist@ARSLIST.ORG *Subject:* Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe *From:* John Sundberg john.sundb...@kineticdata.com *Sent:* Thursday, September 01, 2011 1:31 PM *Newsgroups:* public.remedy.arsystem.general *To:* arslist@ARSLIST.ORG *Subject:* Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is normal -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to pin the underlying T table into memory. Ask the DBA over there to do that -Guilalume -- *From:* Action Request System discussion list(ARSList) [ arslist@ARSLIST.ORG] on behalf of John Sundberg [ john.sundb...@kineticdata.com] *Sent:* Thursday, August 25, 2011
Re: Performance question CTM:People timing
Does anybody know if there is a similar option for SQL Server 2008? Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com http://www.jcp.com/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of patrick zandi Sent: Thursday, September 08, 2011 1:44 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** I have done this in the past:: pin user (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault guilla...@dcshq.com wrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it's a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault mailto:guilla...@dcshq.com Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don't think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn't know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don't really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundberg mailto:john.sundb...@kineticdata.com Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is normal -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info
Re: Performance question CTM:People timing
DBCC PINTABLE Google for it.. search results however may indicate that it might have been discontinued after SQL 2005. I do not have a ready test instance to try it on.. Joe From: Andrew C Goodall Sent: Thursday, September 08, 2011 3:12 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Does anybody know if there is a similar option for SQL Server 2008? Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of patrick zandi Sent: Thursday, September 08, 2011 1:44 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** I have done this in the past:: pin user (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault guilla...@dcshq.com wrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundberg Sent: Thursday, September 01, 2011 1:31 PM Newsgroups
Re: Performance question CTM:People timing
Just did - I see that utility was bad and was taken out for sql server - http://ask.sqlservercentral.com/questions/15490/is-there-a-way-to-force- a-table-into-memory Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com http://www.jcp.com/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: Thursday, September 08, 2011 2:17 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing DBCC PINTABLE Google for it.. search results however may indicate that it might have been discontinued after SQL 2005. I do not have a ready test instance to try it on.. Joe From: Andrew C Goodall mailto:ago...@jcpenney.com Sent: Thursday, September 08, 2011 3:12 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Does anybody know if there is a similar option for SQL Server 2008? Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com http://www.jcp.com/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of patrick zandi Sent: Thursday, September 08, 2011 1:44 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** I have done this in the past:: pin user (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault guilla...@dcshq.com wrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it's a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault mailto:guilla...@dcshq.com Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don't think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle
Re: Performance question CTM:People timing
How would pinning a table impact tables that may have a frequent update? For eg lets say in a case where your customer information that is created and updated frequently on a daily basis, is stored in the People form, and is accessed when creating and updating incident records for them? My understanding when you pin objects to memory, the read is not a frequent read. I do not know at what intervals the memory is updated or if it is updated as soon as there is a change on that object. Anybody with knowledge of that? If the read is as frequent as an update or an insert, what impact would that have on pinning it to the database? Joe From: patrick zandi Sent: Thursday, September 08, 2011 2:44 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** I have done this in the past:: pin user (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault guilla...@dcshq.com wrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume -- From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume -- From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundberg Sent: Thursday, September 01, 2011 1:31 PM
Re: Performance question CTM:People timing
Love these kind of discussions too :-) I completely agree with the cost effectiveness side of the argument in terms of time money.. However while building applications, it does not really cost you that much extra time when you are designing a data schema, to build indexes on columns you think you would need indexes for. When building a data schema, you already know way before you build it, what fields your queries are going to be centered around. Usually it’s a pretty finite list that rarely goes beyond 10 to 15 fields even if you have over 100 columns of data on that form. Yes you may have problems when and if the queries your schema requires, require you to exceed the number of allowable indexes on a schema, which is 32 I think for Oracle and a little higher for MS-SQL (I don’t know the exact number).. The AR System however to the best of my knowledge has a much lower limit – is it 16 or 24??? For all practical purposes however this number is significantly sufficient. Pinning tables is a great fix for a poorly designed / developed application where the developer has not considered performance while developing it and built search related functionality with no consideration for performance, which is causing a meltdown of that application in terms of performance. For the latest version of the ITSM application, I don’t think I found any such holes where indexes are missing where there should have been one on the CTM:People but don’t hold me to that as I have not had any performance related problems with that table recently so didn’t really have the need to analyze that table recently.. The earliest build of ITSM which was known as ITSP was another story. There were like 4 out of the box indexes on that form but about 8 to 10 more candidates for indexes... Joe From: Guillaume Rheault Sent: Thursday, September 08, 2011 2:36 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume
Re: Performance question CTM:People timing
If my memory serves me right, the ability to pin tables in memory was introduced in Oracle 8.0.6, so it's been a while back (more than 10 years ago). With each new database version, this feature has matured, the internals of this feature have changed and matured. But this feature is very solid, very mature and works. You may either google your specific questions or ask a knowledgeable DBA, or somebody that knows all the internals of it. I don't worry about the internals, I only know that it works, by looking at the execution plans and how fast the data is provided. From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 3:22 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** How would pinning a table impact tables that may have a frequent update? For eg lets say in a case where your customer information that is created and updated frequently on a daily basis, is stored in the People form, and is accessed when creating and updating incident records for them? My understanding when you pin objects to memory, the read is not a frequent read. I do not know at what intervals the memory is updated or if it is updated as soon as there is a change on that object. Anybody with knowledge of that? If the read is as frequent as an update or an insert, what impact would that have on pinning it to the database? Joe From: patrick zandimailto:remedy...@gmail.com Sent: Thursday, September 08, 2011 2:44 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** I have done this in the past:: pin user (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault guilla...@dcshq.commailto:guilla...@dcshq.com wrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.netmailto:jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheaultmailto:guilla...@dcshq.com Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume From
Re: Performance question CTM:People timing
One more way to make things even faster in Oracle is to pin the underlying T table into memory. Ask the DBA over there to do that -Guilalume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of John Sundberg [john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Thanks all for the responses. We figured out our slowness. Turns out Oracle statistics had not been updated for 6+ months. Now with 140,000 -- it is near instantaneous on Oracle. -John On Mon, Aug 22, 2011 at 9:20 AM, Andrew C Goodall ago...@jcpenney.commailto:ago...@jcpenney.com wrote: Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the main query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.comhttp://www.jcp.com -Original Message- 2011/8/20 John Sundberg john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930tel:%28651%29%20556-0930-work (651) 247-6766tel:%28651%29%20247-6766-cell (651) 695-8577tel:%28651%29%20695-8577-fax john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.comhttp://www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.orghttp://www.arslist.org attend wwrug11 www.wwrug.comhttp://www.wwrug.com ARSList: Where the Answers Are The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.orghttp://www.arslist.org attend wwrug11 www.wwrug.comhttp://www.wwrug.com ARSList: Where the Answers Are -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
True... good suggestion. Fundamentally - I was looking for what is normal -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to pin the underlying T table into memory. Ask the DBA over there to do that -Guilalume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of John Sundberg [john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Thanks all for the responses. We figured out our slowness. Turns out Oracle statistics had not been updated for 6+ months. Now with 140,000 -- it is near instantaneous on Oracle. -John On Mon, Aug 22, 2011 at 9:20 AM, Andrew C Goodall ago...@jcpenney.com wrote: Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the main query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com -Original Message- 2011/8/20 John Sundberg john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ -- John Sundberg Kinetic Data, Inc. Building a Better Service Experience Recipient of: WWRUG10 Best Customer Service/Support Award WWRUG09 Innovator of the Year Award john.sundb...@kineticdata.com 651.556.0930 I www.kineticdata.com ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundberg Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is normal -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to pin the underlying T table into memory. Ask the DBA over there to do that -Guilalume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of John Sundberg [john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Thanks all for the responses. We figured out our slowness. Turns out Oracle statistics had not been updated for 6+ months. Now with 140,000 -- it is near instantaneous on Oracle. -John On Mon, Aug 22, 2011 at 9:20 AM, Andrew C Goodall ago...@jcpenney.com wrote: Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the main query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com -Original Message- 2011/8/20 John Sundberg john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ -- John Sundberg Kinetic Data, Inc. Building a Better Service Experience Recipient of: WWRUG10 Best Customer Service/Support Award WWRUG09 Innovator of the Year Award john.sundb...@kineticdata.com 651.556.0930 I www.kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
Thanks all for the responses. We figured out our slowness. Turns out Oracle statistics had not been updated for 6+ months. Now with 140,000 -- it is near instantaneous on Oracle. -John On Mon, Aug 22, 2011 at 9:20 AM, Andrew C Goodall ago...@jcpenney.comwrote: Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the main query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com -Original Message- 2011/8/20 John Sundberg john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
140 ms with 20.000 records, MS SQL 2005. Terje From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] On Behalf Of John Sundberg [john.sundb...@kineticdata.com] Sent: 19 August 2011 23:34 To: arslist@ARSLIST.ORG Subject: Performance question CTM:People timing ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
52 ms with 52.000 records, Oracle 10.2 -- J 2011/8/20 John Sundberg john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
I'm a little confused on the question ... but to run the query via the DB takes 63 ms with ~49,000 people records. Oracle database. To run a query via Remedy 'Remedy Login ID' = some user id Takes ~ 1.766 ms to display fully. Thanks, Sean From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Terje Moglestue Sent: Monday, August 22, 2011 7:06 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** 140 ms with 20.000 records, MS SQL 2005. Terje From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] On Behalf Of John Sundberg [john.sundb...@kineticdata.com] Sent: 19 August 2011 23:34 To: arslist@ARSLIST.ORG Subject: Performance question CTM:People timing ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
Typo ... 1.766 seconds instead of 1.766 ms :) From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Garrison, Sean (Norcross) Sent: Monday, August 22, 2011 9:23 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** I'm a little confused on the question ... but to run the query via the DB takes 63 ms with ~49,000 people records. Oracle database. To run a query via Remedy 'Remedy Login ID' = some user id Takes ~ 1.766 ms to display fully. Thanks, Sean From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Terje Moglestue Sent: Monday, August 22, 2011 7:06 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** 140 ms with 20.000 records, MS SQL 2005. Terje From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] On Behalf Of John Sundberg [john.sundb...@kineticdata.com] Sent: 19 August 2011 23:34 To: arslist@ARSLIST.ORG Subject: Performance question CTM:People timing ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.commailto:john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Performance question CTM:People timing
Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the main query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com -Original Message- 2011/8/20 John Sundberg john.sundb...@kineticdata.com: ** How long does it take your DB system to resolve a query for an exact match on CTM:People where the query is 'Remedy Login ID' = some user id Also -- how many records are in your CTM:People -- and what DB are you using? Our sample system is 800ms - with 40,000 records... , Oracle 11g2 (Please get the timings from SQL log) -John -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
