Post reply

QUERY TUNNING.......

  • February 11, 2008 at 6:48 pm

    #119901

    Hello everybody,

    Can anyone give any sugestion fr the query that's down? I've no idea on how to change it to give us better performance.

    Thanks and regards,

    JMSM:blush:

    SELECT CAST(tblRFXProjecto.lngCCId AS VARCHAR) + '.' + CAST(tblRFXProjecto.intCCObrType AS VARCHAR) AS ID,

    tblRFXProjecto.strCCCod AS Reference,

    tblRFXProjecto.strCCName AS Name,

    tblRFXProjecto.strCCAddress AS Address,

    tblRFXProjecto.strCCCodPostal AS ZipCode,

    tblRFXProjecto.strCCDescr AS Description,

    tblRFXProjecto.strCCObs AS OtherInformations,

    tblRFXProjecto.intCCNumFloors_Km AS NrOfFloors,

    tblRFXProjecto.strCCCodLocal AS Town,

    --ISNULL(CAST(tblRFXProjecto.intCCEstValue AS varchar),'0') AS Price,

    CAST(tblGRLTables.strParm2 AS varchar) AS Price,

    CAST(ISNULL(constructionStates.lngCCAreaConst,0) AS INTEGER) AS BuildingSiteSurface,

    CONVERT(VARCHAR, tblRFXProjecto.tmsCreated, 103) AS ConstructionCreationDate,

    tblGRLDistricts.strDistrictDesc AS ProvinceDescr,

    tblGRLDistricts.intDistrictId AS ProvinceID,

    constructionTypesDescr.strDescContest1 + ', ' + constructionTypesDescr.strDescContest3 AS ConstructionTypeDescr,

    constructionTypes.strContestId AS ConstructionTypeID,

    CONVERT(VARCHAR, tblRFXProjecto.tmsChanged, 103) AS ConstructionStateUpdateDate,

    constructionStatesDescr.strDescContest1 + ', ' + constructionStatesDescr.strDescContest3 AS ConstructionStateDescr,

    constructionStates.strContestId AS ConstructionStateID

    FROM tblRFXProjecto

    INNER JOINtblGRLDistricts ON tblGRLDistricts.intDistrictId = tblRFXProjecto.intCCDistrictId AND tblGRLDistricts.intDistrictId<50 --Para so dar de Portugal

    INNER JOINtblINFEntities ON tblINFEntities.gidEntAderenteId = tblRFXProjecto.gidCCEntityguid AND tblINFEntities.strGuidTestes IS NULL

    INNER JOINtblRFXProjectoTST constructionStates ON constructionStates.lngCCId = tblRFXProjecto.lngCCId AND constructionStates.intTypeData = 2 AND constructionStates.bytState = 1

    INNER JOINtblRFXProjectoTST constructionTypes ON constructionTypes.lngCCId = tblRFXProjecto.lngCCId AND constructionTypes.intTypeData = 1 AND constructionTypes.bytState = 1

    INNER JOIN tblINFNegContConfig constructionStatesDescr ON constructionStatesDescr.strContestId = constructionStates.strContestId AND constructionStatesDescr.intLanguage = 1

    INNER JOIN tblINFNegContConfig constructionTypesDescr ON constructionTypesDescr.strContestId = constructionTypes.strContestId AND constructionTypesDescr.intLanguage = 1

    LEFT OUTER JOIN tblGRLTables ON tblGRLTables.lngTableId = 64 AND tblGRLTables.intLanguage = 0 AND tblGRLTables.strElemId = tblRFXProjecto.intCCEstValue

    WHERE CONVERT(VARCHAR, tblRFXProjecto.tmsCreated, 103) = CONVERT(VARCHAR, getdate()-1, 103)

    AND tblRFXProjecto.intCCObrType=2

    AND tblRFXProjecto.intOBROrigin=0 AND tblRFXProjecto.intTest=0

    AND tblRFXProjecto.bytstate=1

    GO

  • February 11, 2008 at 7:09 pm

    #777175

    For starters, this....

    WHERE CONVERT(VARCHAR, tblRFXProjecto.tmsCreated, 103) = CONVERT(VARCHAR, getdate()-1, 103)

    ... will not allow indexes to be used effectively because of the forced scan created by wrapping the column reference in a formula and forcing a datatype change. Change that line to this...

    WHERE tbltblRFXProjecto.tmsCreated >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0)

    AND tbltblRFXProjecto.tmsCreated < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    Second... except in the rarest of cases, filters like " AND tblGRLDistricts.intDistrictId<50 --Para so dar de Portugal" should usually be in the WHERE clause... not as part of the ON join clause...

    Third... do you actually have any indexes/PK's on any of these tables? Do you have a clustered index on each table that makes sense?

    Fourth... do any of the columns in the ON clauses do an IMPLICIT conversion between datatypes???

    Fifth... is the outer join absolutely essential?

    Sixth... is it absolutely essential to resolve this all in one query or can you get a Temp Table involved in the mix to hold some smaller interim results?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 11, 2008 at 7:22 pm

    #777179

    Thanks a lot Jeff.

    Regards,

    JMSM

  • February 11, 2008 at 7:59 pm

    #777192

    You bet... let us know how it works out... tuning queries of this nature may take one of the items I listed... or all 6. Maybe even something else... for example... are all of the tables in this query actually "tables"? Or might one or more of them be some (poorly) written views that are cpu or disk hogs?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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