CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresview
Bjørn
Bjørn

Reputation: 1148

Query so much slower than stored procedure?

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?

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

Upvotes: 1

Views: 226

Answers (4)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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.

Upvotes: 1

Spevy
Spevy

Reputation: 1325

There is a similar question here

The problem was the stored proc declaration SET ANSI_NULLS OFF

Upvotes: 0

roman
roman

Reputation: 117380

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
)

Upvotes: 0

alzaimar
alzaimar

Reputation: 4622

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.

Upvotes: 0

Related Questions