I inherited a process that has suddenly started to fail.
There is table that has 7126226 rows in it and the process is trying to find the min(createdate) for each rowid where a certain criteria is met.
There is a non-clustered index on the rowid and the table is not in rowid order.
This query is run 5 different times against the MDM_C_PARTY_HIST table matching to different tables and fields each time.
Here is one of the queries - in this case the PH.ROWID_OBJECT and the BU.ROWID_OBJECT have indexes but the PH.ROWID_BU does not.
dbo.MDM_C_PARTY_HIST AS PH
JOIN dbo.MDM_C_LKP_BU AS BU ON PH.ROWID_BU = BU.ROWID_OBJECT
BU.BUS_UNIT_NAME <> 'PENDING'
I have left this query run for 25 min and gotten no results. When I look at the query plan it says it\ estimates 580993000 rows.
Here is the question:
I am not sure the best approach for getting this to work.
Do I make the PH.ROWID_OBJECT index clustered instead of nonclustered?
Do I add an index for each field that the the BU.ROWID_OBJECT is being joined to on the PH table?
I am worried that adding more indexes will affect the load process. This table already takes more than an hour to load. If I add 5 more indexes, or add a clustered index won't that just show it down even more?
Is there a better approach to take ? I am at a total loss for how to address this.