I am extracting large amount of data via linked server from VIEWS. I am using SQL Server 2012 and linked server is SQL Server 2008
My select statement is
SELECT * INTO MY_LOCAL_TABLE
FROM
( SELECT * FROM LINKEDSERVER.DB.TABLE.VIEW
WHERE DATE>'2012-01-01' AND ID IN (SELECT ID FROM MY_LOCAL_VIEW)
) Q
I am expecting 300K rows for nearly 700+ IDs. before it used to take couple of hours but now its take more than a 20 hr!!
Could you please suggest any alternative solution for this PAIN??
Very many thanks in advance!
SQL Server uses nested loop, hash, and merge joins. If a slow-performing query is using one join technique over another, you can try forcing a different join type. For example, if a query is using a hash join, you can force a nested loops join by using the LOOP join hint.
Problem (the bad) When SQL Server runs query with Linked Server, it will use the least optimal execution plan due to lack of knowledge of those remote tables. Meaning, your local SQL Server is clueless on remote table indexes and statistics, so it might use incorrect joining mechanism and might be grossly inefficient.
When you use a 4-part name such as [server].db.dbo.table
, especially in a join
, often times the entire table is copied over the wire to the local machine, which is obviously not ideal.
A better approach is to use an OPENQUERY
-- which is handled at the source (linked server).
Try:
SELECT * FROM OPENQUERY([LINKEDSERVER], 'SELECT * FROM DB.TABLE.VIEW WHERE DATE>'2012-01-01') AND ID IN (SELECT ID FROM MY_LOCAL_VIEW)
With this approach the linked server will return all rows for date > x, and then the local server will filter that by ID's in your local table.
Of course, indexing will still play a factor for doing SELECT * FROM DB.TABLE.VIEW WHERE DATE>'2012-01-01
.
Another approach, which I use on large subsets, is to dump the local ID's to the remote server, THEN handle it all remotely, such as:
-- copy local table to linked server by executing remote query DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT ID INTO db.dbo.tmpTable FROM [SERVER].DB.DBO.MY_LOCAL_VIEW' EXEC(@SQL) AT [LINKEDSERVER] -- index remote table?!? DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'CREATE INDEX [IXTMP] ON db.dbo.tmpTable (ID)' EXEC(@SQL) AT [LINKEDSERVER] -- run query on local machine against both remote tables SELECT * -- INTO sometable FROM OPENQUERY([LINKEDSERVER], 'SELECT * FROM DB.TABLE.VIEW WHERE DATE>''2012-01-01'' AND ID IN (SELECT ID FROM db.dbo.tmpTable)') -- now drop remote temp table of id's DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'DROP TABLE db.dbo.tmpTable' EXEC(@SQL) AT [LINKEDSERVER]
If the local view is also large, then you may consider executing a remote query that uses an openquery back to the local machine (assuming the remote machine has the local as a link).
-- copy local table to linked server by executing remote query DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT ID INTO db.dbo.tmpTable FROM OPENQUERY([SERVER], ''SELECT ID FROM DB.DBO.MY_LOCAL_VIEW'')' EXEC(@SQL) AT [LINKEDSERVER]
Others have already suggested about indexing. So I am not going there. suggest another option, if you could change that inner query
SELECT * FROM LINKEDSERVER.DB.TABLE.VIEW
WHERE DATE>'2012-01-01' AND ID IN (SELECT ID FROM MY_LOCAL_VIEW)
To a joined
query using inner join
since you said having 700+ inlist elements. give it a try.
SELECT lnv.* FROM LINKEDSERVER.DB.TABLE.VIEW lnv
inner join MY_LOCAL_VIEW mcv
on lnv.ID = mcv.ID
and lnv.DATE > '2012-01-01'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With