elabuwa Posted March 4, 2011 Share Posted March 4, 2011 Hi guys, This is the scenario. My app runs fine. The number of transactions entered for a day in my client's org is huge. Say min of 200 - 300 receipts, payments etc. However, the server tends to be slowed down when we are calling reports with a large date range, possibly coz the server has to go through each every record. Even though i have written the sql command at the best way possible to minimize processing time, some reports take up to 20 mins. The server is an avg computer with dual core 1.6 ghz ( i think), 1 gb ram, 320 hdd. It is an assembled avg computer used as a server. At the begining all was fine coz the number of records were small. Now the database has expanded up 30000- 35000 records in one table, due to the large number of records. I need a solution to minimize reporting time (possible upgrade may solve the case), but when reports are called, other users of the system cannot even enter records because the server is busy generating the report. What is the best way? I was thinking of using two servers 1. Data storage - where all the data are stored 2. Report server - all data from data storage server is copied to the report server @ a predetermined interval, possible every 30 mins. The users who need reports, can log in to the report server and generate reports. What are your thoughts? I am opened for any type of suggestion.. Your help is much appreciated. Cheers The budget for the client is not too high, so buying an actual server could be impracticle. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted March 4, 2011 Share Posted March 4, 2011 IMHO... wrong approach.... My app runs fine. according to the scenario that you described your application is not running fine... 30,000 records shouldn't be causing the problems that you mention neither your "server"... personally I will focus in: - Review your Data Model design (bad design could be lead to inefficient SQL's among other things) - Review your Table/Views/etc design - Review your Index strategy and selectivity - Isolate and test your problematic SQL's (Explain/Profiling) your 20 minutes report issue is based on some of these factors most likely imho. Quote Link to comment Share on other sites More sharing options...
elabuwa Posted March 7, 2011 Author Share Posted March 7, 2011 Thanks Miko. I will reviewing the thingis you mentioned. Quote Link to comment Share on other sites More sharing options...
ignace Posted March 7, 2011 Share Posted March 7, 2011 Start here: - Review your Index strategy and selectivity It takes your server 20 mins because it can't find a valid index and thus creates a temporary table which it joins with all other temporary tables and creates you a nice cartesian product to process. Post your table/queries here and we can give you some advice on which indexes you need. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.