Optimizing SQL Query

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  April 8, 2008 at 2:42 pm

  #800015

  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

  April 9, 2008 at 5:33 am

  #800275

  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

  April 9, 2008 at 7:44 am

  #800401

  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 .