Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server Optimization


Linked Server Optimization

Author
Message
Bill Richards-377350
Bill Richards-377350
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 395
Comments posted to this topic are about the item Linked Server Optimization
StaticFix
StaticFix
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 37
Nice article and I agree with you on the performance factors, but also would like to add that there are occasions like when you need real time data(read reports) from db located remotely, options are limited.

Like recently, I got an assignment of retrieving data from db residing in many sql express clients located remotely, infact nearly 100's of clients and need to pull data into a centrally located sql server for real time monitoring using data retrieved.

Any thoughts?
Jacob Luebbers
Jacob Luebbers
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 1215
Ouch - sounds like a horrible setup w00t

Are these remote SQL Express clients accessible via static IPs? Do you pull the data from the central SQL server or push from the clients? Unless these answer to these are "yes" and "pull" you'd probably be better off with merge replication from the clients.


Regards,

Jacob
StaticFix
StaticFix
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 37
Correct, horrible setup......

And right the first thought that came in my mind was to do merge replication (i am not actually a dba but .net dev) and learned sql express cannot be a publisher for merge replication and hence wil not work out.

Yes these SQL clients are accessible via statis IP's, the current setup is i have written SP's for the required report in centralised server and scheduled it to run every 5 min (latency of 10 min was approved, as app that updates the db in sql clients happens in a period of 5 min), ya so its kind of pulling from client.
Jacob Luebbers
Jacob Luebbers
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 1215
Ah yep, sorry SQL Express :-P I was reading MSDE...

If you can't change the architecture then minimise the amount of data transfered across the linked server boundary. If at all possible (as the main article mentions) avoid all joins between linked servers - I've found the best performance is when you use remote exec a proc on the remote server and insert the results into a table on the local side with INSERT... EXEC... as the query is entirely resolved on the remote side and the results only come back to the local server.

This of course requires the remote proc to be almost self-sufficient - I have no idea of your schema but if the remote clients use tables that share a common schema with the central server if may be possible to have a remote proc which takes a single parameter (eg max(primary key value) from the central table) and returns all rows from the remote (client) table that are higher than that value, or similar. Or even better, if you are inserting into seperate tables from each client and have your data partitioned by something simple like date, no parameters need to be passed at all.

Regards,

Jacob
StaticFix
StaticFix
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 37
Thanks for your suggestions Jacob....

Yes, you are right schema happens to be different for the clients (the application that writes into db is definitely not a scalabale one coz they cant figure out a generic structure), mainly because customer select the required fields to be monitored using the app. But still i will be checking this option of dumping (only latest/last inserted) records into tables in central server with a remote proc and later join the tables for the report.
Roi Assa
Roi Assa
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 153
You should understand what kind of data (and amount) you have in both servers (local and remote) when you're using linked server.
Sometimes you'd like to run the join on the remote server and "get" the result only (if local table is small and the join will eliminate records for example).

For this case use the REMOTE join hint.

From BOL:

"REMOTE:

Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table.

If the right table is local, the join is performed locally. If both tables are remote but from different data sources, REMOTE causes the join to be performed on the site of the right table. If both tables are remote tables from the same data source, REMOTE is not required.

REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.

REMOTE can be used only for INNER JOIN operations."

Roi Assa


Kindest Regards,

Roi Assa

FibRock
FibRock
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 257
Good article, I would have liked to seen the exploration of a linked server in a openrowset subquery where you can have the where clause logic run on the remote server thereby returning less rows to the SQL Server. I have always felt this was a good way to improve linked server query response but I have never tested it agianst the 4 part named query or the temp table.

Steve
http://stevefibich.net
http://utilitydb.codeplex.com/
brhunter-540485
brhunter-540485
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 131
I have, in the past, created views on the local server that contained the required remote data. Although you don't get quite the performance gain from copying the data down from the remote server, it allows you to maintain a granular security structure at the remote location, and you do acquire some performance gains over direct access. From a security standpoint, you only expose that data that needs to be exposed. One "gotchya" on this arrangement is that you now expose your local architecture to vulnerabilities dependent on the schema consistancy of the remote server(s). If someone changes the remote table schemas without notification / propegation of the changes, things on the local server . . . ah . . break.
k perkins
k perkins
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 190
Check out openquery() to access data on linked servers. It will return only the results of the query after processing the query on the remote server.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search