We are a company using Cache for Windows (x86-64) 2007.1.3 (Build 607) and have been experiencing slow database performance, especially when reading, due to the large database size of 19Gb. We're using a custom made application build with the .NET framework 2 via an ODBC, and noticed that when making queries through particularly large tables, even though having proper indices where applicable, the performance is quite lacking.
For example to make a select count query on a table having 23,267,429 records it took 237 seconds, 152 sec, 172 sec on three different occasions (e.g. SELECT COUNT(ID) FROM MY_TABLE). Is that "normal" or can we make it in 10 seconds or less?
On the same table if we had to run another query using an index built on two fields, and if we tried to look for a non-existing record, it would take 1.3 sec.
If using the same table if we had to run another query using this same index, and if we tried to look for an existing record, it would take 1.1 sec.
On a relatively smaller table it would take 0.35 seconds
The first question is whether there is a way to make the database reading possibly faster . We have tried playing around with the memory by allocating more RAM but without any positive results. Using the server Microsoft's performance montior, it is evident that during peak load there are very high percentage reads on the disks going on whilst the CPU is relaxed and the writing is minimum. There are times when the server reading is fixed at 100% usage for over half an hour.
Secondly, as part of the solution, we are considering changing the RAID5 to RAID10 with four disks of 300G SAS running 15000rpm. Will that help to improve the performance? If yes, what cluster size of disks and what other technical setup do you recommend please to improve, hopefully in a drastic manner the performance?
Further details about our scenario
x3400, 2 CPUs Xeon Quad Core @ 1.60GHz
8 Gigs RAM
RAID 5 setup with five 146.8GB 3.5in 15K HS U320 SAS HDD
1 Gigabit Ethernet
Windows 2003 server, having hardware dedicate to this database only.