for this query, i think we'd need to see the actual execution plan on this, as well as a list of all indexes on the fctAssetFulfillment and the FulfillmentPartnerInstall tables.
can you post that here as a .sqlplan attachment?
one problem is this portion of the WHERE statement:
AND fpi.AssetUsage LIKE '%install%'
'that's going to require a scan of the table; is there another way to filter the data to get the same rows? a status which coincides with that, maybe?
New Born DBA (1/14/2014)
This is the query which takes about 32 seconds to execute. Any help will be appreciated.Checked the index on **ID which is only 10% fragmented. No blocking issues.
SELECT fct.AccountID, count(*)
FROM fctAssetFulfillment fct
JOIN FulfillmentPartnerInstall fpi ON fpi.FMProBoxID = fct.AMC_BoxID
WHERE fct.Source = 'FMPro' AND isnumeric(fct.AMC_BoxID)<>0
AND fct.SerialNo = fpi.SerialTag
AND fpi.CompletionDate IS NOT NULL
AND fpi.AssetUsage LIKE '%install%'
AND fct.InstallActualDate<>fpi.CompletionDate
--AND (fct.FulfillmentStatus <> 'installed')
GROUP BY fct.AccountID
Lowell