Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm new to visual studio 2008, microsoft sql server 2008, little under 2 weeks now. Was hoping someone can help clean up my code. I'm using sql business intelligence development studio. This is a large database and has to pull back as least 6000+ pages. This code works with smaller amounts of information but on the larger ones hangs. I'm guessing it's a coding error on my part slowing it down.

SELECT   
     vRTCAttStatusDaily.yr_cde,   
     vRTCAttStatusDaily.trm_cde,   
     vRTCAttStatusDaily.crs_cde,
     vRTCAttStatusDaily.clean_crs_cde,   
     vRTCAttStatusDaily.id_num,   
     vRTCAttStatusDaily.firstname,   
     vRTCAttStatusDaily.lastname,   
     vRTCAttStatusDaily.middlename,   
     name_master.first_name,   
     name_master.last_name,   
     address_master.phone,   
     stud_term_sum_div.udef_1a_1,   
     table_detail.table_desc,   
     vrtcreauthorizationstatus.authorization_status,   
     section_master.division_cde,   
     degree_history.major_1,   
     vRTCAttStatusDaily.StartDate,  
     vRTCAttStatusDaily.cleanclassdate,
     vRTCAttStatusDaily.attend_status,
 stud_term_sum_div.id_num AS id_num2



FROM {oj vRTCAttStatusDaily LEFT OUTER JOIN vrtcreauthorizationstatus ON vRTCAttStatusDaily.id_num = vrtcreauthorizationstatus.id_num LEFT OUTER JOIN section_master ON vRTCAttStatusDaily.yr_cde = section_master.yr_cde AND vRTCAttStatusDaily.trm_cde = section_master.trm_cde AND vRTCAttStatusDaily.crs_cde = section_master.crs_cde},   
     faculty_load_table,   
     name_master,   
     address_master,   
     stud_term_sum_div,   
     table_detail,   
     degree_history

WHERE ( vRTCAttStatusDaily.yr_cde = faculty_load_table.yr_cde ) and  
      ( vRTCAttStatusDaily.trm_cde = faculty_load_table.trm_cde ) and  
      ( vRTCAttStatusDaily.crs_cde = faculty_load_table.crs_cde ) and  
      ( faculty_load_table.instrctr_id_num = name_master.id_num ) and  
      ( vRTCAttStatusDaily.id_num = stud_term_sum_div.id_num ) and  
      ( vRTCAttStatusDaily.yr_cde = stud_term_sum_div.yr_cde ) and  
      ( vRTCAttStatusDaily.trm_cde = stud_term_sum_div.trm_cde ) and  
      ( stud_term_sum_div.udef_1a_1 = table_detail.table_value ) and  
      ( vRTCAttStatusDaily.id_num = address_master.id_num ) and  
      ( vRTCAttStatusDaily.id_num = degree_history.id_num ) and  
      ( ( faculty_load_table.lead_instrctr_flg = 'Y' ) AND  
      ( address_master.addr_cde = '*LHP' ) AND  
      ( table_detail.column_name = 'rtc_enroll_sts' ) AND  
      ( vRTCAttStatusDaily.yr_cde IN (@Year)) AND 
      ( vRTCAttStatusDaily.trm_cde IN (@Term)) AND
       degree_history.cur_degree = 'Y' )

ORDER BY address_master.id_num,
 vRTCAttStatusDaily.startdate,
 vRTCAttStatusDaily.clean_crs_cde
share|improve this question
1  
Man, edit and fix your code formatting! – Will Jun 2 '11 at 15:06

1 Answer

up vote 2 down vote accepted

Three things pop right out at me. The first is that you are doing a lot of joins for one table. Every time you have a join, you lose a bit of performance. If possible, you would have better results if you could limit the number of joins you have. This isn't always possible, but it is something to at least consider.

Second, you are doing all of your join conditions in the WHERE statement. While this is acceptable, it does leave you vulnerable to accidentally creating a CROSS JOIN by not specifying a relationship. I would move the join conditions into the JOIN statements. This will also allow you to specify your join types explicitly. This might be another area to review (do you need all INNER joins or should some of them be LEFT joins?).

Third, you are going to be heavily dependent on proper indexes in your database. I would review your indexes to make sure that every join condition has an index on it.

In general, this query will slow down when it tries to join large numbers of records. If you still have issues after reviewing the above suggestions, I would suggest you look at your query execution plan.

share|improve this answer
Thank you very much sir that was exactly the specific advise I was needing. – Daniel Jun 2 '11 at 15:41
It's worth looking at your execution plan to see where you are causing bottlenecks. Have a review of the execution plan both before and after making changes to gain an understanding of how different improvements affect the overall query performance. – Matthew Abbott Jun 2 '11 at 18:08
Ok, it turns out the code written for vrtcattstatusdaily called on itself basically twice. Not much I could do about that, but removing faculty_table_load from the query made it work properly. It now can pull 289k rows in roughly 1.4 mins. Thanks Biggs – Daniel Jun 7 '11 at 15:23

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.