Sam the change to SQL may help. But more importantly the normalization of the database. Database normalization rules demand that the database exist in 3rd normal form (3NF). This means that there is no duplicated data between tables. This can be difficult to do especially where the records must maintain an historical integrity.
If AC has ALL THE CODES (ICD-9). There are nearly 20,000 of the codes. The average doc probably does not use more than 200. The average patient health record does not contain more than 50. However a bottleneck in the design is that everytime you open a patient record it has to match those 50 records against the 20,000 codes...every time. A more efficient design would be for AC to only have the 200-300 most utilized codes (which are specialty grouped). This way the two matching sets are smaller.
It is easier to find 50 from 200 than it is to find 50 from 20,000.
Last edited by gkfahnbulleh; 05/21/2008 11:52 PM.
