Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Linked Server Optimization Expand / Collapse
Author
Message
Posted Thursday, March 6, 2008 10:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 5:29 AM
Points: 40, Visits: 380
Comments posted to this topic are about the item Linked Server Optimization
Post #465641
Posted Thursday, March 6, 2008 11:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:43 AM
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?
Post #465646
Posted Thursday, March 6, 2008 11:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
Ouch - sounds like a horrible setup

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
Post #465647
Posted Thursday, March 6, 2008 11:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:43 AM
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.

Post #465649
Posted Friday, March 7, 2008 1:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
Ah yep, sorry SQL Express 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
Post #465660
Posted Friday, March 7, 2008 2:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:43 AM
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.
Post #465669
Posted Friday, March 7, 2008 5:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 1, 2014 1:54 AM
Points: 29, 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

Post #465762
Posted Friday, March 7, 2008 6:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:43 PM
Points: 222, Visits: 242
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/
Post #465775
Posted Friday, March 7, 2008 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:13 AM
Points: 4, Visits: 106
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.
Post #465779
Posted Friday, March 7, 2008 7:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 2, 2013 9:05 PM
Points: 142, 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.
Post #465812
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse