I run an app on a citrix box which is a Dual Xeon 3.0 w/ 3 gigs and a 1 gb network card; it talks to a similiarly configured SQL server, that only runs SQL. I am running Windows 2000 server on both boxes.
It loads a patient with a lot of activity in 30 seconds. Normal patients, without as much activity load in 8 seconds. Part of the time is spent rendering a graphical
dental chart (about 2 seconds) and it loads the roughly data twice from sql - once to draw the chart and then again to display the text.
Anyway, if I throw an opteron or athalon at this, does anyone think I can approach speeding this up by a factor of 2?
Other than hardware, I am going to investigate optimizing the sql database. I called the application company and they said, "yeah, that patient has a lot of data".
I am thinking, man, sql should scale beyond a 2 gig database for 20,000 patients, one of whom has maybe 1000 entries to call up in their account!
When I write a query to get everything the database holds on the patient, I get it all back in less than a second. But when you look at sql profiler as the app gets it back, it is sending about 500 individual select statements. I was told, using many "small" select statements prevents the database from slowing down for other users but may make an individual request, like a patient with a lot of data to pull slower.