October 10, 2012 at 11:50 pm
Hi champs,
Stuck with a baffling linked server problem. would greatly appreciate an explanation for the below behaviour.
I have a SQL Server ServerA on which is created a linked server ServerB (both being SQL Server 2008 SP1 X64)
When I run the below query on ServerA, providing a literal in the WHERE clause, it returns results in ~11 seconds,
with 1,648,169 rows poulated in the table TableA, whose size becomes ~211 MB
IF OBJECT_ID('dbo.TableA') IS NOT NULL
DROP TABLE dbo.TableA;
GO
SELECT portfolio_id --int
,portfolio_name --varchar(100)
,product_type --varchar(50)
,mfg_id --int
,source_symbol --varchar(50)
,aggregate_symbol --char(20)
,book --varchar(50)
,pc --float
,loss_piece_no --varchar(50)
INTO dbo.TableA
FROM ServerB.DatabaseB.dbo.ViewB
WHERE business_date = '20120731'
But, If I run the same query above replacing the literal '20120731' with datetime variable @business_date, the query goes on and on.. Ran out of patience before the query could run completely.
IF OBJECT_ID('dbo.TableA') IS NOT NULL
DROP TABLE dbo.TableA;
GO
DECLARE @business_date DATETIME = '20120731';
SELECT portfolio_id --int
,portfolio_name --varchar(100)
,product_type --varchar(50)
,mfg_id --int
,source_symbol --varchar(50)
,aggregate_symbol --char(20)
,book --varchar(50)
,pc --float
,loss_piece_no --varchar(50)
INTO dbo.TableA
FROM ServerB.DatabaseB.dbo.ViewB
WHERE business_date = @business_date
From the plans I can see that the predicate is being applied at the remote server, but in the case of the former the query on the remote server reads : ....WHERE business_date = convert(datetime,'2012-07-31T00:00:00'
while the Estimated Plan of the 2nd query shows : WHERE business_date = ?
The ViewB on the remote ServerB has two tables with outer join on their respective business_date columns and another column. Both these tables have indexes which have business_date as the first column.
When the second query is executing, I see an OLEDB wait on the local server and a SOS_SCHEDULER_YIELD on the remote server.
October 11, 2012 at 2:34 am
I have understood your problem and I have faced it a lot of times and each time the solution has been depending upon the need. Let me spend sometime testing on it after my lunch and I will get back to you shortly.
Chandan
October 11, 2012 at 4:24 am
OK. so this is how it is. When you hardcode a value in the query using linked server, you can find the correct filter applied at the remote server and you happily get the results.
But when a variable gets involved which can be dynamic, it cannot be used in the linked server straightforward. The trick is to make a query first using that variable value and then pass it to the engine rather than directly using the variable in a linked server syntax. for example:
DECLARE @Sql VARCHAR(8000)
declare @lastactivitydate nvarchar(30);
select @lastactivitydate='2012-10-09';
SET @Sql = 'select * from Server.[Database].dbo.[vw_customers]
where lastactivitydate= '+@lastactivitydate
--select @sql
EXEC(@Sql)
You will have to use dynamic SQL here like I showed above. You will end up sometimes wondering about undefined variables, some syntax errors but they will fade away if you play with the quotes near the variable. The above case works for me because the column 'lastactivitydate' in the remote server is an integer. If that was a character value I would have played with the quotes.
note that there is a commented line 'select @sql'
I usually comment the execute part first and uncomment this portion to first know what statement is generated and I try to execute that individually. That helps when you get syntax errors and want to fix them.
Try this method and let me know if this works.
October 11, 2012 at 4:35 am
The filter is being applied locally. You could use EXEC with dynamic SQL as Chandan suggests; I'd personally use OPENQUERY, which will also require your statement to be constructed as a string (OPENQUERY doesn't accept variables).
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 11, 2012 at 4:39 am
ChrisM@home (10/11/2012)
The filter is being applied locally. You could use EXEC with dynamic SQL as Chandan suggests; I'd personally use OPENQUERY, which will also require your statement to be constructed as a string (OPENQUERY doesn't accept variables).
I agree with you. I prefer openquery more but I did not want to make things more complex and wanted to retain the same version with what Yusuf came up with.
Chandan
October 11, 2012 at 5:54 am
Thanks Chandan and Chris,
Have already tried the OPENQUERY and the recent EXECUTE AT remoteserver feature (latter having the much needed ability to pass parameters) and both work wonderfully well for this query.
The thing that amazes me is that this performance problem is not consistent. At times, it goes through and intermittently it does not.
I am unable to explain to my users why a thing that was working in the past (4 part query with the variable) does not work now, and why the code change is required.
October 11, 2012 at 7:17 am
Yusuf Ali Bhiwandiwala (10/11/2012)
Thanks Chandan and Chris,Have already tried the OPENQUERY and the recent EXECUTE AT remoteserver feature (latter having the much needed ability to pass parameters) and both work wonderfully well for this query.
The thing that amazes me is that this performance problem is not consistent. At times, it goes through and intermittently it does not.
I am unable to explain to my users why a thing that was working in the past (4 part query with the variable) does not work now, and why the code change is required.
That could be due to network at times. You can consider replication if the things make too much of noise to have an identical copy at your end to be queried. With linked server you cannot do much at times.
But 1 thing you are doing rightly is to look what query is being executed at remote end if it has filters because often the query executed at linked server end does not carry filters with them causing a complete scan. You may also test importing the data locally in temp tables and then doing joins on them if required. So it all boils down to what you want vs what you can configure and how far!!!
Thanks
Chandan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply