October 31, 2013 at 7:33 am
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
October 31, 2013 at 10:28 am
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..
October 31, 2013 at 11:00 am
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
October 31, 2013 at 12:09 pm
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.
November 1, 2013 at 12:19 am
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
July 28, 2022 at 6:56 pm
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 =?
July 29, 2022 at 12:02 am
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
Change is inevitable... Change for the better is not.
July 29, 2022 at 2:48 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy