October 16, 2008 at 12:40 pm
QUERY1:
select surname from dbo.V_Index where id = 'A000001'
QUERY2:
exec dbo.GET_SURNAME 'A000001'
QUERY1 retrieves the result in 0 ms
QUERY2 retrieves the result in 12000 ms
Anybody can help me understand why sproc works slower.
INFORMATION:
VIEW1: [V_Index]
select * from linkedserver1.db.dbo.index
SPROC: [GET_SURNAME]
create procedure [dbo].[GET_SURNAME]
@_id char(7)
AS
select surname from dbo.index where id = @_id
GO
[linkedserver1]
Database: db
VIEW2: index (Select * from linkedserver2...index)
October 16, 2008 at 1:33 pm
We noticed something similar in attempting to do cross server queries with views. I believe the difference comes in because doing it one way will pull the entire dataset over, and then filter it on the local server, where as the other method filters it remotely and pulls back just what's needed.
I'd be curious to hear some more definitive ways of avoiding this or enforcing where the filtering takes places myself.
October 16, 2008 at 2:48 pm
Hi Garadin,
Thank you for your message.
I investigated these two querries using "Profiler". I am getting exactly same results except the time.
I could not develop any solution.
Please let me know if you can find any useful material.
Regards,
/Sel
October 16, 2008 at 3:21 pm
You may be running into parameter sniffing.
This is a pretty decent article explaining what it is and how to avoid it:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 16, 2008 at 5:35 pm
Hi Matt,
It is an interesting article thanks for the info.
/Sel
October 17, 2008 at 5:46 am
Do the execution plans change between the two?
I agree with Matt. It's probably parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2008 at 1:16 pm
Hi,
I really like the article "Parameter Sniffing & Stored Procedures Execution Plan" yet I have got the same timing result when I modify the sproc with local variable.
My guess is:
Query to views which are based on linkedservers
1) direct query to view:
runs the entire query in the remote server and executes the where clause remotely
2) executing stored procedure by passing a parameter or a local variable
runs the select part on the remote, retrieves all the result set to the local where we are executing the sproc, and then filters out the where clause locally.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply