Help with new Synonym feature

I have found what I think is a good use for synonyms. However there's a
BUT I'm hoping someone can help me with.
I have several Great Plains databases for each of our overseas
operations. I use replication to pull these tables into one database
but I put them in seperate schemas, i.e. UK.IV00101, US.IV00101,
NO.IV00101.
I have various queries of the form;
If @.Subsidiary = 'UK'
Select * From UK.MyTable
If @.Subsidiary = 'US'
Select * From US.MyTable
If @.Subsidiary = 'NO'
Select * From NO.MyTable
or I have to use dynamic SQL or duplicate SP's for each subsidiary.
Now with synonyms I can do;
Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
Exec (@.SQL)
Select * From xMyTable
Drop Synonym xMyTable
Obviously in the real world we are talking about quite a few tables and
far more complex select statements, so this method seems to give some
performance advantage and a much neater script.
The downside is that I have to watch out for the script being run twice
and "cross-using" the same synonym. Now I could wrap it in a
transaction but this is purely for reporting so I'm trying to avoid
adding locking overheads.
What I'm after is "best practice" in this situation and comments on any
performance implications to what I'm trying.
What would have been nice would be a form of temporary synonym in the
same vain as temporary tables, i.e. valid just for that session and
valid only to that session.
Documentation on Synonyms is a little thin in BOL. Merely telling you
how to use rather than discussions on performance and what it's purpose
was for (which is ironic as I always complain that MS usually tell you
what something can do but not how!)
Regards
Chris> Now with synonyms I can do;
> Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
> Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
> Exec (@.SQL)
> Select * From xMyTable
> Drop Synonym xMyTable
> Obviously in the real world we are talking about quite a few tables and
> far more complex select statements, so this method seems to give some
> performance advantage and a much neater script.
> The downside is that I have to watch out for the script being run twice
> and "cross-using" the same synonym. Now I could wrap it in a
> transaction but this is purely for reporting so I'm trying to avoid
> adding locking overheads.
Then don't use the SYNONYM at all, just say:
EXEC ('SELECT col_list FROM '+@.Subsidiary+'.MyTable')

