Approach to do performance testing on DB
My client has 100 branches. Application has installed in local branch systems. He has taken the data from 100 branches through Core FTP and put in Core FTP server located in Central office. He download that data from Core FTP server and send to Central DB. After that he generate different types of reports taking data from database.
Database is not sending the data to the reports and it takes lot of time to generate reports. He is using 20GB Database
1. What is the approach to do performance testing on this?
2. Which is best tool to test this type of problems?
Thanks in advance.
Start with analyzing the queries which generate the reports. SQL Profiler for Microsoft, AWR for ORACLE, with similar solutions for DB2 and MYSQL. Make sure that the structure of the database allows for efficient processing of the queries. Are they index supported? Do you need to partition an index on date range or customer ID to allow for more efficient processing of the data where a much smaller index can be scanned, instread of one from beginning to end. In short, the smaller the amount of work your disk subsystem has to do (from an IO perspective) then the faster the queries can be processed in order to satisfy your queries.
Next up, speeding up throughput: Do you have enough spindles for your data which is being pulled? Do you have any arbitration on the read/write heads which slows grabbing the data? Have you considered a move to Static RAM drives for your index partitions (slower on write typically but monstrously fast on reads....killing even the fastest of RAID hard drives on I/O operations per second.)
Make sure it works at the highest level of efficiency for a single user before you consider testing for multiple. Also, if you can leverage off hours generation of data instead of on demand then you can potentially hit the database at it's lowest utilization point. For example, schedule the queries to kick off sequentially after the data has been uploaded and database backups complete. Have the results mailed to the user(s) who need them instead of having each user submit essentially the same reqport request manually.
Replace ineffective offshore contracts, LoadRunnerByTheHour
. Starting @ $19.95/hr USD.
Put us to the test, skilled expertise is less expensive than you might imagine.
Twitter: @LoadRunnerBTH @PerfBytes
Thanks for your reply. Your post had solved my problem.