Geek Answers Handbook

The same SQL query takes longer to work in one database than another database under the same server

I have an SQL database server and 2 databases under it with the same structure and data. I run the same sql query in two databases, one of them takes longer and the other in less than 50% of cases. They both have different implementation plans.

The request for the presentation is as follows:

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

What I have already tried is to rebuild and reorganize the index in the table (tblRRMHistSimShifts - this table has more than 2 million records), it is checked for locks or other background processes or errors on the server, the maximum degree of parallelism for the server is 0.

Is there anything else you can suggest to fix this problem?

+4
source share
2 answers

The fact that you have two databases on the same server and with the same data set (as you said) does not provide the same execution plan.

Here are some of the reasons why the query plan may be different:

  • The mdf and ldf files (for each database) are located on different disks. If one drive is faster, this database will run the query faster.
  • statistics stalled. If you have one database with newer statistics than another, SQL has a better chance of choosing the right one (and faster).
  • Indexes: I know you said that they are both the same, but I would check if you have the same types of indexes on both.

Focus on why the query is slow, or instead of the actual execution plan, instead of comparing. Checking the actual execution plan for a slow query will give you an idea of ​​why it is slower.

Also, I would not add a NO LOCK statement to fix this problem. In my experience, most slow queries can be tuned using a code or index, instead of adding NO LOCK hints, which can get modified or old result sets, depending on your transactions.

+1
source

The best way is to rebuild and 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')
0
source

More articles:


All Articles