Jeff Moden (7/16/2014)
Igor Micev (7/14/2014)
--Extend this index to make it coverable for the query:CREATE UNIQUE NONCLUSTERED INDEX [UIX_did_uid_sid] ON [Dim].[UserDevice]
(
[DeviceID] ASC,
[UserID] ASC,
[SubscriberID] ASC
)
INCLUDE(
Locale,[LastUsedDate]
)
--Create this indexCREATE UNIQUE NONCLUSTERED INDEX [UIX_NCSTransaction_IDs] ON [Ext].[NCSTransaction]
(
[SubscriberID],[UserID],[DeviceID]
)
INCLUDE ([Locale],location,[TransactionStartDateTime])
If the "select" part of the merge statement takes 4 minutes, then you'd better replace that code with a temp table and put the second index on the temp table.
I think there is a chance to improve it a lot. So it's your turn
Since the ROW_NUMBER partitioning in the query is...
row_number() over(partition by [SubscriberID],[UserID],[DeviceID] order by transactionstartdatetime desc) as rn
... you might erg out a bit more performance if you made that second index include the transactionstartdatetime desc as a part of the key rather than a part of the INCLUDE.
Hi Jeff,
I'm not sure about your proposal.
Example:
CREATE NONCLUSTERED INDEX [Index1] ON [dbo].[FactProductInventory]
([ProductKey] ASC,
[DateKey] ASC)
CREATE NONCLUSTERED INDEX [Index2] ON [dbo].[FactProductInventory]
([ProductKey] ASC)
INCLUDE([DateKey])
DBCC DROPCLEANBUFFERS
set statistics io on
SELECT t.ProductKey,t.DateKey
FROM DBO.FactProductInventory t
set statistics io off
/* Index2 used automatically by SQL Server
(776286 row(s) affected)
Table 'FactProductInventory'. Scan count 1, logical reads 1352, physical reads 3, read-ahead reads 1362, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
*/
DBCC DROPCLEANBUFFERS
set statistics io on
SELECT t.ProductKey,t.DateKey
FROM DBO.FactProductInventory t with (index (Index1))
set statistics io off
/* Forcing Index1
(776286 row(s) affected)
Table 'FactProductInventory'. Scan count 1, logical reads 1352, physical reads 3, read-ahead reads 1362, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
*/
Both columns are INT type. The query optimizer chose to use the index which is narrower (Index2). I think it would be still more firm for the real scenario from above.
Igor Micev,My blog: www.igormicev.com