"Performance Issues of Health Care System using SQL server - PDF"
(IJCSIS) International Journal of Computer Science and Information Security, Vol. 8, No. 2, 2010 Performance Issues of Health Care System using SQL server Narendra Kohli Nishchal K. Verma Electrical Engineering Department Electrical Engineering Department Indian Institute of Technology Indian Institute of Technology Kanpur, India Kanpur, India firstname.lastname@example.org email@example.com Abstract—: In this paper, a smart card based on line health care through card reader. Designing of the proper databases and system and its performance issues using SQL server are uses of different indexing techniques in SQL server 2005 will proposed. To provide a good quality of treatment in the hospital, help for fast retrieval of patient data. it is required to integrate all the hospitals of country via internet. A Smart Card with 10 digits unique registration no. with his some personal information is issued to patient. After getting II. Literature review registration in any hospital of the hospital network, patient has to The need for automation systems in hospitals gains more go for checkup with smart card only. All the patient information importance . A patient may be registered in any hospital of i.e. personal, doctor prescriptions, test reports etc. will be stored the networking of the hospitals. As per the requirement it is in the database of the local server of the hospital and time to time uploaded to the centralized server. On the basis of unique important to share the patient information between different registration no., all the patient information can be retrieved from hospitals without ignoring privacy constraints & other related the database of the centralized server. Smart card based online information.  Proposed the use of smart card to store the health care system application has been designed as front end basic information of the patient. Nowadays different types of .Net and back end in SQL server. The block size or page size smart cards are exiting in market  . These cards have being used during the database creation is playing very proven to be convenient tokens for identification and important role in performance tuning. It is very important to authentication in day to day activities . As per the decide the proper block size before database design. You cannot requirement, different types of smart cards can be used to store change the block size once you have created the database. Re- the patient information.   explains about telemedicine creating the database again is a very costly affair. and PACS. E-health is producing a great impact in the field of Keywords- hospital, patient, smart card, SQL server information distribution of the health services to the hospitals 2005 and public  . Query optimization of SQL server has been discussed in  . I. INTRODUCTION III. PROBLEM FORMULATION Automation & networking of the hospitals are the necessity of the society. The purpose of the same is to provide the better 3.1 If a patient is registered in one hospital and services for patients and it will increase the working efficiency integration and retrieval of patient information is not possible of the hospital system. Main idea for health care system is to in the hospital system then while taking the consultation with obtain, store, analysis or process and uses of patient doctor, patient can easily forget to explain his previous information (patient, doctor, hospitals, laboratory tests etc.). At registration counter in a hospital, administrator will generate a treatment. In case of which incorrect prescriptions may be 10 digit unique patient-Id. Basic information’s i.e. name, applied. Keeping in mind an intelligent system i.e. smart card address, phone no. etc. with unique patient id will be stored on based online health care system has been proposed. The the smart card and issued to the patient. As per the patient id, proposed system is given in Fig. 1. The servers for the patient related information i.e. doctor diagnosis, test reports, hospitals with high technical configuration are required. All MRI, CT-scan images etc will be stored in the databases of the the servers of the hospitals are connected with one centralized hospital server and time to time uploaded to the centralized server of the hospital through internet. A very high bandwidth server. In future this information will be useful for doctors to dedicated internet lease line has been proposed to use for the diagnose the illness and give the important suggestions to the system. A smart card reader / writer unit has been attached to patients for their health. While visiting to the hospital for each computer of this hospital system network. The proposed treatment, patient has to carry only smart card  . health care system has been loaded to all the servers of all the Administrator of the hospital or doctor will use the smart card hospitals. The patient smart card stores some important 279 http://sites.google.com/site/ijcsis/ ISSN 1947-5500 (IJCSIS) International Journal of Computer Science and Information Security, Vol. 8, No. 2, 2010 information like unique patient id, name, sex, date of birth, IV Methodology blood group etc. As per the patient-id, patient details like treatment prescriptions, test reports, images like MRI, CT-scan Approach for above issues: etc. have been stored in the database of the hospital server. On the basis of stored details of the patient, doctor can prescribe 1. The patient will provide basic information through smart the proper medicine. SQL server tuning has been used while card (having 10 digit patient id and personal information) at designing the application. registration counter where it will be uploaded to server. 2. In smart card we are storing patient id and personal information like name, address, DOB etc. of a person. As per the size of smart card we can store the information of a person. 3. Patient’s files may be one of the two forms image or PDF. Images are stored separately in image tables and PDF in PDF table .These tables contain an Image ID or PDF ID and their description. Image ID and PDF ID are stored in their respective tables as foreign keys that may be in patient doctor or Patient Lab or in PatientRoom Tables. 4. Since we are storing all the patient related information’s on a centralized database server. So movements of files is not required .The required files for a particular patient & their lab test report & images, prescriptions, scan document & diagnosis reports can be retrieved from centralized database Fig. 1: Smart card based health care system server. Accessing the information’s related to patient will be controlled by various level of access control and it will help us to prevent the unauthorized access from the data base. 3.2 To increase the performance of the application of online health care system, the following issues & 5. As we have normalized our database we have removes all technical issues will be taken: possible redundancies 3.2.1 Issues: 6. We have covered optimization at application design level, database design level, memory utilization & optimization of • Design an application that can serve maximum queries for accessing the data base. Few points about the number of hospital and provide a platform for optimization are as follows: their interaction. • Application is studied well and requirements are • A system generated patient id that is unique for identified. These requirements are categorized on maximum possible time interval. object level and respective tables to store that • Store patient information optimally. information are created. After that database is • Speed up the DML (insert, update, delete) normalized up to BCNF (Boyce Codd Normal Form) operations. level and 14 tables are created. • Store patient information for maximum possible • Size of attributes (their respective data types) is time. further minimized in order to compact record size, So that maximum number of records can be stored in a single data page. Select queries are designed to 3.2.2 Technical issues: handle all possible search criteria. • Indexes are created to speed up above read access. • Normalization of tables. First data base ids well studied to find out what • Maximum utilization of memory. possible values different attributes will take and what • Imposing necessary constraints on tables. will be the size of tables. Considering both the things • Selecting best possible structure for queries. Hash, B-tree (clustered and non clustered) and • Tuning and optimizing queries. Bitmap indexes are proposed. • Optimal selection of join order and join algorithm. • Order of attributes in composite indexes are studied and justified. • Every hospital will have day to day patient information locally to improve the performance 280 http://sites.google.com/site/ijcsis/ ISSN 1947-5500 (IJCSIS) International Journal of Computer Science and Information Security, Vol. 8, No. 2, 2010 particularly in case of frequent insert, update • PatientLab stores test report of patients. operation. • PatientRoom Stores Room id’s, Daily charges And other • In a multi table join queries, we have studied the attributes of room where Patient is admitted nature of the table & depending on the characteristic of the table their order will be pre estimated. SQL The following analysis is based on query and stored queries Hints can also be given to optimizer to follow procedure given below: that order to achieve the optimal performance. The query selects Patient details from four different tables by • Proper join algorithms are justified in respective applying inner joins. cases and hints are given to optimizer. Query: • Selecting the most optimal execution path by the SELECT Patient1.PatName, Patient1.Address, optimizer is the time consuming process. By given Patient1.ContactNo, Patient1.Sex, Patient1.Occupation, the Hints we can reduce the time taken by the Patient1.Guardian, Patient1.RegNo,Patient1.ReferredBy, optimizer up to some level Patient1.BloodGroup, Patient1.PatientHistory, Patient1.Disease, Patient1.DiseaseCatageory, • To improve the DML operation by the hospital we try Patient1.EntryDate,PatientRoomIPD.Hospital_Id, to keep the data initially at the local server. This PatientRoomIPD.Room_Id, PatientRoomIPD.StartDate, scheme reduces load from centralized server. PatientRoomIPD.EndDate,PatientRoomIPD.Status, PatientRoomIPD.TotalCharge, PatientDoctor.DoctorId, • Every day a particular time is selected to update PatientDoctor.DoctorName, PatientDoctor.StartDate AS central server by collecting information from local TreatmentStartDate,PatientDoctor.EndDate AS servers automatically. The operation to refresh the TreatmentEndDate, PatientDoctor.No_Of_Visits, centralized server may be made more frequent if PatientDoctor.Charges AS TreatmentCharge, required. PatientLab.LabNo,PatientLab.TestNo, PatientLab.TestDate,PatientLab.Unit • Transaction log is permanently off at centralized FROM Patient1 server as we don’t need any log for recovery. INNER JOIN Because the all information’s are at the local server & PatientRoomIPD ON Patient1.PatId = PatientRoomIPD.PatID can be reproduced easily. INNER JOIN • All primary key, foreign key constraints are removed PatientDoctor ON Patient1.PatId = PatientDoctor.PatId from centralized server as those constraints are INNER JOIN already checked at local servers. So there is not need PatientLab ON Patient1.PatId = PatientLab.PatId to revalidate the information which is validated. It will improve the data loading performance at the where Patient1.PatId=@Id; central server. On the hardware configuration: Intel® Duel Core 2.8GHz 512 • For updating centralized server we will recommend RAM to use bulk copy and bulk insert schemes Table 1: results on front end and query analyzer V. Findings On Asp.net Search Page On Query Analyzer (Front-End) Iterati Execution time in Executi Execution Execution 5.1 Simulation of query optimization for performance tuning: on ms using Stored on time time in ms time in ms Procedure in ms using using To identify the bottleneck and performance related issues we using Stored query Query Procedure have done the following case study using different methods 1 .719 .771 .827 .740 and analyze the query and their execution path. In our 2 .750 .756 .827 .704 database out of 14 tables, 4 tables stores patient related 3 .750 .722 .720 .716 information’s and every table has approximately 14 lac 4 .813 .733 .766 .843 records in SQL server to simulate. 5 .719 .781 .810 .797 6 .844 .727 .784 .767 • Patient Stores registration information of patient 7 .672 .697 .750 .803 8 .765 .755 .780 .767 • PatientDoctor Stores Doctor related information like Mean .754 .743 .783 .767 disease and others 281 http://sites.google.com/site/ijcsis/ ISSN 1947-5500 (IJCSIS) International Journal of Computer Science and Information Security, Vol. 8, No. 2, 2010 Since we are taking different ID’s for different iterations that’s why stored procedures are complied each time when we call the procedure. The optimizer and analyzer check the SQL Table 2: results on modified table row sizes statement line by line and character by character and it treats the query differently every time when we give different IDs. On Asp.net Search On Query Analyzer As per the table 1 stored procedures taking much time than a Page query because we are unable to take the benefits of (Front end) Iterati Executio Executi Execution Execution time in precompiled stored procedure (they are compiled every time on n time in on time time in ms ms using Query when they are called in our case because it has more ms using in ms using intelligence to reuse the query) Stored using Stored Procedur Query Procedure e 5.2 Optimization based on memory management: 1 .562 .625 .563 .610 2 .609 .625 .610 .580 SQL Server 2005 stores data in a special structure called data 3 .578 .593 .610 .593 pages that are 8Kb (8192 bytes) in size. Some space on the 4 .578 .625 .594 .610 data pages is used to store system information, which leaves 5 .609 .609 .593 .597 6 .563 .625 .594 .577 8060 bytes to store user's data. So, if the table's row size is 7 .531 .547 .594 .593 4040 bytes, then only one row will be placed on each data 8 .578 .610 .593 .577 page. If you can decrease the row size to 4030 bytes, you can Averag .576 .607 .593 .592 store two rows within a single page. The less space used, the e smaller the table and index, and the less the I/O SQL Server has to perform when reading data pages from disk. So, one After optimization of page and block sizes in the databases should design the tables in such a way as to maximize the average execution time is reduced as per table 2. number of rows that can fit into one data page and you should specify the narrowest columns you can. The narrower the Time in stored procedure and query: columns, the less data that is stored, and the faster SQL Server are able to read and write data. On the above discussion it is 0.754 ms (Old value), 0.576 ms (new value) clear that row size of a table should be some devisor of 8060 0.743 ms (Old value), 0.607 ms (new value) to maximum utilizes memory and speed up read/write from database. 5.3 Effect of indexing: So table size should be one of the following magic numbers An index is a database objects that, when a table is created, 1 2 4 5 10 13 20 can provide faster access path to data and can facilitate faster 26 31 52 62 65 124 query execution. 130 155 260 310 403 620 a) After indexing on PatID of PatientDoctor table, the 806 1612 2015 4030 8060 bytes results are as table 3. So the modification of table row size are based on above Table 3: Indexing on PatId of PatientDoctor table magic number On Asp.net Search On Query Analyzer New Databases: Page(front end) Iterati Execution Executio Executio Execution time We have executed query and stored procedure from front end on time in ms n time in n time in in ms using and query analyzer by taking same Patient ID at random on using Stored ms using ms using Query Procedure query Stored new data base after modification of table row sizes. Results Procedur are as table 2 e 2 .297 .344 .326 .330 3 .344 .390 .390 .300 4 .297 .390 .356 .330 5 .313 .328 .300 .327 6 .266 .359 .374 .343 7 .344 .328 .330 .313 8 .359 .344 .373 .357 9 .328 .375 .326 .330 Averag .318 .357 .347 .329 e(ms) 282 http://sites.google.com/site/ijcsis/ ISSN 1947-5500 (IJCSIS) International Journal of Computer Science and Information Security, Vol. 8, No. 2, 2010 b) After indexing on PatID of PatientDoctor and Table 6: Indexing on PatId of PatientDoctor & PatientLab & PatientRoom table without memory optimization. PatientLab table, results are as in table 4. On Asp.net Search On Query Analyzer Page Iteration Execution Execution Execution Execution time Table 4: Indexing on PatID of PatientDoctor and PatientLab table time in ms time in ms time in ms in ms using using using using Query Stored query Stored On Asp.net Search On Query Analyzer Procedure Procedure Page 1 .125 .110 .076 .110 Iteratio Execution Executio Execution Execution 2 .094 .094 .110 .140 n time in ms n time in time in ms time in ms 3 .110 .078 .123 .093 using ms using using using Query 4 .078 .157 .047 .144 Stored query Stored 5 .110 .063 .120 .047 Procedure Procedure 1 .265 .312 .250 .250 6 .094 .094 .060 .153 2 .297 .297 .297 .297 7 .093 .094 .110 .080 3 .281 ,281 .314 .283 8 .079 .031 .140 .126 4 .312 .250 .296 .310 Average .098 .090 .098 .112 5 .297 .297 .250 .326 6 .297 .250 .250 .280 V. Conclusions: 7 .329 .297 .280 .250 8 .281 .297 .280 .250 • Stored procedure using front end has better Average .295 .286 .277 .280 understanding about the SQL statement. If we are changing only some value for a given ID then it understand that the SQL statement is exactly the c) After indexing on PatID of PatientDoctor and same as the previously executed statement with PatientLab and Patient Room table with proper memory different value only. So the optimizer by passes the optimization, results are as in table 5. parsing, query optimization and generation of new execution path, and used the already available path Table 5: Indexing on PatId of PatientDoctor & PatientLab & PatientRoom for the next statement with different ID. This table conclude that instead of using anonymous SQL statement we should recommend to use the stored On Asp.net Search On Query Analyzer procedure which gets stored in the database in the Page compiles format to avoid re-compilation every time Iteratio Execution Execution Execution Execution as in case of simple un-name SQL statement or n time in ms time in ms time in ms time in ms using using using using anonymous SQL statement. As it’s stored Stored query Stored query permanently in the database so it can be cached in the Procedure Procedure memory and can be shared by several applications 1 .000 .047 .077 .000 and also improves the reusability. 2 .000 .000 .080 .076 3 .031 .031 .047 .077 4 .031 .063 .064 .063 • The block size or page size being used during the 5 .031 .000 .077 .047 database creation is playing very important role in 6 .016 .000 .034 .060 performance tuning. If we know the average row size 7 .000 .000 .140 .060 for a table that before going to create the database we 8 .063 .031 .047 .013 should calculate the block sizes in such a way that Average .019 .017 .070 .049 maximum number of records can be stored in one page. It will drastically reduce the number of I/Os means less number of disk activity will be performed d) After indexing on PatID of PatientDoctor and to fetch the data. It is very important to decide the PatientLab and PatientRoom on old data base without memory proper block size before database design. You cannot optimization, we are getting poor results. Results are as in change the block size once you have created the table 6 database. Re-creating the database again is a very costly affair. • After creating indexes on the four tables query execution and stored procedure execution is taking less time than before. This is a good result. But this conclusion is only based on the B-tree index which is 283 http://sites.google.com/site/ijcsis/ ISSN 1947-5500 (IJCSIS) International Journal of Computer Science and Information Security, Vol. 8, No. 2, 2010 better for the column which has very high cardinality  J.R.Campbell and R. Stoupa. The patient, the provider, the processor: information management in ambulatory care. proceeding SCAMC,ed. & uniform distribution of the data. We have further pp. 930-940 ( IEEE Computer Society Press 1990) explored the possibilities of using different kind of  J.R.Scherrer. the Hospital information system- integrated patient indexing techniques in case if the cardinality is low, records. Vol 48. Elsevier Science Ireland Ltd. 1995. data is not uniformly distributed & indexing column  Jinman Kim, David Dagan Feng, Tom Weidong Cai, Stefan Eberl. Integrated Multimedia Medical Data Agent in E-Health. Pan-Sydney is monotonically increasing number. In these cases B- Area Workshop on visual Information Processing .Sydney, Australia. tree indexes will reduce the performance instead of 2001 improvements .In my other work I have proposed to  L.L.weed. knowledge coupling: New premises and new tools for incorporate hash, bitmap & Btree indexing techniques medical care and education .Springer Verlog. New York. 1991.  SQL server tutorials:sqlserverpedia.com/wiki/SQL-Server-Tutorial in database engine so that the optimizer will follow  SQL server tutorial: http:// www.quackit.com/sql_server/tutorial/. the most optimal query execution path. ACKNOWLEDGMENT We acknowledge to Dr. Arnab Bhatacharya, Er. Kaleeq Ahmed IITkanpur for their advices in the preparation of paper. AUTHORS PROFILE REFERENCES Narendra Kohli is working as Assistant Professor in the computer science and engineering deptt. , HBTI Kanpur. He is doing research on Telemedicine and PACS at IIT Kanpur.  Smart Card Handbook.  Advance Card systems: http://www.acs.com.hk/index.php.  http://www.parivahan.nic.in. Nishchal K. Verma is working as Assistant professor in Electrical  Narendra kohli, Nishchal K. Verma. Performance issues of smart card engineering deptt., IIT Kanpur. His research interests are Machine based online health care automation system. Proceedings of the 1st Learning, Biometrics, GMM, HMM, Fuzzy Systems, Clustering international conference on Signals, systems & automation. 28th -29th Algorithms, Coloe Segmentaion,Video Image sequence recognition. December. India. 2009.  Eugene Lockett, Sungkyu park,Gueng cheng Jiang,Mike riddle. Security aspects of smart cards-term project CS 574 Fall 2003 San Diego state university, Nov.3, 2003. 284 http://sites.google.com/site/ijcsis/ ISSN 1947-5500