> What I'm after is "best practice" in this situation
Dynamic SQL, in any flavor, is not best practice. To achieve best practice,
you will likely have to redesign, e.g. store all the data in a single table
and put things like 'NO' and 'US' in a column. Since these seem to be the
only variants of the data, there doesn't seem to be a compelling reason to
complicate all access to the data by splitting them out into umpteem tables.
The naming scheme "IV00101" seems suspect too, are there also tables like
IV00102, IV00103, etc.?
A|||Create a union view:-
CREATE VIEW dbo.ALL_MyTable AS
SELECT 'UK' AS Subsidiary , field1, field2, etc FROM UK.MyTable
UNION ALL SELECT 'US' AS Subsidiary , field1, field2, etc FROM US.MyTable
UNION ALL SELECT 'NO' AS Subsidiary , field1, field2, etc FROM NO.MyTable
Then you can filter using
SELECT * FROM dbo.ALL_MyTable WHERE Subsidiary IN( 'UK' , 'US' )
"Chris McGuigan" <mr.mcgoo@.gmail.com> wrote in message
news:e00UtwR6FHA.4076@.tk2msftngp13.phx.gbl...
> I have found what I think is a good use for synonyms. However there's a
> BUT I'm hoping someone can help me with.
> I have several Great Plains databases for each of our overseas
> operations. I use replication to pull these tables into one database
> but I put them in seperate schemas, i.e. UK.IV00101, US.IV00101,
> NO.IV00101.
> I have various queries of the form;
> If @.Subsidiary = 'UK'
> Select * From UK.MyTable
> If @.Subsidiary = 'US'
> Select * From US.MyTable
> If @.Subsidiary = 'NO'
> Select * From NO.MyTable
> or I have to use dynamic SQL or duplicate SP's for each subsidiary.
> Now with synonyms I can do;
> Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
> Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
> Exec (@.SQL)
> Select * From xMyTable
> Drop Synonym xMyTable
> Obviously in the real world we are talking about quite a few tables and
> far more complex select statements, so this method seems to give some
> performance advantage and a much neater script.
> The downside is that I have to watch out for the script being run twice
> and "cross-using" the same synonym. Now I could wrap it in a
> transaction but this is purely for reporting so I'm trying to avoid
> adding locking overheads.
> What I'm after is "best practice" in this situation and comments on any
> performance implications to what I'm trying.
> What would have been nice would be a form of temporary synonym in the
> same vain as temporary tables, i.e. valid just for that session and
> valid only to that session.
> Documentation on Synonyms is a little thin in BOL. Merely telling you
> how to use rather than discussions on performance and what it's purpose
> was for (which is ironic as I always complain that MS usually tell you
> what something can do but not how!)
> --
> Regards
> Chris|||Chris McGuigan (mr.mcgoo@.gmail.com) writes:
> I have several Great Plains databases for each of our overseas
> operations. I use replication to pull these tables into one database
> but I put them in seperate schemas, i.e. UK.IV00101, US.IV00101,
> NO.IV00101.
> ...
> Now with synonyms I can do;
> Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
> Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
> Exec (@.SQL)
> Select * From xMyTable
> Drop Synonym xMyTable
> Obviously in the real world we are talking about quite a few tables and
> far more complex select statements, so this method seems to give some
> performance advantage and a much neater script.
> The downside is that I have to watch out for the script being run twice
> and "cross-using" the same synonym. Now I could wrap it in a
> transaction but this is purely for reporting so I'm trying to avoid
> adding locking overheads.
>...
> What would have been nice would be a form of temporary synonym in the
> same vain as temporary tables, i.e. valid just for that session and
> valid only to that session.
That would indeed be a nice feature, but it falls on the same reason
as "SELECT * FROM @.tablename" - each table has unique set of statistics,
and needs to be compiled separately.
Had this been your own database, best practice had been as Aaron says,
make the subsidary a column in the table. I understand that with the
given setup, this is not really palatable. (Although I believe that it
is possible to set up replication to fill in the column for you. It may
not be worth the hassle though.)
So I agree with Rebecca York's suggestion: set up views that includes
all data. Be careful to use UNION ALL, then the optimzer should generate
a plan, so that if you have "WHERE subsidary = 'NO'", no other tables
are accessed. (Rebecca had UNION ALL in her post, but I wanted to stress
this point.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.
The Exec is not an option, the real query is quite complex so currently
I have 4 sp's, one for each subsidiary. I do try to avoid dynamic SQL.
Ultimately, I will merge the 4 tables into one table but I have time
constraints and haven't worked out the best way to achieve this yet.
Synonyms looked like it could improve my code but delving deeper, don't
seem so useful.
The table naming is to do with Great Plains now a Microsoft product.
Regards
Chris
Aaron Bertrand [SQL Server MVP] wrote:

> Then don't use the SYNONYM at all, just say:
> EXEC ('SELECT col_list FROM '+@.Subsidiary+'.MyTable')
>
> Dynamic SQL, in any flavor, is not best practice. To achieve best
> practice, you will likely have to redesign, e.g. store all the data
> in a single table and put things like 'NO' and 'US' in a column.
> Since these seem to be the only variants of the data, there doesn't
> seem to be a compelling reason to complicate all access to the data
> by splitting them out into umpteem tables. The naming scheme
> "IV00101" seems suspect too, are there also tables like IV00102,
> IV00103, etc.?
> A|||Thanks Rebecca,
I hadn't thought of this as some of the tables can be relatively large
I assumed rightly or wrongly that I may get performance issues.
I'll look into this further.
Regards
Chris
Rebecca York wrote:
> Create a union view:-
> CREATE VIEW dbo.ALL_MyTable AS
> SELECT 'UK' AS Subsidiary , field1, field2, etc FROM UK.MyTable
> UNION ALL SELECT 'US' AS Subsidiary , field1, field2, etc FROM
> US.MyTable UNION ALL SELECT 'NO' AS Subsidiary , field1, field2, etc
> FROM NO.MyTable
> Then you can filter using
> SELECT * FROM dbo.ALL_MyTable WHERE Subsidiary IN( 'UK' , 'US' )
>
> "Chris McGuigan" <mr.mcgoo@.gmail.com> wrote in message
> news:e00UtwR6FHA.4076@.tk2msftngp13.phx.gbl...|||Thanks Erland,
I am aware of the Union All optimizer and always use it unless I
specifically don't want it.
I understand where you are coming from on the statistics.
So that leads me to wonder what purpose Synonyms can have other than to
give friendly names!
--
Regards
Chris
Erland Sommarskog wrote:

> Chris McGuigan (mr.mcgoo@.gmail.com) writes:
> That would indeed be a nice feature, but it falls on the same reason
> as "SELECT * FROM @.tablename" - each table has unique set of
> statistics, and needs to be compiled separately.
> Had this been your own database, best practice had been as Aaron says,
> make the subsidary a column in the table. I understand that with the
> given setup, this is not really palatable. (Although I believe that it
> is possible to set up replication to fill in the column for you. It
> may not be worth the hassle though.)
> So I agree with Rebecca York's suggestion: set up views that includes
> all data. Be careful to use UNION ALL, then the optimzer should
> generate a plan, so that if you have "WHERE subsidary = 'NO'", no
> other tables are accessed. (Rebecca had UNION ALL in her post, but I
> wanted to stress this point.)|||Chris McGuigan (mr.mcgoo@.gmail.com) writes:
> So that leads me to wonder what purpose Synonyms can have other than to
> give friendly names!
I will have to admit that is also the purpose I can see.
Well, I can see a few more scenarios. Here is one example: say that you
have an application that uses several databases. For all cross-database
access, you would set up synonyms. The idea is that if you need to set
up a second environment on the same server, you can achieve this by
updating all synonyms, but leaving the stored procedures untouched.
But for this purpose it would be more interesting to set up a synonym
for a database - which you can't.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx