September 19, 2006 at 12:51 pm
I have this query that takes 36 seconds to run. If I break it into 2 by creating a temp table and then joining to the rest of the tables it finishes in 6 seconds.
Any ideas on why the optimizer is not doing what is being done by breaking it into 2 queries. Clustered Indexes are on hospital_id, encounter_id in all the tables that are being joined. But it still does a index scan on the encounter
Any suggestions will be appreciated. Here is the query
SELECT
icd9_px_code val, TMP_PATS.encounter_id, TMP_PATS.hospital_id, prescore_calc.disease_group group_code, 0 intrv_number, 0 source, procedure_episode.day_of_stay,( datepart(hour, procedure_episode.start_datetime) * 100 + datepart(minute, procedure_episode.start_datetime )) st_time FROM procedures iv_tbl,RR_REQUEST_POPULATION TMP_PATS, PRESCORE_CALC ,ENCOUNTER ,procedure_episode WHERE icd9_px_code='3995' andTMP_PATS.score_type = 'LL' and TMP_PATS.encounter_id = PRESCORE_CALC.encounter_id andTMP_PATS.hospital_id = PRESCORE_CALC.hospital_id andTMP_PATS.request_id = 31 and TMP_PATS.encounter_id = iv_tbl.encounter_id and TMP_PATS.hospital_id = iv_tbl.hospital_id and TMP_PATS.encounter_id = encounter.encounter_id andTMP_PATS.hospital_id = encounter.hospital_id and iv_tbl.encounter_id = procedure_episode.encounter_id andiv_tbl.hospital_id = procedure_episode.hospital_id and iv_tbl.episode_sequence =procedure_episode.episode_sequence AND prescore_calc.procedure_end_date_dos is not null and procedure_episode.day_of_stay <= prescore_calc.procedure_end_date_dos order by tmp_pats.encounter_id, tmp_pats.hospital_id, prescore_calc.disease_group
drop
table #a select icd9_px_code val, TMP_PATS.encounter_id, TMP_PATS.hospital_id, iv_tbl.episode_sequence into #a FROM RR_REQUEST_POPULATION TMP_PATS , procedures iv_tbl WHERE icd9_px_code like '3995' and TMP_PATS.score_type = 'LL' and TMP_PATS.request_id = 31 and TMP_PATS.hospital_id = iv_tbl.hospital_id and TMP_PATS.encounter_id = iv_tbl.encounter_id
create clustered index xpkatemp on a# (hospital_id, encounter_id )
select
a.val, a.encounter_id, a.hospital_id ,prescore_calc.disease_group group_code, 0 intrv_number, 0 source, procedure_episode.day_of_stay, ( datepart(hour, procedure_episode.start_datetime) * 100 + datepart(minute, procedure_episode.start_datetime )) st_timefrom #a a , PRESCORE_CALC, ENCOUNTER , procedure_episode where a.hospital_id = PRESCORE_CALC.hospital_idand a.encounter_id = PRESCORE_CALC.encounter_idand a.hospital_id = encounter.hospital_id and a.encounter_id = encounter.encounter_id and a.hospital_id = procedure_episode.hospital_id and a.encounter_id = procedure_episode.encounter_id and a.episode_sequence = procedure_episode.episode_sequenceAND prescore_calc.procedure_end_date_dos is not null and procedure_episode.day_of_stay <= prescore_calc.procedure_end_date_dos order by a.encounter_id, a.hospital_id, prescore_calc.disease_group
September 20, 2006 at 4:28 am
Are the stats on the underlying tables uptodate? Can you post the 2 sets of execution plans? You may benefit even more by qualifying your tables with dbo.
As a readability level it would also improve if you used the SQL coding style
"select * from table1 a inner join table2 b on a.id = b.id, etc"
as it gives you more options/reusability later if you wished to delete/update records based on the multi-table join.
How many records endup in #a...compared to the volume of records in the other tables?
Is "icd9_px_code " in an index? given it is included in a 'like' statement (especially with NO wildcard after the constant!!) I note there is a minor difference in the 2 code samples in this respect.`...the original says "=3995"
September 20, 2006 at 5:34 am
Welcome to query tuning - you've answered your own question - using a temp table is better so do it that way < grin >
( please don't use select * it's a very poor way to code! )
You may not have the best indexes but without seeing a query plan I couldn't really say - have a look at the plans.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 20, 2006 at 7:32 am
I wish I had some insight into the original posting, but my question actually relates to one of the responses. Specifically,
Can you post the 2 sets of execution plans?
I've seen references to posting the execution plan, and I've even seen execution plans posted in this forum, but I can't figure out how to do that, once I've got the plan displayed in Query Analyzer.
Thanks,
Mattie
September 21, 2006 at 5:28 am
It's difficult which ever way you do it, but the showplan text is the easiest method to post a plan. I normally start with graphical plans as it's easy to spot glaring issues. Other than that I use profiler to store the plans when I'm looking at more specific problems.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 21, 2006 at 5:47 am
MattieNH....
I "believe" you need to post an image of the 'graphical' execution plan on a internet facing webserver....and link to that from here. I think this applies to all posting of 'images'. I'd like to do it myself as well at times....but I believe I need 'extra security access' to do so....which can't be justified. I also don't know of 'free webservers' to host these images.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply