October 20, 2007 at 5:33 am
Hi,
I am ok with the concept you have built but the only problem I am looking is a view.If we are working with the amall database then its ok but whai I know is if we are using view with huge amount of data, It becomes slow.
October 20, 2007 at 8:25 am
Not sure I agee about linked servers always being slower... I've found that it depends on the number of hops it has to make... I've got several linked servers at work and a high volume app that reads from the "main" server... the ones that are "local", (basically, same rack) operate with the same access speeds as if done on the main. The one that's 550 miles away takes about 3 times longer to do the same thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2007 at 6:55 pm
I used linked server and it was slower due to network problem, maybe just the network engineers in the company were not that good. I used OPENROWSET all the time in my stored procedures but we used window authentication so I did not need to put sign on id and password.
October 22, 2007 at 6:52 am
I am against OPENROWSET/OPENDATASOURCE. If you change something in your topology (network name,database name,login/password) it is a nightmare to change your code. Also, it is least secure method, because you have to encode security information into your SP or save it elsewhere.
For me, best way - use link server.
Now, if you know for sure, database names will not be changed ever, you can encode 4 part names directly.
But, because this is unlike situation, I recommend using views/synonyms.
Also consider use of openquery for very complex queries.
For example:
select * FROM [LINKSERVER].DB.DBO.T1 LEFT JOIN [LINKSERVER].DB.[DBO].T2
Whould be much faster in
select * from openquery(LINKSERVER,'select * FROM DBO.T1 LEFT JOIN [DBO].T2')
Brandie Tarvin (10/19/2007)
I agree that if you're using databases on the same server that a view definitely seems to be the way to go, but...Even if you use a view to query Subscription from Orders, if either database is moved to another server, you're still going to have to in and edit the view to correct it. And, unless you decide to use OpenRowSet / OpenDataSource, you're still going to have to use Linked Servers.
So, if you're in a scenario where you have 2 different servers and 2 different dbs, one on each server, which option do you go for? Creating a view with a Linked Server 4 dot name in it or creating the actual query with the Linked Server 4 dot name or OpenRowSet / OpenDataSource?
I haven't actually played much with the OpenRowSet / OpenDataSource stuff yet. And I happen to be working on a major reporting product which does indeed require me to connect to multiple servers and their databases, so I'd like to see what thoughts everyone has on the issue.
October 22, 2007 at 7:00 am
I had a query where I was pulling directly from the linked server (multiple tables in JOINs) with a SELECT statement on the destination server. After reading Steve's article, I decided to try a view. I created a non-indexed view with all the JOINS on the source server, then on the destination server, did a SELECT . This cut my query time down from 15+ minutes to less than 5 minutes.
WOW... I thought it would take about the same amount of time, but it really did all the "work" on the source server and then just pulled back the results rather than pulling everything down to the destination and doing the work there. At least, that what it looks like to me.
Definitely sold on views now. @=)
October 24, 2007 at 3:49 am
to have the remote data synchronised with local data anytime, what about using trigger in remote server's table to update the local table , of course using linked server in trigger. Is that possible ,if so since the linked server reference is just for a record, it should be fast
October 24, 2007 at 3:56 am
try this by learning..
http://www.sqlservercentral.com/articles/Advanced/designingcrossdatabasequeries/1753/
October 24, 2007 at 7:23 am
hey okie_greg
You made one of the two points that i was going to make.
My second point was concerning the collation sequence.
If the two databases have different collation sequences then you have to set the collation sequence on the "where" or "join on" part of the select statement so that you are comparing apples to apples.
October 24, 2007 at 4:29 pm
Putting any synchronous process with a component that will run outside of the local SQL Server itself (i.e. pretty much anything like email, linked server tasks, xp_cmdshell calls, etc.) into a trigger is usually a bad idea.
Even if it works fine for weeks, eventually it is likely to fail to return in a timely fashion, thus preventing your transaction from completing, and likely blocking other transactions.
May 16, 2008 at 5:24 pm
In SQL Server 2005, views using linked servers import the entire base table from the linked server into the local tempdb without any indexes. Your query filters are not applied until the data has been materialized on the local server. Unless your table is very small, this is incredibly inefficient.
Oddly enough, this function works correctly in SQL Server 2000. The query is passed to the linked server where your filter conditions are applied, and only the relevant records are passed back to the local server. Microsoft has acknowledged that this is a bug.
July 29, 2008 at 11:41 pm
Hi all
I have a problem related Cress DataBase Queries, I have a table T in two database DB1 and DB2 with same defination.
I want to refer that table T from an other database DB3 on depending some condition i have to select DB1 or DB2. I don't want the whole query in string. Is it there any why that i just set condition in FROM section.
Like
Select *
From
if(true)
DB1.T
else
DB2.T
Thanks.
July 30, 2008 at 4:32 am
The simplest way is to use 2 queries in an IF / ELSE statement.
IF X = 'TRUE'
Begin
Select * from TB1
End
ELSE
Begin
Select * from TB2
End
You can also use a CASE statement, but not really in the FROM clause.
Select Case When X = 'TRUE' Then TB1.MyCol
Else TB2.MyCol END as MyCol
from TB3
left outer join TB1
on TB3.MyID = TB1.MyID
left outer join TB2
on TB3.MyID = TB2.MyID
The thing about the above example is that you really can't have the join between TB1 & TB2 unless they're going to return the same record set. Given your scenario, they usually don't. Which is why you need a table in common between them (TB3 which has all the IDs from both tables) and do a left outer join on both so you still have all your available records for the CASE to work properly.
So I'd stick with the IF statement and just use two queries that are similar.
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply