0

My application is simply copying table from local database to distributed database server. The data to be copied is quite big about thousand records, and finished the jobs for about 30s (not sure how long exactly it takes). This application is run on scheduled or user input (force method).

The database server is using JHA (Jack Henry and Association).

The problem is, sometimes the database server suffer very high cpu and memory usage, this causes some other applications that using same database server failed to fetch data from the database. After doing some trace, we found out that the operation that comes from my application is using very high cpu and memory usage.

My solution is when copying the data, im copying 100 record at one time, then my put my application to sleep for 100ms, then copying next 100 record, and so on until all data is copied. Is this enough? Is my solution will have significant effect? What is your suggestion?

FYI, my application is run from several computer, so several client computer and one database server. And i really-really need your helps here :)

Hendra
  • 43
  • 2
  • 10
  • reasoning about performance is hard, without some basic stripped down source code it becomes undoable... Do you use transactions? Is this lan/wan/internet? what technology stack? what is in one record? How many fields, what type? – rene Aug 17 '12 at 10:53
  • I think not, so far i read the source code (i do not build it from scratch, it is an existing application, and the initial creator is resigned long time ago) it is just a basic database connection without any transaction, FYI this application is using VB.Net 2.0. The record is containing financial data from customer, it have about 20 fields, it have varchar, int, and numeric type. My idea is to give the data base server some times to do other things (that is what the sleep for), so the database server can do operation from other application. It is sufficient? What is you suggestion? – Hendra Aug 18 '12 at 17:28
  • I would suggest you stop guessing. I wouldn't bother how busy the other server is. That is their problem. – rene Aug 18 '12 at 18:12
  • sorry, but i'am not at the office till next week, so i cant sure how many fields and type in there. will check it out once im back to my office. well, my task is to fix the performance issues at the database server that comes from my application doing much query, where other application using same database server so they will have affect from it too. so it is more or less my problem now, at least that is my task – Hendra Aug 21 '12 at 06:18

1 Answers1

0

Although this link http://msdn.microsoft.com/en-us/library/ff649152 is retired it's general process guidance is still usefull for nailing performance issues.

Also invest in some tooling. VS comes with some performance analyzer tools, the SDK has some and you can use PerfView http://www.microsoft.com/en-us/download/details.aspx?id=28567. You als need to look at your DB Platform. What is going on there? Maybe a index missing? or an unused index?

  1. What is your performance goal?
  2. Measure, have a baseline,
  3. investigate, generate options,
  4. implement one (just one)
  5. measure again.
  6. Gained anything? No, rollback 3, pick the next option or do 2 if out of options
  7. Reached the performance goal? No, goto 3.
rene
  • 41,474
  • 78
  • 114
  • 152
  • the article contain great information for my reference, however my application itself does not the problem here. my application is running at the client computer, it doing much query to the database server. the usage from my application at the client computer is low, but the database server having high cpu and memory usage because it performing queries from my application, did you get the image? i'll do your instruction, and hope i can find out the problem and fix it – Hendra Aug 21 '12 at 06:26