Execution Times vs Logical Reads/Scan Count

  • tony28 (10/31/2013)


    slower queries are important but i think the CPU time = 78 ms, elapsed time = 79 ms are ok no??

    or do you think that is it problem ?

    If the times are OK, why are you optimising the query in the first place?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/31/2013)


    tony28 (10/31/2013)


    slower queries are important but i think the CPU time = 78 ms, elapsed time = 79 ms are ok no??

    or do you think that is it problem ?

    If the times are OK, why are you optimising the query in the first place?

    logical reads? ... But ok, I made new query, tommorow I will post and I have to test this ... thx for disscussing..



    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

  • The logical reads you posted in the OP look quite low.

    What about this query made you look at it?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/31/2013)


    The logical reads you posted in the OP look quite low.

    What about this query made you look at it?

    This procedure is in timer 1second and on 3positions, and when I used select for top procedures order by logical reads, it was there with lot of logical reads...and I think if it can be better, why dont do it.

    I can learn more.



    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

  • Hello, I think that this one is better, I will see, but I tested on test server and it looks good -- sqlplan in attachment

    What do you think about it ?

    SELECT distinct C.ORDER_DATE, C.COMMIT_NO, C.BODY_NO, C.CAR_CODE, "@@PROD_FLAG" FROM (

    SELECT * FROM ( SELECT TOP 2 ORDER_DATE, COMMIT_NO, BODY_NO, CAR_CODE, PROD_FLAG "@@PROD_FLAG" FROM TB_WORK_ORDER_MFD01 (NOLOCK)

    WHERE ORDER_DATE<=@LAST_ORDER_DATE AND ORDER_DATE + COMMIT_NO <= @LAST_ORDER_DATE + @LAST_COMMIT_NO AND DATA_TYPE <> 'SD'

    AND ORDER_DATE+COMMIT_NO NOT IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_REINPUT (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID)

    --AND ORDER_DATE+COMMIT_NO IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_MASTER_MFD01 (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID )

    ORDER BY ORDER_DATE DESC, COMMIT_NO desc ) A

    UNION ALL

    SELECT * FROM (SELECT TOP 5 ORDER_DATE, COMMIT_NO, BODY_NO, CAR_CODE, PROD_FLAG "@@PROD_FLAG" FROM TB_WORK_ORDER_MFD01 (NOLOCK)

    WHERE ORDER_DATE>=@LAST_ORDER_DATE AND ORDER_DATE + COMMIT_NO > @LAST_ORDER_DATE + @LAST_COMMIT_NO AND DATA_TYPE <> 'SD'

    AND ORDER_DATE+COMMIT_NO NOT IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_REINPUT (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID)

    --AND ORDER_DATE+COMMIT_NO IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_MASTER_MFD01 (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID )

    ORDER BY ORDER_DATE, COMMIT_NO ) B

    )C

    LEFT JOIN TB_MASTER_MFD01 M

    ON M.ORDER_DATE=C.ORDER_DATE

    AND M.COMMIT_NO = C.COMMIT_NO

    WHERE M.STATION_ID=@P_STATION_ID

    ORDER BY C.ORDER_DATE,C.COMMIT_NO

    (6 row(s) affected)

    Table 'TB_MASTER_MFD01'. Scan count 7, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'TB_REINPUT'. Scan count 2, logical reads 114, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'TB_WORK_ORDER_MFD01'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 8 ms.

    So now I think it looks more better, thx for discussing, after this I found another solution and refresh and learn next/new issue. thx



    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

  • Buenas tardes,

     

    dependiendo el caso son importantes las lecturas lógicas ,

    y en otros casos es importante el tiempo de ejecucion,

     

    Lecturas Lógicas : Consumo

    Tiempo de Ejecución: el tiempo es importante, por los procesos Compartidos (LOCKS)?

    LECTURAS LOGICAS VS TIEMPO DE EJECUCION , CUAL ES MEJOR =?

  • Brayan wrote:

    Buenas tardes,

    dependiendo el caso son importantes las lecturas lógicas ,

    y en otros casos es importante el tiempo de ejecucion,

    Lecturas Lógicas : Consumo

    Tiempo de Ejecución: el tiempo es importante, por los procesos Compartidos (LOCKS)?

    LECTURAS LOGICAS VS TIEMPO DE EJECUCION , CUAL ES MEJOR =?

    Since you speak English well enough to be able to respond, would you please do so in English?  Thanks and welcome aboard.

    --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)
    Intro to Tally Tables and Functions

  • You need to convert the "NOT IN"(s) to "NOT EXISTS"(s), like this:

    NOT EXISTS(SELECT 1 FROM TB_REINPUT TR WITH (NOLOCK) WHERE TR.ORDER_DATE>=@LAST_ORDER_DATE AND TR.STATION_ID=@P_STATION_ID AND

    TR.ORDER_DATE+COMMIT_NO = TB_WORK_ORDER_MFD01.ORDER_DATE+TB_WORK_ORDER_MFD01.COMMIT_NO)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 8 posts - 16 through 23 (of 23 total)

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