Synonym SLOW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Synonym SLOW

Author

Topic

TallOne
Starting Member

49 Posts

Posted - 2008-11-14 : 10:16:44

Only in production I have a slow synonym. The object type is stored procedure and when called it takes about three minutes to run I'm calling it like this within a stored procedure...
EXEC dbo.SYN_SP_SyncTheData param1,param2,param3,param4
The synonym points to a stored procedure in another db on the same server instance. If I call the stored procedure explicitly it takes about 1 second to execute.
EXEC DBNAME.dbo.SyncTheData param1,param2,param3,param4
No problem there. I'm manually executing this in query analyzer to eliminate any security issues. Does anyone have any experience with this type of behavior? It's been driving me bonkers!!!!!!!!

TIA
TallOne

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 10:19:11

If the procedure you want to run always are on same database, why use synonym?

E 12°55'05.63"
N 56°04'39.26"

TallOne
Starting Member

49 Posts

Posted - 2008-11-14 : 11:20:05

I guess it's one of those "Nice to have" features. I use it throughout the code and only need to configure the syn in one place.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 11:24:07

So the synonym ís pointing to a "Common" database?
Where other maintenance routines are?`

Have you checked security right for synomym?

E 12°55'05.63"
N 56°04'39.26"

TallOne
Starting Member

49 Posts

Posted - 2008-11-14 : 13:14:49

Correct. For example db1,db2 and db3 all have the same synonym that points to synDB. My login is a domain account and I have added domain\Domain Users to all dbs.

pittsburghsteelersfan
Starting Member

4 Posts

Posted - 2008-12-18 : 09:31:46

I'm having the same issue...worked for days on this and have the same issue...although mine also has an issue *sometimes* even if I call it directly, e.g., otherdatabase.dbo.mytable...significant time lag issue...

Any insights here? Thanks a TON!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 09:35:22

Could be a case of parameter sniffing.
Try to recompile the stored procedure.

E 12°55'05.63"
N 56°04'39.26"

pittsburghsteelersfan
Starting Member

4 Posts

Posted - 2008-12-18 : 10:27:32

Tried the recompile...no impact...run 2 EXACT queries in Mgmt Studio, one using the Synonym DB and the other the "Common/Real" db, e.g.,
USE [SynDB]
select * from myTable

USE [CommonDB]
select * from myTable

and the SynDB (with the synonym pointing to the same CommonDB.dbo.myTable), and the execution plan is *nearly* ALWAYS slower (it doesn't matter what order I run them either)...it's almost as if it has to pull more data and/or isn't using the indexes...it's just plain weird and makes ZERO sense...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:07:11

The two queries are not identical.
One is running from local database (syndb) and one is run on target database.

A fair test would be this

USE MyDatabase
GO

select * from [SynDB]..myTable
select * from [CommonDB]..myTable

Also, is CommonDB placed on same server or on another server/instance? as MyDatabase?

E 12°55'05.63"
N 56°04'39.26"

pittsburghsteelersfan
Starting Member

4 Posts

Posted - 2008-12-18 : 11:23:29

running "select f1,f2,f3... from commondb.dbo.mytable" on production takes around 10 seconds...

running
use commondb
select f1,f2,f3... from mytable
takes <1 second

same server/instance...it is a WIDE table with about 350k records...it is recreated every night as a rollup/aggregation of a much larger set of tables w/ >100 million recs...used for web display...

the only common thing I can see is the different db...thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources