Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Optimizing SQL Query Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2008 11:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
The first thing I noticed was that you're using a function as the first part of your WHERE clause. This makes it difficult for the optimizer to us an index. Off the top of my head I would index MSP_WEB_WORK.WWORK_START and MSP_WEB_WORK.WWORK_FINISH in the table and change the WHERE to:

MSP_WEB_WORK.WWORK_START BETWEEN '1/1/2008'
And '12/31/08 11:59:59 PM'

Do the same for the FINISH column.

It seems that you only want records in 2008 anyhow.

You may get a performance improvement if the query weeds out a lot of rows before applying other criteria.

Todd Fifield
Post #481743
Posted Tuesday, April 8, 2008 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:42 AM
Points: 16, Visits: 104
Thank you Tod your suggestion was apply and the query run faster than before.
Post #481839
Posted Tuesday, April 8, 2008 1:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:42 AM
Points: 16, Visits: 104
Can someone help me I would like to save the execution plan using SQL management studio. But I'm still having the error telling me that :

Msg 195, Level 15, State 5, Line 1
'SHOWPLAN_XML' is not a recognized option.
Msg 195, Level 15, State 8, Line 1
'XML' is not a recognized statistics option.

Thanks

Post #481850
Posted Tuesday, April 8, 2008 1:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 7,115, Visits: 14,984
Stanley Pagenel (4/8/2008)
Can someone help me I would like to save the execution plan using SQL management studio. But I'm still having the error telling me that :

Msg 195, Level 15, State 5, Line 1
'SHOWPLAN_XML' is not a recognized option.
Msg 195, Level 15, State 8, Line 1
'XML' is not a recognized statistics option.

Thanks



SET SHOWPLAN_XML is a SQL Server 2005-specific option (or rather - introduced in 2005 and later versions). It needs to be run against a SQL 2005 database engine. Your data is running against a SQL 2000 engine, so that setting doesn't exist.

Your options would be SHOWPLAN_TEXT or SHOWPLAN_ALL.


----------------------------------------------------------------------------------
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?
Post #481860
Posted Tuesday, April 8, 2008 2:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:42 AM
Points: 16, Visits: 104
Matt is there anyway I can do that in a 2000 environment.
Post #481862
Posted Tuesday, April 8, 2008 2:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #481887
Posted Wednesday, April 9, 2008 5:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:55 AM
Points: 132, Visits: 529
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'
Post #482186
Posted Wednesday, April 9, 2008 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:42 AM
Points: 16, Visits: 104
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 .
Post #482326
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse