sql-serverschemareplication

Schemabind a SQL view with a linked server

I have to index a view to query much faster, but this view has underlying view with a linked server that I cannot schema bind. Which I can't because it is in a linked server and don't have direct access to the linked server. Does replication will work? That underlying linked view have 1000 - 1700 rows that changes.

Haven't tried replication cause I don't want to mess up the database. How can copy/replicate this view so I can schema bind?

Solution

  • the linked server is not slow.

    Great! You've already eliminated one potential bottleneck.

    Instead of going to great lengths to materialize the data - which you can't do via an indexed view, since every object referenced by the view must exist within the same database as the view - and an indexed view is unlikely to be a magical fix anyway - you need to focus on finding the actual bottleneck.

    Look at the execution plans. Is the query running remote or bringing all the data local and then filtering? If the query joins linked server tables to local objects, compare the difference using REMOTE JOIN. You could also consider experimenting with materializing and indexing the data locally at runtime before joining, e.g.:

    SELECT [cols] INTO #foo FROM [linked_server]...;
CREATE INDEX whatever on #foo(...);
-- now join to #foo

    If the query doesn't join to local tables (and doesn't rely on local resolution of functions like DB_NAME(), OBJECT_NAME(), etc.), consider using OPENQUERY(), OPENROWSET(), EXECUTE AT, or sp_executesql against the linked server, e.g.:

    EXEC [linked_server].database_name.sys.sp_executesql N'SELECT 
     ...
     FROM {table(s) without server references}
     ...';

    This will force optimization and filtering to happen at the destination. This will also allow you (or someone with access) to take that exact query and tune it on the linked server directly to get better insight into index opportunities to make it perform better no matter where it runs. You might not have control over the linked server, but surely you can request better indexes to support your workloads. This arms those users with a lot more information than just saying "my linked server query is not performing well."

    You could also combine them, e.g.

    INSERT #foo(cols)
  EXEC [linked_server].database.sys.sp_executesql N'SELECT ...';

-- or

INSERT #foo(cols) SELECT cols FROM OPENROWSET(...);

    Some other answers that might potentially help (and a search for many more):

    And also check out this broader topic on Microsoft Learn: