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: [email protected] 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) [[email protected]] on behalf of Joe Martin D'Souza [[email protected]] Sent: Thursday, September 08, 2011 12:33 PM To: [email protected] 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: [email protected] 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) [[email protected]] on behalf of Joe Martin D'Souza [[email protected]] Sent: Thursday, September 01, 2011 2:19 PM To: [email protected] 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: [email protected] 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) [[email protected]] on behalf of John Sundberg [[email protected]] Sent: Thursday, August 25, 2011 7:25 AM To: [email protected] Subject: Re: Performance question CTM:People timing 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"
