5

I'm pretty new at all this so be gentle :).

I have large medical DB where I need to extract data for patients based on selected doctor(s).

Right now I build a patient list by

SELECT patID 
FROM appointments 
WHERE docID IN ('docid1', 'docid2', ..... , 'docidn') 

and once i get my patient list I use it when extracting data from a whole bunch of patient related tables.

Depending on my initial doctors list I can end up with patient lists that are 20,000+. The total # of patients in the system is close to 1,000,000.

I then take my patient list and start getting data from other tables, more or less like this

Select * 
FROM xrays 
WHERE patID IN ('patid1', 'patid2', 'paid3',....., 'patidn' ) 

where I build the list of patids based on my the list I have from the first part.

As you can imagine, with a patient list of 20,000, the above query is taking a long time.

Any suggestions on a better way to do this?

2
  • 1
    Show us the DDL for those tables and any indexes you have defined on them. Apr 14, 2012 at 1:23
  • 2
    Also, do you really need to select all this data at once? If you're selecting it and then filtering it up in your application, try instead filtering the data as much as possible directly in the database. So, if you know you only need the information for a couple patients or doctors, ask only for that data upfront. Apr 14, 2012 at 2:15

1 Answer 1

8

This is what Joins are for. You don't have to get a list of patients ids and then send them back to the SQL engine. You can combine the two queries into one, with JOIN:

SELECT x.* 
FROM xrays AS x
  JOIN appointments AS a
    ON a.patID = x.patID 
WHERE a.docID IN ('docid1', 'docid2', ..... , 'docidn') 
ORDER BY x.patID                   --- optional so your application gets
                                   --- the data of patients, ordered.

Other things to check is the indexes you have on the tables. As a rule of thumb, at least all columns used in joins, like your docID, patID, should have an index.

3
  • Thanks. Gave it a try and works. Also added indexes, which helped some. Still takes time but not near as long.
    – elbert
    Apr 14, 2012 at 3:15
  • 1
    As @Nick pointed, you should do this, only if you really need the data for all those 20K patients at the same time. Apr 14, 2012 at 11:26
  • Yes. For this application I am doing a backup of data where the data is a subset based on the patients extracted from appointments where the filters are doctor(s) and/or appointment date range.
    – elbert
    Apr 14, 2012 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.