1

I have created a query which performs with aprox 2 seconds with top 100. If i create a stored procedure of this exact query it takes 12-13 seconds to run.

Why would that be?

  • Elements table count = 2309015 (with userid specified = 326969)
  • Matches table count = 1290 (with userid specified = 498)
  • sites table count = 71 (with userid specified = 9)

code

 with search (elementid, siteid, title, description, site, link, addeddate)
 as
 (
     select top(@top) 
         elementid,
         elements.siteid, title, elements.description,
         site =
             case sites.description
                 when '' then sites.name
                 when null then sites.name
                 else sites.name + ' (' + sites.description + ')'
             end, 
        elements.link,
        elements.addeddate 
    from elements
        left join sites on elements.siteid = sites.siteid
    where title like @search and sites.userid = @userid
    order by addeddate desc
)
select search.*, isnull(matches.elementid,0) as ismatch 
from search
    left join matches on matches.elementid = search.elementid
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
Bjørn
  • 1,138
  • 2
  • 16
  • 47
  • you have looked the query execution plan? – Hamlet Hakobyan Nov 22 '12 at 20:08
  • "I have created a query which performs with aprox 2 seconds with top 100. If i create a stored procedure of this exact query it takes 12-13 seconds to run".It follows,that the stored procedure so much slower than query. – Hamlet Hakobyan Nov 22 '12 at 20:11
  • @HamletHakobyan i do not understand, the sp is slow yes... (checking out the execution plan now) – Bjørn Nov 22 '12 at 20:12
  • There is one that has a cost, and it's the clustered index on elements table which is specified for the elementid. It seems like it's because of the Inner join.... This however does not explain why its so much slower while running inside a stored procedure... – Bjørn Nov 22 '12 at 20:16
  • You have some answers about why it's slower and how you can avoid that, please accept one of the answers or undelete your own answer and accept it. Or do you need more answers for this? – Roman Pekar Sep 23 '13 at 08:37
  • It is true that i have many answers, however non of them helped me in finding a solution to the problem, and i did not find a solution to the problem, i tried a whole bunch of other approaches to the problem. Currently the application is running with a solution that is so far from this question that there would be no point in typing it in here... There is a lot of general answers here which to me does not satisfy. Therefore its difficult for me to select an answer as "THE answer"... – Bjørn Sep 23 '13 at 12:44

4 Answers4

1

When you create SP it is compiled and stored and when the SP has parameters, by which you filter your result, the optimizer don't know which value you will pass on execution, then he treat as 33% selection and by this creates plan. When you execute query, the values are provided and optimizer create the execution plan depended on this values. I sure, the the plans are different.

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
0

Without code, I can only guess. When writing a sample query, you first have a constant where clause and second a cache. The stored procedure has no chance of either caching or optimizing the query plan based on a constant in the where clause.

alzaimar
  • 4,572
  • 1
  • 16
  • 30
  • I do not understand why there would be a difference. when running the stored procedure i run it like this: exec web_storedprocedure 1, 100, '%searchphrase%'. This is the one that is slow, the stored procedure, which does not have cached anything... i would have understood it if the query were the one that was slow.... – Bjørn Nov 22 '12 at 20:10
0

I can suggest two ways to try

First one, write your sp like this:

create procedure sp_search
(
    @top int,
    @search nvarchar(max),
    @userid int
)
as
begin
    declare @p_top int, @p_search nvarchar(max), @p_userid int

    select @p_top = @top, @p_search = @search, @p_userid = @userid

    with search (elementid, siteid, title, description, site, link, addeddate)
    as
    (
        select top(@p_top) 
            elementid,
            elements.siteid, title, elements.description,
            site =
             case sites.description
                 when '' then sites.name
                 when null then sites.name
                 else sites.name + ' (' + sites.description + ')'
             end, 
            elements.link,
            elements.addeddate 
        from elements
            left join sites on elements.siteid = sites.siteid
        where title like @p_search and sites.userid = @p_userid
        order by addeddate desc
    )
    select search.*, isnull(matches.elementid,0) as ismatch 
    from search
        left join matches on matches.elementid = search.elementid
end

Second one, use inline table function

create function sf_search
(
    @top int,
    @search nvarchar(max),
    @userid int
)
returns table
as
return
(
    with search (elementid, siteid, title, description, site, link, addeddate)
    as
    (
        select top(@top) 
            elementid,
            elements.siteid, title, elements.description,
            site =
             case sites.description
                 when '' then sites.name
                 when null then sites.name
                 else sites.name + ' (' + sites.description + ')'
             end, 
            elements.link,
            elements.addeddate 
        from elements
            left join sites on elements.siteid = sites.siteid
        where title like @search and sites.userid = @userid
        order by addeddate desc
    )
    select search.*, isnull(matches.elementid,0) as ismatch 
    from search
        left join matches on matches.elementid = search.elementid
)
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • I didn't think that parameter sniffing was generally advised? I've read many disadvantages from doing this (related to the sp you posted) – Ric Nov 22 '12 at 21:23
0

There is a similar question here

The problem was the stored proc declaration SET ANSI_NULLS OFF

Community
  • 1
  • 1
Spevy
  • 1,325
  • 9
  • 22