Performance Issues of Health Care System using SQL server - PDF

Document Sample
Performance Issues of Health Care System using SQL server - PDF Powered By Docstoc
					                                                                  (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
                     nkohli@iitk.ac.in                                                             nishchal@iitk.ac.in


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 [4]. 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. [1] 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 [2] [3]. 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 [5]. 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. [6] [7] 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 [8] [9]. Query optimization of SQL server has been
                                                                               discussed in [10] [11].

                        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 [1] [2].                       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                       [6]    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                       [7]    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                         [8]    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                           [9]    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.
                                                                                    [10]   SQL server tutorials:sqlserverpedia.com/wiki/SQL-Server-Tutorial
        in database engine so that the optimizer will follow                        [11]   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.
[1]   Smart Card Handbook.
[2]   Advance Card systems: http://www.acs.com.hk/index.php.
[3]   http://www.parivahan.nic.in.                                                  Nishchal K. Verma is working as Assistant professor in Electrical
[4]   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.
[5]   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

				
DOCUMENT INFO