-- ORIGINAL
SELECT
sob.id
, MAX(jnl.jnlID) AS jnlID
FROM
sysobjects sob
LEFT JOIN
MyJournalTbl jnl
ON
jnl.object_ID = sob.id
WHERE
sob.name = 'MyTableName'
AND sob.xtype IN ('U','V')
GROUP BY
sob.id
;
-- TESTING
SELECT
sob.id
, jnl.jnlID
FROM
sysobjects sob
OUTER APPLY
(
SELECT
TOP (1)
jnlID
FROM
MyJournalTbl
WHERE
object_ID = sob.id
ORDER BY
jnlID DESC
) jnl
WHERE
sob.name = 'MyTableName'
AND sob.xtype IN ('U','V')
;
CREATE TABLE MyJournalTbl(
insertDatetime datetime NOT NULL,
jnlID int IDENTITY(1,1) NOT NULL,
object_ID int NOT NULL,
pkValue int NOT NULL,
eventType tinyint NOT NULL,
CONSTRAINT PK_MyJournalTbl PRIMARY KEY CLUSTERED
(
jnlID ASC
)
)
;
CREATE NONCLUSTERED INDEX IDX_MyJournalTbl_max ON dbo.MyJournalTbl
(
object_ID ASC,
jnlID ASC
)
;
SELECT
object_ID
, COUNT(*) AS Sub_count
FROM
MyJournalTbl
GROUP BY
object_ID
ORDER BY
COUNT(*) DESC
;
SELECT
sob.id
--, MAX(jnl.jnlID) AS jnlID
FROM
sysobjects sob
--LEFT JOIN
-- MyJournalTbl jnl
--ON
-- jnl.object_ID = sob.id
WHERE
--sob.name = 'MyTableName' AND
sob.xtype IN ('U','V')
GROUP BY
sob.id
SET SHOWPLAN_ALL ON;
GO
-- ORIGINAL --
SELECT
sob.id
, MAX(jnl.jnlID) AS jnlID
FROM
sysobjects sob
LEFT JOIN
MyJournalTbl jnl
ON
jnl.object_ID = sob.id
WHERE
sob.name = 'MyTableName'
AND sob.xtype IN ('U','V')
GROUP BY
sob.id
;
GO
-- EVALUATION --
SELECT
sob.id
, MAX(jnl.jnlID) AS jnlID
FROM
sysobjects sob
LEFT JOIN
MyJournalTbl jnl
ON
jnl.object_ID = sob.id
AND sob.name = 'MyTableName'
AND sob.xtype IN ('U','V')
GROUP BY
sob.id
;
GO
-- TESTING --
SELECT
sob.id
, jnl.jnlID
FROM
sysobjects sob
OUTER APPLY
(
SELECT
TOP (1)
jnlID
FROM
MyJournalTbl
WHERE
object_ID = sob.id
ORDER BY
jnlID DESC
) jnl
WHERE
sob.name = 'MyTableName'
AND sob.xtype IN ('U','V')
;
GO
SET SHOWPLAN_ALL OFF;
SELECT
jnl.object_ID
, MAX(jnl.jnlID) AS jnlID
FROM
MyJournalTbl jnl
WHERE
jnl.object_ID = 333960266 --[ name='MyTableName' AND xtype IN ('U','V')]
GROUP BY
jnl.object_ID
OPTION (MAXDOP 1)
;
SELECT
jnl.object_ID
, MAX(jnl.jnlID) AS jnlID
FROM
MyJournalTbl jnl
WHERE
jnl.object_ID = 333960266 --[ name='MyTableName' AND xtype IN ('U','V')]
GROUP BY
jnl.object_ID
;
usually, either one server is using parallel query execution, actually slowing down performance for small queries, or the stats need to be recomputed on the tables/indexes.