Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcolli00
Starting Member
19 Posts |
 Posted - 2011-03-23 : 18:15:06
|
| I am a newby DBA and trying to improve a query that is taking 37 seconds to finish. Just by looking at the code, will you give me pointers of what I can do to improve this? Our developers do not want to remove the cross apply which seems to be the culprit here. Your feed back is appreciated so very much! MC--start queryselect fu_fb_fk,shft_pk, sum(fu_cleanableSquareFeet) as squareFeet, dailyAdjustedMinutes= sum( case when cmx_exceptionQuantity = 0 then convert(decimal(18,2),(cmx_baseQuantity *1.0) / isnull(cmx_areaCSF,1) * f_area.fu_cleanableSquareFeet * isnull(cmx_baseTime,0) /isnull(cmx_perUnitQuantity,1) *cfreq_factor) else convert(decimal(18,2),cmx_exceptionQuantity * 1.0 * isnull(fu_cleanableSquareFeet,0)*ISNULL(cmx_baseTime,0) /ISNULL(cmx_perUnitQuantity,1)* cfreq_factor) end) from f_area cross join f_shift inner join v_areatype on fu_wa_fk=type_pk left outer join f_custodialMatrix matrix on cmx_fu_fk = f_area.fu_pk or cmx_wa_fk=v_areatype.wa_pk or matrix.cmx_apwa_fk=(case when fu_apwa_fk is not null then wa_apwa_fk else wa_apwa_fk end) left outer join f_custodialMatrixShift on csm_shft_fk = shft_pk and csm_cmx_fk=matrix.cmx_pk left outer join f_webtmaCustodialFrequency on cfreq_pk=(case when csm_level=1 then cmx_level1 when csm_level=2 then cmx_level2 when csm_level=3 then cmx_level3 when csm_level=4 then cmx_level4 else cmx_level5 end) where fu_clnt_fk = 1000 and shft_clnt_fk=1000 and shft_isCustodial = 1 and fu_cleanableSquareFeet is not null group by fu_fb_fk, shft_pk ------------end query. |
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2011-03-24 : 05:53:05
|
| Hi mcolli00,I'm also no expert in MS SQL but there are some helpful tools already delivered by Microsoft. If you start MS SQL MAnagement Studio and load this query you could select "Query" -> "Display estimated execution plan". When you then run the query you see some more details on the execution, especially how long each part takes. Then you could start optimization on the most time-consuming part instead of trying some wild guesses. Here are some further links you might find helpful if you haven't use the execution plan yet:[url]http://msdn.microsoft.com/en-us/library/ms191194.aspx[/url][url]http://msdn.microsoft.com/en-us/library/ms191227.aspx[/url]Hope this helps. |
 |
|
|
mcolli00
Starting Member
19 Posts |
Posted - 2011-03-24 : 14:41:04
|
| I see. Thanks for replying, Heinz23! MC |
|
|
|
|
|
|
|
|
help simplify query - seems very complex..to me.