SPROC works way slower than SELECT statement

  • 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)

  • 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]

  • 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

  • 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?

  • Hi Matt,

    It is an interesting article thanks for the info.

    /Sel

  • 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

  • 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