Optimizing SQL Query

Question

Optimizing SQL Query

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Short of recreating your environment on 2005, or attaching that database to a 2005 server and upgrading it to compatibility level 90 , no, not that I know of.

Neither of the above is a trivial task, so they're not an option. Even if you did either, you would have to spend some time re-optimizing, reorganizing, etc... just to make sure that the database performance is up to snuff.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

w.lengenfelder SSC Eights! Points: 884 More actions · Answer 2

as the people posted before:

I would also avoid the use of a funktion in the From or Where Cause.

Especially in the case of the function is not scalar.

Further you should use = to compare RES_UID with 'Interne'. The Query Analyzer handles this better.

I would replace the From and Where Clause with

FROM MSP_WEB_WORK INNER JOIN
MSP_WEB_ASSIGNMENTS ON MSP_WEB_WORK.WASSN_ID = MSP_WEB_ASSIGNMENTS.WASSN_ID INNER JOIN
MSP_WEB_PROJECTS ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN
MSP_WEB_RESOURCES ON MSP_WEB_WORK.WRES_ID = MSP_WEB_RESOURCES.WRES_ID INNER JOIN
MSP_RESOURCES ON MSP_WEB_PROJECTS.PROJ_ID = MSP_RESOURCES.PROJ_ID AND
MSP_WEB_RESOURCES.RES_EUID = MSP_RESOURCES.RES_EUID INNER JOIN
MSP_TASKS ON MSP_WEB_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID AND MSP_WEB_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID
INNER JOIN MSP_RESOURCES as Resources2 ON MSP_RESOURCES.RES_EUID = Resources2.RES_UID
INNER JOIN MSP_TEXT_FIELDS ON Resources2.RES_UID = MSP_TEXT_FIELDS.TEXT_REF_UID
AND MSP_TEXT_FIELDS.PROJ_ID = Resources2.PROJ_ID
WHERE -- year(MSP_WEB_WORK.WWORK_START)=2008 and year(MSP_WEB_WORK.WWORK_FINISH)=2008
MSP_WEB_WORK.WWORK_START BETWEEN '1/1/2008' And '12/31/08 11:59:59 PM'
AND (MSP_WEB_WORK.WWORK_TYPE = 1 OR MSP_WEB_WORK.WWORK_TYPE = 2)
-- AND (dbo.GetInterneExterne(MSP_RESOURCES.RES_EUID) LIKE 'Interne')
AND (MSP_TEXT_FIELDS.PROJ_ID = 1) AND (MSP_TEXT_FIELDS.TEXT_FIELD_ID = 205521546)
AND Resources2.RES_UID = 'Interne'

Stanley Pagenel Ten Centuries Points: 1283 More actions · Answer 3

Thanks a lot guys for your effort helping me solving this issue. Removing all the functions from the where clause solve the problem. The query ran a lot faster now. I consider this case close .