Re: Performnace Issue Due To Report Fetching
Since you say that T28 is the Report form I would suggest doing the following. Turn on SQL Logging on the server and capture the actual SQL statement being sent to the database (What you have below is the cached version and debugging is always easier when you see the actual query data instead of the db parameter fields). Also... How many reports do you have defined in your Report Form? What version of Remedy are you running? Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of manoj jain Sent: Friday, July 17, 2009 6:57 AM To: arslist@ARSLIST.ORG Subject: Performnace Issue Due To Report Fetching Hi Remedians, I need some suggesstions from your side related to performance issue. Our DBA team got one query due to this query performance issue occuring. Query: High I/O query SELECT T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C90013,C536870929,C536870931,C536870914,C536871018,C536870938,C65000,C536870952,C536871025,C536871038,C53690,C536870912,C7,C900040206,C901092014,C900110076,C536870987,C901170021 FROM T28 WHERE (((T28.C112 LIKE :SYS_B_00) OR ((T28.C112 LIKE :SYS_B_01) OR ((T28.C112 LIKE :SYS_B_02) OR ((T28.C112 LIKE :SYS_B_03) OR ((T28.C112 LIKE :SYS_B_04) OR ((T28.C112 LIKE :SYS_B_05) OR ((T28.C112 LIKE :SYS_B_06) OR (T28.C112 LIKE :SYS_B_07 AND (((T28.C536871018 :SYS_B_08) AND ((T28.C65000 LIKE :SYS_B_09) OR (T28.C65000 LIKE :SYS_B_10))) AND (T28.C536871019 IS NULL)) AND (((T28.C536870938 = :SYS_B_11) AND (T28.C536870938 = (:SYS_B_12 + ((:SYS_B_13 * :SYS_B_14) * :SYS_B_15 OR ((T28.C900110022 = :SYS_B_16) AND (T28.C900110022 (:SYS_B_17 + ((:SYS_B_18 * :SYS_B_19) * :SYS_B_20)) OR (((T28.C536870984 = :SYS_B_21) AND (T28.C536871019 IS NULL)) AND ((T28.C900110022 = :SYS_B_22) AND (T28.C900110022 (:SYS_B_23 + ((:SYS_B_24 * :SYS_B_25) * :SYS_B_26)) AND (T28.C7 != :SYS_B_27)) AND (T28.C7 != :SYS_B_28))) ORDER BY :SYS_B_29 ASC T28 is Report form So the problem is that number of users try to fetch report simultaneously from the server. So what could be the solution for this? Thanks Regards, Manoj Jain ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Performnace Issue Due To Report Fetching
Here are a few suggestions: - Build better indexes - Archive old data in the subject table to reduce overall table size - Use fewer LIKE statements...i.e., reduce ANYWHERE pattern matching in fields where it's not necessary. LIKE is a performance killer - Consider implementing the NOLOCK option Norm -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of manoj jain Sent: Friday, July 17, 2009 6:57 AM To: arslist@ARSLIST.ORG Subject: Performnace Issue Due To Report Fetching ** Hi Remedians, I need some suggesstions from your side related to performance issue. Our DBA team got one query due to this query performance issue occuring. Query: High I/O query SELECT T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019 ,C900200400,C536870958,C536870950,C900170325,C536870935,C90013,C5368 70929,C536870931,C536870914,C536871018,C536870938,C65000,C536870952, C536871025,C536871038,C53690,C536870912,C7,C900040206,C901092014,C90 0110076,C536870987,C901170021 FROM T28 WHERE (((T28.C112 LIKE :SYS_B_00) OR ((T28.C112 LIKE :SYS_B_01) OR ((T28.C112 LIKE :SYS_B_02) OR ((T28.C112 LIKE :SYS_B_03) OR ((T28.C112 LIKE :SYS_B_04) OR ((T28.C112 LIKE :SYS_B_05) OR ((T28.C112 LIKE :SYS_B_06) OR (T28.C112 LIKE :SYS_B_07 AND (((T28.C536871018 :SYS_B_08) AND ((T28.C65000 LIKE :SYS_B_09) OR (T28.C65000 LIKE :SYS_B_10))) AND (T28.C536871019 IS NULL)) AND (((T28.C536870938 = :SYS_B_11) AND (T28.C536870938 = (:SYS_B_12 + ((:SYS_B_13 * :SYS_B_14) * :SYS_B_15 OR ((T28.C900110022 = :SYS_B_16) AND (T28.C900110022 (:SYS_B_17 + ((:SYS_B_18 * :SYS_B_19) * :SYS_B_20)) OR (((T28.C536870984 = :SYS_B_21) AND (T28.C536871019 IS NULL)) AND ((T28.C900110022 = :SYS_B_22) AND (T28.C900110022 (:SYS_B_23 + ((:SYS_B_24 * :SYS_B_25) * :SYS_B_26)) AND (T28.C7 != :SYS_B_27)) AND (T28.C7 != :SYS_B_28))) ORDER BY :SYS_B_29 ASC T28 is Report form So the problem is that number of users try to fetch report simultaneously from the server. So what could be the solution for this? Thanks Regards, Manoj Jain _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Performnace Issue Due To Report Fetching
You can replicate the database on another server and use that strictly for reporting. Your data will be behind, by how much depends on how you replicate. Even if you do need up to the minute reports, you can at least off load the user who don't need that. Ben Cantatore Remedy Manager (914) 457-6209 Emerging Health IT 3 Odell Plaza Yonkers, New York 10701 norm.kai...@eglin.af.mil 07/17/09 8:50 AM Here are a few suggestions: - Build better indexes - Archive old data in the subject table to reduce overall table size - Use fewer LIKE statements...i.e., reduce ANYWHERE pattern matching in fields where it's not necessary. LIKE is a performance killer - Consider implementing the NOLOCK option Norm -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of manoj jain Sent: Friday, July 17, 2009 6:57 AM To: arslist@ARSLIST.ORG Subject: Performnace Issue Due To Report Fetching ** Hi Remedians, I need some suggesstions from your side related to performance issue. Our DBA team got one query due to this query performance issue occuring. Query: High I/O query SELECT T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019 ,C900200400,C536870958,C536870950,C900170325,C536870935,C90013,C5368 70929,C536870931,C536870914,C536871018,C536870938,C65000,C536870952, C536871025,C536871038,C53690,C536870912,C7,C900040206,C901092014,C90 0110076,C536870987,C901170021 FROM T28 WHERE (((T28.C112 LIKE :SYS_B_00) OR ((T28.C112 LIKE :SYS_B_01) OR ((T28.C112 LIKE :SYS_B_02) OR ((T28.C112 LIKE :SYS_B_03) OR ((T28.C112 LIKE :SYS_B_04) OR ((T28.C112 LIKE :SYS_B_05) OR ((T28.C112 LIKE :SYS_B_06) OR (T28.C112 LIKE :SYS_B_07 AND (((T28.C536871018 :SYS_B_08) AND ((T28.C65000 LIKE :SYS_B_09) OR (T28.C65000 LIKE :SYS_B_10))) AND (T28.C536871019 IS NULL)) AND (((T28.C536870938 = :SYS_B_11) AND (T28.C536870938 = (:SYS_B_12 + ((:SYS_B_13 * :SYS_B_14) * :SYS_B_15 OR ((T28.C900110022 = :SYS_B_16) AND (T28.C900110022 (:SYS_B_17 + ((:SYS_B_18 * :SYS_B_19) * :SYS_B_20)) OR (((T28.C536870984 = :SYS_B_21) AND (T28.C536871019 IS NULL)) AND ((T28.C900110022 = :SYS_B_22) AND (T28.C900110022 (:SYS_B_23 + ((:SYS_B_24 * :SYS_B_25) * :SYS_B_26)) AND (T28.C7 != :SYS_B_27)) AND (T28.C7 != :SYS_B_28))) ORDER BY :SYS_B_29 ASC T28 is Report form So the problem is that number of users try to fetch report simultaneously from the server. So what could be the solution for this? Thanks Regards, Manoj Jain _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Performnace Issue Due To Report Fetching
Are any of the bind variable referring to a user? a.g.bryan.14 On 7/17/09, manoj jain manoj.jain4...@gmail.com wrote: ** Hi Remedians, I need some suggesstions from your side related to performance issue. Our DBA team got one query due to this query performance issue occuring. *Query:* ** *High I/O query* * * *SELECT*T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C90013,C536870929,C536870931,C536870914,C536871018,C536870938,C65000,C536870952,C536871025,C536871038,C53690,C536870912,C7,C900040206,C901092014,C900110076,C536870987,C901170021 *FROM* T28 *WHERE* (((T28.C112 *LIKE* :SYS_B_00) *OR* ((T28.C112 *LIKE*:SYS_B_01) *OR* ((T28.C112 *LIKE* :SYS_B_02) *OR* ((T28.C112 *LIKE* :SYS_B_03) * OR* ((T28.C112 *LIKE* :SYS_B_04) *OR* ((T28.C112 *LIKE* :SYS_B_05) *OR * ((T28.C112 *LIKE* :SYS_B_06) *OR* (T28.C112 *LIKE* :SYS_B_07 *AND* (((T28.C536871018 :SYS_B_08) *AND* ((T28.C65000 *LIKE*:SYS_B_09) *OR* (T28.C65000 *LIKE* :SYS_B_10))) *AND* (T28.C536871019 *IS* * NULL*)) *AND* (((T28.C536870938 = :SYS_B_11) *AND* (T28.C536870938 = (:SYS_B_12 + ((:SYS_B_13 * :SYS_B_14) * :SYS_B_15 *OR*((T28.C900110022 = :SYS_B_16) *AND* (T28.C900110022 (:SYS_B_17 + ((:SYS_B_18 * :SYS_B_19) * :SYS_B_20)) *OR* (((T28.C536870984 = :SYS_B_21) *AND*(T28.C536871019 *IS* *NULL*)) *AND* ((T28.C900110022 = :SYS_B_22) *AND* (T28.C900110022 (:SYS_B_23 + ((:SYS_B_24 * :SYS_B_25) * :SYS_B_26)) *AND*(T28.C7 != :SYS_B_27)) *AND* (T28.C7 != :SYS_B_28))) *ORDER* *BY* :SYS_B_29 *ASC* *T28 is Report form* So the problem is that number of users try to fetch report simultaneously from the server. So what could be the solution for this? Thanks Regards, Manoj Jain _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Performnace Issue Due To Report Fetching
Get a reporting server and crystal enterprise that would be the ideal solution. Other that limit access to the reporting ser and limit query chunk size Sent from my Verizon Wireless BlackBerry -Original Message- From: B A a.g.bryan...@gmail.com Date: Sat, 18 Jul 2009 00:06:06 To: arslist@ARSLIST.ORG Subject: Re: Performnace Issue Due To Report Fetching Are any of the bind variable referring to a user? a.g.bryan.14 On 7/17/09, manoj jain manoj.jain4...@gmail.com wrote: ** Hi Remedians, I need some suggesstions from your side related to performance issue. Our DBA team got one query due to this query performance issue occuring. *Query:* ** *High I/O query* * * *SELECT*T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C90013,C536870929,C536870931,C536870914,C536871018,C536870938,C65000,C536870952,C536871025,C536871038,C53690,C536870912,C7,C900040206,C901092014,C900110076,C536870987,C901170021 *FROM* T28 *WHERE* (((T28.C112 *LIKE* :SYS_B_00) *OR* ((T28.C112 *LIKE*:SYS_B_01) *OR* ((T28.C112 *LIKE* :SYS_B_02) *OR* ((T28.C112 *LIKE* :SYS_B_03) * OR* ((T28.C112 *LIKE* :SYS_B_04) *OR* ((T28.C112 *LIKE* :SYS_B_05) *OR * ((T28.C112 *LIKE* :SYS_B_06) *OR* (T28.C112 *LIKE* :SYS_B_07 *AND* (((T28.C536871018 :SYS_B_08) *AND* ((T28.C65000 *LIKE*:SYS_B_09) *OR* (T28.C65000 *LIKE* :SYS_B_10))) *AND* (T28.C536871019 *IS* * NULL*)) *AND* (((T28.C536870938 = :SYS_B_11) *AND* (T28.C536870938 = (:SYS_B_12 + ((:SYS_B_13 * :SYS_B_14) * :SYS_B_15 *OR*((T28.C900110022 = :SYS_B_16) *AND* (T28.C900110022 (:SYS_B_17 + ((:SYS_B_18 * :SYS_B_19) * :SYS_B_20)) *OR* (((T28.C536870984 = :SYS_B_21) *AND*(T28.C536871019 *IS* *NULL*)) *AND* ((T28.C900110022 = :SYS_B_22) *AND* (T28.C900110022 (:SYS_B_23 + ((:SYS_B_24 * :SYS_B_25) * :SYS_B_26)) *AND*(T28.C7 != :SYS_B_27)) *AND* (T28.C7 != :SYS_B_28))) *ORDER* *BY* :SYS_B_29 *ASC* *T28 is Report form* So the problem is that number of users try to fetch report simultaneously from the server. So what could be the solution for this? Thanks Regards, Manoj Jain _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
