SPROC works way slower than SELECT statement

Question

SPROC works way slower than SELECT statement

day-476284

SSCommitted

Points: 1844
More actions
October 16, 2008 at 12:40 pm

#194695

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)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

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.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

day-476284 SSCommitted Points: 1844 More actions · Answer 2

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

day-476284 SSCommitted Points: 1844 More actions · Answer 4

Hi Matt,

It is an interesting article thanks for the info.

/Sel

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

day-476284 SSCommitted Points: 1844 More actions · Answer 6

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.