The Same Sql Query Takes Longer To Run In One Db Than Another Db Under The Same Server

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 comple

Solution 1:

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:

• mdf and ldf files (for each database) are on different drives. If one drives is faster, that database will run the query faster too.
• stalled statistics. If you have one database with newer stats than the other one, SQL has better chances of picking a proper (and faster) execution plan.
• Indexes: I know you said they both are identical, but I would check if you have same type of Indexes on both.

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.

Solution 2:

Best way is rebuild & reorganize your request

SELECTDISTINCT  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')