Execution Times vs Logical Reads/Scan Count

Question

Execution Times vs Logical Reads/Scan Count

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

tony28 SSCertifiable Points: 7108 More actions · Answer 2

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..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

tony28 SSCertifiable Points: 7108 More actions · Answer 4

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.

tony28 SSCertifiable Points: 7108 More actions · Answer 5

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

Brayan Newbie Points: 1 More actions · Answer 6

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 =?

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

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)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 8

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) A socialist is someone who will give you the shirt off *someone else's* back.