Reputation: 41
I have a SQL database server and 2 databases under it with the same structure and data. I run the same sql query in the 2 databases, one of them takes longer while the other completes in less than 50% of the time. They both have different execution plans.
The query for the view is as below:
SELECT DISTINCT i.SmtIssuer, i.SecID, ra.AssetNameCurrency AS AssetIdCurrency, i.IssuerCurrency, seg.ProxyCurrency, shifts.ScenarioDate, ten.TenorID, ten.Tenor,
shifts.Shift, shifts.BusinessDate, shifts.ScenarioNum
FROM dbo.tblRrmIssuer AS i INNER JOIN
dbo.tblRrmSegment AS seg ON i.Identifier = seg.Identifier AND i.SegmentID = seg.SegmentID INNER JOIN
dbo.tblRrmAsset AS ra ON seg.AssetID = ra.AssetID INNER JOIN
dbo.tblRrmHistSimShift AS shifts ON seg.Identifier = shifts.Identifier AND i.SegmentID = shifts.SegmentID INNER JOIN
dbo.tblRrmTenor AS ten ON shifts.TenorID = ten.TenorID INNER JOIN
dbo.tblAsset AS a ON i.SmtIssuer = a.SmtIssuer INNER JOIN
dbo.tblRrmSource AS sc ON seg.SourceID = sc.SourceID
WHERE (a.AssetTypeID = 0) AND (sc.SourceName = 'CsVaR') AND (shifts.SourceID =
(SELECT SourceID
FROM dbo.tblRrmSource
WHERE (SourceName = 'CsVaR')))
The things i have already tried are - rebuild & reorganize index on the table (tblRRMHistSimShifts - this table has over 2 million records), checked for locks or other background processes or errors on server, Max degree of parallelism for the server is 0.
Is there anything more you can suggest to fix this issue?
Upvotes: 4
Views: 9099
Reputation: 16904
Best way is rebuild & reorganize your request
SELECT DISTINCT i.SmtIssuer, i.SecID, ra.AssetNameCurrency AS AssetIdCurrency, i.IssuerCurrency, seg.ProxyCurrency, shifts.ScenarioDate, ten.TenorID, ten.Tenor,
shifts.Shift, shifts.BusinessDate, shifts.ScenarioNum
FROM dbo.tblRrmIssuer AS i INNER JOIN dbo.tblRrmSegment AS seg ON i.Identifier = seg.Identifier AND i.SegmentID = seg.SegmentID
INNER JOIN dbo.tblRrmSource AS sc ON seg.SourceID = sc.SourceID
INNER JOIN dbo.tblRrmAsset AS ra ON seg.AssetID = ra.AssetID
INNER JOIN dbo.tblRrmHistSimShift AS shifts ON seg.Identifier = shifts.Identifier AND i.SegmentID = shifts.SegmentID AND shifts.SourceID = sc.SourceID
INNER JOIN dbo.tblRrmTenor AS ten ON shifts.TenorID = ten.TenorID
INNER JOIN dbo.tblAsset AS a ON i.SmtIssuer = a.SmtIssuer
WHERE (a.AssetTypeID = 0) AND (sc.SourceName = 'CsVaR')
Upvotes: 0
Reputation: 11
The fact that you have two databases on same server and with same data set (as you said) does not ensure same execution plan.
Here are some of the reasons why the query plan may be different:
Focus on see why the query is running slow or see the actual execution plan, instead of comparing. Checking the actual execution plan for the slow query will give you a hint of why is running slower.
Also, I would not add a NO LOCK statement to fix the issue. In my experience, most slow queries can be tuned up via code or Index, instead of adding a NO LOCK hint that may get you modified or old result sets, depending of your transactions.
Upvotes: 1