Search code examples
sqlsql-serverdatabaset-sqldatabase-performance

Query performance issue when executing from another database

I try to execute a query from another database, using both synonyms and a direct call like select * from [DB].[schema].[view] .

When I run the query on the original database it executes in 1 second. If I call the view from another database, no matter if I use synonyms or a call like "[DB].[schema].[view]" it take about 1.5 MINUTES to execute. Any idea what the problem might be?

USE DB
GO;
select * from schema.view //working fine : 1 second
GO;

use master
GO;
select * from db.schema.view //taking more than 1.5 minutes   
GO;
Solution

  • It seems it's a compatibility level problem. Thanks to this post: https://dba.stackexchange.com/questions/194612/poor-performance-when-calling-query-from-another-db-on-same-server.

    Curios it's that if compatibility level is 140 I have bad performance, but if I change the compatibility level to 100 everything works fine