If you need to get the largest value for a field, should you use MAX in your query? Or should you use TOP 1 with ORDER BY?
Which is better? Which is faster? Is that always true?
Do you think you know the answer?
Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.
The results may surprise you!
Did I miss anything or make any mistakes in my testing? Are there other considerations when choosing between MAX vs. TOP 1?
Here are the queries that I used in my testing. Note that your results will vary depending on how much data you have in your tables and your SQL Server version.
--MAX vs TOP 1 with ORDER
BY
SET STATISTICS IO ON
SELECT MAX(DOCDATE) AS
DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'
SELECT TOP 1 DOCDATE FROM
PM30200 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC
SET STATISTICS IO OFF
SET STATISTICS IO ON;
WITH cteMaxDate (DOCDATE)
AS
(
SELECT MAX(DOCDATE) FROM PM10000 WHERE VENDORID =
'ACETRAVE0001'
UNION
SELECT MAX(DOCDATE) FROM PM20000 WHERE VENDORID =
'ACETRAVE0001'
UNION
SELECT MAX(DOCDATE) FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'
)
SELECT MAX(DOCDATE) AS
DOCDATE FROM cteMaxDate;
WITH cteMaxDate2
(DOCDATE) AS
(
SELECT TOP 1 DOCDATE FROM PM10000 WHERE VENDORID =
'ACETRAVE0001' ORDER BY DOCDATE DESC
UNION
SELECT TOP 1 DOCDATE FROM PM20000 WHERE VENDORID =
'ACETRAVE0001' ORDER BY DOCDATE DESC
UNION
SELECT TOP 1 DOCDATE FROM PM30200 WHERE VENDORID =
'ACETRAVE0001' ORDER BY DOCDATE DESC
)
SELECT MAX(DOCDATE) AS
DOCDATE FROM cteMaxDate2;
SET STATISTICS IO OFF;
SELECT COUNT(*) FROM
SEE30303 --73,069 records
SELECT TOP 10 * FROM
SEE30303
SET STATISTICS IO ON;
SELECT MAX(DATE1) AS
DATE1 FROM SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE')
SELECT TOP 1 DATE1 FROM
SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE') ORDER BY DATE1
DESC
SET STATISTICS IO OFF;
SET STATISTICS IO ON;
SELECT MAX(DATE1) AS
DATE1 FROM SEE30303
SELECT TOP 1 DATE1 FROM
SEE30303 ORDER BY DATE1 DESC
SET STATISTICS IO OFF;
SET STATISTICS IO ON;
SELECT MAX(DATE1) AS
DATE1 FROM SEE30303 OPTION (MAXDOP 1)
SELECT TOP 1 DATE1 FROM
SEE30303 ORDER BY DATE1 DESC OPTION (MAXDOP 1)
SET STATISTICS IO OFF;
SELECT COUNT(*) AS Rows
FROM IV30500
SELECT TOP 100 * FROM
IV30500
SET STATISTICS IO ON;
SELECT MAX(POSTEDDT) AS
POSTEDDT FROM IV30500 --OPTION (MAXDOP 1)
SELECT TOP 1 POSTEDDT
FROM IV30500 ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)
SET STATISTICS IO OFF;
SET STATISTICS IO ON;
SELECT MAX(POSTEDDT) AS
POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT
BETWEEN '2017-01-01' AND '2017-12-31' --OPTION (MAXDOP 1)
SELECT TOP 1 POSTEDDT
FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT BETWEEN
'2017-01-01' AND '2017-12-31' ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)
SET STATISTICS IO OFF;
SET STATISTICS IO ON;
SELECT MAX(TRXSORCE) AS
POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG')
SELECT TOP 1 TRXSORCE
FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') ORDER BY TRXSORCE
DESC
SET STATISTICS IO OFF;
USE [TWO]
GO
CREATE NONCLUSTERED INDEX
NCI_IV30500_ITEMNMBR
ON [dbo].[IV30500]
([ITEMNMBR])
INCLUDE ([TRXSORCE])
GO
USE [TWO]
GO
DROP INDEX
IV30500.NCI_IV30500_ITEMNMBR
GO
Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.
No comments:
Post a Comment