February 17, 2011 at 3:17 pm
I have 1 script that I can run on Server A where the data resides and it takes a fraction of a second to run. I then try and run it on Server B where Server A is linked and the exact same script runs and runs and runs for close to 5 minutes. Why does it take longer to run through a linked server?
Thanks
February 17, 2011 at 3:27 pm
Are all the tables in the script on the foreign server? Does the linked server user have the necessary security to see the statistics on the tables to create the same execution plan?
There are a number of reasons this can happen, but it comes down to a simple result. Instead of running foreignly as much as it can regarding where clauses and joins and the like, it ends up pumping the entire data tables over so the local system can use them.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 3:59 pm
There are ways to "tune" Linked Server queries by deciding between accessing remote data using either 4-part naming or OPENROWSET OPENQUERY. As Craig mentioned depending on how your query is structured a large amount of data may need to be transferred from the remote server to the local server so it can be joined to or filtered.
If you post your SQL code we might be able to make a recommendation to improve performance. Also useful would be DDL of the involved tables and the row counts for each table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 18, 2011 at 9:06 am
As I was trying to simplify the query for this post, I found the culprit. It is the case statement that is causing the time. Without the case statement, it works fine. So why is the case statement causing such a problem? Keep in mind, the case statement works fine when I am on the server where the data resides. It only causes a problem when run through a linked server.
select a.CustomerID, b.LineItemNumber,
sum(case when datepart(yyyy,InvoiceDate)=2010 and datepart(mm,InvoiceDate)<=1 then ShipQuantity*ItemShippingVolume else 0 end) YTD
from [SQLDB-02].[FSDBRP].dbo.FS_ARInvoiceHeader a inner join [SQLDB-02].[FSDBRP].dbo.FS_ARInvoiceLine b on a.ARInvoiceHeaderKey=b.ARInvoiceHeaderKey
inner join [SQLDB-02].[FSDBRP].dbo.FS_Item c on b.ItemKey=c.ItemKey
where
datepart(yyyy,InvoiceDate)>=2010
and datepart(mm,InvoiceDate)<=1
group by a.CustomerID, b.LineItemNumber
order by a.CustomerID, b.LineItemNumber
select a.CustomerID, b.LineItemNumber,
sum(ShipQuantity*ItemShippingVolume) YTD
from [SQLDB-02].[FSDBRP].dbo.FS_ARInvoiceHeader a inner join [SQLDB-02].[FSDBRP].dbo.FS_ARInvoiceLine b on a.ARInvoiceHeaderKey=b.ARInvoiceHeaderKey
inner join [SQLDB-02].[FSDBRP].dbo.FS_Item c on b.ItemKey=c.ItemKey
where
datepart(yyyy,InvoiceDate)>=2010
and datepart(mm,InvoiceDate)<=1
group by a.CustomerID, b.LineItemNumber
order by a.CustomerID, b.LineItemNumber
February 18, 2011 at 10:03 am
My suggestion, especially since you're only referencing remote tables, use OPENQUERY.
select * from OPENQUERY(SQLDB-02,
'select a.CustomerID, b.LineItemNumber,
sum(case when datepart(yyyy,InvoiceDate)=2010 and datepart(mm,InvoiceDate)<=1 then ShipQuantity*ItemShippingVolume else 0 end) YTD
from [FSDBRP].dbo.FS_ARInvoiceHeader a inner join [FSDBRP].dbo.FS_ARInvoiceLine b on a.ARInvoiceHeaderKey=b.ARInvoiceHeaderKey
inner join [FSDBRP].dbo.FS_Item c on b.ItemKey=c.ItemKey
where
datepart(yyyy,InvoiceDate)>=2010
and datepart(mm,InvoiceDate)<=1
group by a.CustomerID, b.LineItemNumber
order by a.CustomerID, b.LineItemNumber')
February 18, 2011 at 12:36 pm
Colleen nailed it, I'll just try to explain:
There are several threads here on SSC on Linked Sserver performance;one thread which stated that openquery against a linked server was faster for a linked server vs a query on your server using 4 partnaming conventions;
for example, select * from LinkedServer.Databasename.dbo.MillionRowTable where something=somethingelse
vs
SELECT *
FROM OPENQUERY(LinkedServer, 'select * from MillionRowTable where something=somethingelse');
the reason: the first query gets the million rows locally into temp, then does the filtering, where the openquery does the work on the linkedserver, and returns just the results.
similarly, if you are joining a local table to a linked table to get results, it can take forever, because the MillionRowTable on the linked server gets copied over the wire to temp db, THEN the join to your local table is performed.
You'll have to look at the specific data you are trying to get form the linked server and limit it BEFORE you join to it to prevent that from happening.
depending on the allowed latency, you might want to replicate the table from the source to get rid of the linked server.
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply