Synonyms affect exeuction plan

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 2000 Forums

Transact-SQL (2000)

Synonyms affect exeuction plan

Author

Topic

BrianArmstrong
Starting Member

6 Posts

Posted - 2006-12-11 : 11:24:22

I have a situation where replacing a full server name with a synonym in an update statement causes a HUGE performance hit.

Say I have a Synonym:

CREATE SYNONYM symTbl FOR PRODSERVER1.MYDB.DBO.TBL

And these two queries:

UPDATE symTbl SET intField = 1 WHERE charField = 'Y'
UPDATE PRODSERVER1.MYDB.DBO.TBL SET intField = 1 WHERE charField = 'Y'

The first query runs MUCH, MUCH slower than the second. If you check the execution plan, you will see that the first query (using synonyms) issues a Remote Query that pulls the entire remote table over the network. The second query (using full server name) avoids this step entirely.

Is there any way I can rewrite my query to use synonyms but get the performance of using full server names? Or is this just a limitation of the way synonyms are implemented?

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-12-11 : 11:31:42

You could write this as a stored procedure then it will take no time at all...

CREATE PROCEDURE stp_UpdateSymTbl
@CharField char(xx),
@Intfield int
AS
UPDATE
symTbl
SET
inField = @int
WHERE
charField = @CharField
GO

Then just pass it the params.

"Impossible is Nothing"

BrianArmstrong
Starting Member

6 Posts

Posted - 2006-12-11 : 11:38:58

This query is being called from within a stored procedure. That doesn't change the execution plan - it still takes forever.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-11 : 12:31:00

sounds like you are passing the table name to another stored proc, and therefore are executing dynamic sql. No wonder it takes a lot longer. (just a shot in the dark)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

BrianArmstrong
Starting Member

6 Posts

Posted - 2006-12-11 : 12:47:46

I'm not using any dynamic SQL - synonyms are a new feature in SQL Server 2005. It allows you to create a database-wide alias for an object that lives on a linked server. That way, no more hard-coding server names into your stored procedures.

I'll edit the fp to make it clearer what I'm talking about.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-12-11 : 13:52:33

The optimizer does not recognize that the synonym points to a remote object, so it doesn't run it as an RPC. It's not likely to be fixed (if it even can be) so you're stuck using 4 part names to get the best performance.

I'd suggest using stored procedures for all your remote calls, something like:

EXEC PRODSERVER1.MYDB.DBO.ChangeInt @intField = 1, @charField = 'Y'

BrianArmstrong
Starting Member

6 Posts

Posted - 2006-12-11 : 14:19:38

Thanks robvolk - that's what I was afraid of. Unfortunately my actual UPDATE is being done on the result of a cross-server join, so the stored proc interface would be a little unwieldy. I guess I'll stick with 4-part names for now.

Synonyms are great, though, for stored procs and tables you aren't going to run updates against!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-12-11 : 17:57:49

My experience with cross-server joins has been: don't. Avoid as much as possible. You cannot get adequate performance with cross-server joins unless you have miniscule data sets. If you profile it, you'll see that the join gets broken into sp_cursorfetch calls, one for each remote row returned.

I usually create stored procs on the linked server that can accept a variable(s) and returns only the data I need. I'll insert that into a temp table on the local server and do the joins there. As far as updating remote data, I do something similar: a procedure on the linked server that accepts parameters and does the update/delete/insert on that server.

The thing is, if these 2 databases really need to talk to each other that much, they should be on the same server.

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 01:47:57

I do the same as Rob. Pull the relevant remote data locally, work out a dataset to update based on that, push that data to the remote server, and then call an Sproc on the remote server to actually do the update.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources