Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Show us the DDL for those tables and any indexes you have defined on them. –  Nick Chammas Apr 14 '12 at 1:23
1  
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. –  Nick Chammas Apr 14 '12 at 2:15
add comment

1 Answer

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.

share|improve this answer
    
Thanks. Gave it a try and works. Also added indexes, which helped some. Still takes time but not near as long. –  elbert Apr 14 '12 at 3:15
    
As @Nick pointed, you should do this, only if you really need the data for all those 20K patients at the same time. –  ypercube Apr 14 '12 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 '12 at 15:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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