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 |
|
rajasnkl
Starting Member
1 Post |
 Posted - 2009-03-25 : 12:36:47
|
| Hi All,This portion of the query which is part of 'MS project Central' is causing performance issue that it runs more than 70 seconds when the expected time should be between 3 to 5 seconds.Select Distinct MSP_WEB_WORKGROUP_FIELDS_INFO.CUSTFIELD_INFO_ID,CUSTFIELD_NAME FROM MSP_WEB_WORKGROUP_FIELDS_INFO MSP_WEB_WORKGROUP_FIELDS_INFO, MSP_WEB_PROJECT_WORKGROUP_INFO MSP_WEB_PROJECT_WORKGROUP_INFO, MSP_WEB_ASSIGNMENTS WA, MSP_WEB_WORKGROUP_FIELDS MSP_WEB_WORKGROUP_FIELDS Where MSP_WEB_WORKGROUP_FIELDS_INFO.FIELD_ID=MSP_WEB_WORKGROUP_FIELDS.FIELD_ID and WA.WASSN_ID=MSP_WEB_WORKGROUP_FIELDS.WASSN_ID and MSP_WEB_WORKGROUP_FIELDS_INFO.CUSTFIELD_INFO_ID=MSP_WEB_PROJECT_WORKGROUP_INFO.CUSTFIELD_INFO_ID and MSP_WEB_PROJECT_WORKGROUP_INFO.WPROJ_ID=WA.WPROJ_ID and WA.WASSN_IS_CONFIRMED<>0 and ((WA.WRES_ID=521 and WA.WASSN_REMOVED_BY_RESOURCE=0) OR WA.WRES_ID_TEAM_LEAD=521 OR WA.WASSN_ID in (-1)) ORDER BY A.CUSTFIELD_INFO_ID None of the following option helps to get the time down to 3 to 5 seconds. I tried running this query after each of the statistics update.But no helps and it stills runs 70 seconds.1) sp_updatestatus2) UPDATE STATISTICS <for all 4 tables> WITH FULLSCAN3) UPDATE STATISTICS <for all 4 tables> WITH SAMPLE 1000 RECORDSThe number of records in each tables are MSP_WEB_ASSIGNMENTS = 86950MSP_WEB_PROJECT_WORKGROUP_INFO = 259200MSP_WEB_WORKGROUP_FIELDS = 259200MSP_WEB_WORKGROUP_FIELDS_INFO = 336Each of these tables have some CLUSTERED INDEX and some UNIQUE index. Apparently the UNIQUE index on the column CUSTFIELD_INFO_ID of MSP_WEB_WORKGROUP_FIELDS_INFO is the culprit.What I mean by culprit is that If I convert the UNIQUE on CUSTFIELD_INFO_ID of MSP_WEB_WORKGROUP_FIELDS_INFO to simple INDEX, My query runs very fast and gives me results in 3 seconds. Since this table has only 336 rows, I even tried by removing the index on this column and re-run the query. To my surprise, query ran in 3 seconds again.I don't have any control on this query, as it comes from different application.So the possibilities of tweaking this query to make it efficient is not an option.You may have a question in your mind that why can't I get around this performance issue with either by using simple index or eliminating index on the column CUSTFIELD_INFO_ID of MSP_WEB_WORKGROUP_FIELDS_INFO. Answers is I need UNIQUE index to enforce duplication prevention on the column CUSTFIELD_INFO_ID. Therefore I am left with only option of playing around with statistics so that I get the results in 3-5 seconds without changing the database design.Any thoughts to fix this problem is very much appreciated. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-03-25 : 16:06:27
|
It is so beauty...SELECT DISTINCT msp_web_workgroup_fields_info.custfield_info_id, custfield_name FROM msp_web_workgroup_fields_info msp_web_workgroup_fields_info, msp_web_project_workgroup_info msp_web_project_workgroup_info, msp_web_assignments wa, msp_web_workgroup_fields msp_web_workgroup_fields WHERE msp_web_workgroup_fields_info.field_id = msp_web_workgroup_fields.field_id AND wa.wassn_id = msp_web_workgroup_fields.wassn_id AND msp_web_workgroup_fields_info.custfield_info_id = msp_web_project_workgroup_info.custfield_info_id AND msp_web_project_workgroup_info.wproj_id = wa.wproj_id AND wa.wassn_is_confirmed <> 0 AND ((wa.wres_id = 521 AND wa.wassn_removed_by_resource = 0) OR wa.wres_id_team_lead = 521 OR wa.wassn_id IN (-1)) ORDER BY a.custfield_info_id Maybe now someone can read this and give any tipsWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
|
|
PERFORMANCE ISSUE WITH QRY USING UNIQUE INDEX(SQ