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
>>> [email protected] 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:[email protected]] On Behalf Of manoj jain Sent: Friday, July 17, 2009 6:57 AM To: [email protected] 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,C900100003,C5368 70929,C536870931,C536870914,C536871018,C536870938,C650000000,C536870952, C536871025,C536871038,C536900000,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.C650000000 LIKE :"SYS_B_09") OR (T28.C650000000 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: [email protected] ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
