Wondering if there are other ways in addition to adding indicies to make following sql query more efficient

Here is the query:

SELECT DISTINCT patientid.acct
FROM   patientid,
       doc_table
WHERE  patientid.acct = doc_table.acct
       AND patientType IN ( 'I', '1', 'T' )
       AND fid IN ( '023' )
       AND ( dischargedate IS NULL
              OR dischargedate = ''
              OR dischargedate < '19000101' )
       AND dbcreate_date > DATEADD(hh, -24, GETDATE());  

We will be adding indicies for 开发者_开发知识库patientType, fid dischargedate and dbcreate_date. But I wondering if the query itself could be written in a different way to make it more efficient.

Thank you,

Without more info it's hard to be prescriptive, but in general:

1 - Are you sure you need to DISTINCT? That can be an expensive operation on larger data sets. Run the query without it to see if the results differ. If they do you may want to change the structure to eliminate duplicates.

2 - Put your most restrictive condition in your WHERE clause first, then in descending order of restriction. For example, if your patienttype filter drops the results to 50%, but the fid filter drops it to 20%, use the fid filter first.

3 - for indexes, make sure your JOIN keys are indexed: patientid.acct and doc_table.acct this will probably have the biggest impact on performance.

As a side not, use explicit JOIN syntax, it's much easier to read and maintain, especially on longer queries. A revised version would be:

SELECT DISTINCT patientid.acct
FROM   patientid 
INNER JOIN  doc_table
    ON patientid.acct = doc_table.acct
WHERE patientType IN ( 'I', '1', 'T' )
...

Use WHERE EXISTS instead of a JOIN and getting rid of the duplicates with DISTINCT

One thing you should consider: Fix the values for the column dischargedate and select one value which says null or '' or < '19000101' so you can get rid of the or: a) it costs time and b) a database shouldn't be a mess.