SP w/Linked Server parameter issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP w/Linked Server parameter issue

Author

Topic

reidkell
Starting Member

16 Posts

Posted - 2010-03-25 : 16:17:02

Hi, all. My first post. Am desperate to solve this problem. I'm using SQL 2000 and have the following stored proc, with a join to a linked server.

CREATE PROCEDURE dbo.usp_WA_Login_SessionValues
(@EntryID Varchar(15))
AS
SELECT u.UserID
,u.uID
,c.CompanyName
,u.EmailAddress
FROM SRP..tblUser u
Join CSN65NT.ARSInterface.dbo.SD_ContactInformation ci --Linked Server
ON u.EMailAddress COLLATE DataBase_Default = ci.Email_Address
Join SRP..tblDept d
ON u.DeptID = d.DeptID
Join SRP..tblDivision di
ON d.DivisionID = di.DivisionID
Join SRP..tblCompany c
ON di.CompanyID = c.CompanyID
WHERE ci.Entry_ID = @EntryID
AND u.[UserActive?]=1

When calling the proc...
EXEC usp_WA_Login_SessionValues 'NWDC00000016815'
...it takes anywhere from 28-35 seconds! This is causing huge timeout issues for our web app.

However, if I just execute the SELECT statement without using a variable...

SELECT u.UserID
,u.uID
,c.CompanyName
,u.EmailAddress
FROM SRP..tblUser u
Join CSN65NT.ARSInterface.dbo.SD_ContactInformation ci --Linked Server
ON u.EMailAddress COLLATE DataBase_Default = ci.Email_Address
Join SRP..tblDept d
ON u.DeptID = d.DeptID
Join SRP..tblDivision di
ON d.DivisionID = di.DivisionID
Join SRP..tblCompany c
ON di.CompanyID = c.CompanyID
WHERE ci.Entry_ID = 'NWDC00000016815'
AND u.[UserActive?]=1

...it runs in less than a second.

Even if I remove the Stored Proc from the equation and simply declare the variable, set it, then use it in the WHERE clause, it takes a long time to run.

My question is, how does the inclusion of the variable cause such performance degradation? And since I must use a stored proc, what can I do to fix the problem?

Any help is greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-25 : 16:43:50

This could be parameter sniffing. Do some research on that subject and see what solution works for you. You typically just add extra variables to the stored procedure and set the input variables to those new ones. I think it's pretty ridiculous that this parameter sniffing phenomenon exists and that Microsoft should fix it.

Another possible reason is a bad plan in cache. Check the plan, recompile the stored procedure, and then check the plan again. If the two plans are different and the second one is better, then it was a bad plan issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

reidkell
Starting Member

16 Posts

Posted - 2010-03-25 : 19:41:31

Thanks for the advice. I had high hopes for the simple Parameter Sniffing fix, but that didn't do it. I am still stumped. Check this out (simplified):

This query, with a hardcoded value, executes in less than a second. Perfect.

SELECT u.UserID
FROM SRP..tblUser u
Join CSN65NT.ARSInterface.dbo.SD_ContactInformation ci
ON u.EMailAddress COLLATE DataBase_Default = ci.Email_Address
WHERE ci.Entry_ID = 'NWDC00000016815'
AND u.[UserActive?]=1

However, simply introducing a declared variable, no stored proc in sight, takes 20+ seconds:

DECLARE @MyID as Varchar(15)
SET @MyID = 'NWDC00000016815'

SELECT u.UserID
FROM SRP..tblUser u
Join CSN65NT.ARSInterface.dbo.SD_ContactInformation ci
ON u.EMailAddress COLLATE DataBase_Default = ci.Email_Address
WHERE ci.Entry_ID = @MyID
AND u.[UserActive?]=1

Not sure what to say. For what it's worth, I ran the above with SET STATISTICS PROFILE ON and got the following for Remote Query...

First (fast) query:
(SELECT ci."Email_Address" "Col1004"
FROM "ARSInterface"."dbo"."SD_ContactInformation" ci
WHERE ci."Entry_ID"=N'NWDC00000016815'))

Second (slow) query:
(SELECT ci."Email_Address" "Col1005",ci."Entry_ID" "Col1006"
FROM "ARSInterface"."dbo"."SD_ContactInformation" ci))

The good news is, I *did* find a workaround solution, but I don't like it. Executing the whole thing using Dynamic SQL works great. I would still like to solve the parameter problem if anyone has any further input. Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-25 : 20:34:42

Sounds like a bad plan issue then. Compare the the execution plans of both the slow and fast ones.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-03-25 : 22:43:55

I think the problem is how SQL Server is sending the query to the linked server. When you use a variable, SQL Server is pulling all of the data across and then applying the filter.

You can try changing the linked server property Collation Compatible to true and see if that helps. I don't think it will, but you can try it to find out.

The other option is to build a stored procedure on the other system and execute that procedure with a passed parameter. You would put the results in a temp table, then join to the temp table in your final query.

reidkell
Starting Member

16 Posts

Posted - 2010-03-25 : 22:55:52

Collation Compatible setting didn't help, but I like your idea about code on the linked server side itself. I'm going to explore that. Many thanks, all.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-03-26 : 22:08:55

Yeah - didn't think it would, but worth a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources