The database I am working with is part of a data warehouse made available for research teams, so there are some limits to what I can do with adding indexes to tables. In particular, TIUDocument_8925_New is a view to some tables, and we don't have access to those tables (except via the view). The join of TIUDocument_8925 and VHAEnterpriseStdTitle_Filter is in the view's definition, so that join is apparently executed whenever a query references TIUDocument_8925_New. We have little or no leverage in asking for indexes to be changed on those underlying tables. I can change the indexes on the PatientMap_2Patients and ICD tables (and the temp tables, of course) though, since we created them.

Adding the MAXDOP hint had a big impact. The original query that ran for an hour without returning results finished in about 12 minutes with the MAXDOP hint. When I used the hint on queries that used one or the other of the temp tables, they ran faster, especially the one that used the diagnoses temp table. Using only the addenda temp table with MAXDOP in the full query took 4 minutes, and using only the diagnoses temp table with MAXDOP took 33 seconds. Using both temp tables allowed the query to run in about 3 seconds. They query lans for these queries are attached.

It takes about 25 seconds to create each of the two temp tables, and 3 seconds for the query that uses them, but if I execute the temp table creation statements and the query together, the whole thing takes about 1:25.

I don't know very much about SQL tuning, so I need to ask you to explain how to do some of the things you suggested. These are:

- align the index on one of the #tmps to the rest of the data's indexes [Could you give an example of this? I'm not sure if I understand how to do it.]

- include TIUAddendumSID in the index for #DocAndAdIDs [does this mean defining the index on both columns or mentioning TIUAddendumSID in an INCLUDE clause?]

- get a statistics update with fullscan going

Thanks,

Mike