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

SQL Server Linked Server Distributed Query with Parameter vs Literal Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 11:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:09 AM
Points: 50, Visits: 352
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.
Post #1371220
Posted Thursday, October 11, 2012 2:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 459, Visits: 1,886
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
Post #1371287
Posted Thursday, October 11, 2012 4:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 459, Visits: 1,886

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.



Post #1371335
Posted Thursday, October 11, 2012 4:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 1,072, Visits: 6,336
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).


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1371336
Posted Thursday, October 11, 2012 4:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 459, Visits: 1,886
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
Post #1371341
Posted Thursday, October 11, 2012 5:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:09 AM
Points: 50, Visits: 352
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.
Post #1371391
Posted Thursday, October 11, 2012 7:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 459, Visits: 1,886
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
Post #1371456
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse