SQL Server Performance

ad hoc query ridiculously faster than sproc?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jholovacs, May 22, 2007.

  1. jholovacs New Member

    I have a stored procedure similar to this:

    set ANSI_NULLS OFF
    set QUOTED_IDENTIFIER OFF
    GO

    CREATE PROC [links].[dbo].[proc_address_select]
    @vINT,
    @idVARCHAR(15)
    WITH RECOMPILE
    AS
    BEGIN
    SELECT
    s.field1,
    s.field2,
    first_name,
    last_name,
    addr_house_num,
    addr_street,
    addr_supp1,
    addr_zip,
    dbOther.dbo.func_get_name(p.id) as e_id,
    vsa.id as t_id,
    CASE COALESCE(status_cd, '')
    WHEN 'M' THEN 'Y'
    ELSE
    ''
    END
    as gauge,
    c.customer_id,
    s.service_id
    FROM
    links.dbo.service s
    LEFT OUTER JOIN survey.dbo.prospect p
    ON s.service_id = p.service_id
    LEFT OUTER JOIN inf.dbo.vw_transform vsa
    ON s.service_id = vsa.service_id
    LEFT OUTER JOIN customer.dbo.customer c
    ON s.service_id = c.service_id
    AND isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
    WHERE
    vsa.v_id = @v
    AND vsa.t_id = @id
    ORDER BY
    addr_street, addr_house_num, addr_supp1
    END


    If I run a query like:

    exec proc_service_address_select @v = <some number>, @id = <some varchar string>

    this proc will return results in about 6-10 minutes.

    If however, I run this query as ad hoc in management studio or Query analyzer with the same parameter values like so:

    DECLARE @v INT
    DECLARE @id VARCHAR(15)

    set @v=<some number>
    set @id=<some varchar string>

    SELECT
    s.field1,
    s.field2,
    first_name,
    last_name,
    addr_house_num,
    addr_street,
    addr_supp1,
    addr_zip,
    dbOther.dbo.func_get_name(p.id) as e_id,
    vsa.id as t_id,
    CASE COALESCE(status_cd, '')
    WHEN 'M' THEN 'Y'
    ELSE
    ''
    END
    as gauge,
    c.customer_id,
    s.service_id
    FROM
    links.dbo.service s
    LEFT OUTER JOIN survey.dbo.prospect p
    ON s.service_id = p.service_id
    LEFT OUTER JOIN inf.dbo.vw_transform vsa
    ON s.service_id = vsa.service_id
    LEFT OUTER JOIN customer.dbo.customer c
    ON s.service_id = c.service_id
    AND isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
    WHERE
    vsa.v_id = @v
    AND vsa.t_id = @id
    ORDER BY
    addr_street, addr_house_num, addr_supp1


    This will give me the expected result, with one huge difference: this query takes 10 to 15 seconds!!!

    The data execution plans are completely different, and I have no idea why. Suggestions?
  2. Adriaan New Member

    I'd try dropping the WITH RECOMPILE clause.

  3. jholovacs New Member

    eh... that's what it started with. I thought it might be caching its execution plan, so I added the RECOMPILE hint, with no noticeable performance difference.

    The data execution plan between the ad hoc query and the sproc are pretty radically different, and I'm scratching my head over why that would be.
  4. FrankKalis Moderator

    Can you post the execution plans?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. jholovacs New Member

    maybe... is there a way to make them "text only"? the DEP takes up like 10 pages of mostly empty space, very difficult to follow as an image file.
  6. FrankKalis Moderator

    You can use SET SHOWPLAN_TEXT ON to get the textual representation of the plans. It might be good to wrap the plans with the [ code ] ... [ / code ] tags. Makes it easier to read them. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  7. joechang New Member

    look into parameter sniffing

    what are the data type for the columns below
    vsa.venture_id = @jv
    AND vsa.transformer_id = @id
  8. jholovacs New Member

    venture_id is int, and transformer_id is varchar(15), just like the vars. Plus, I would think that if that was the performance issue, the result would be the same across both query methods.

    I'm trying to get management buy-in to post the DEP's; it's all proprietary stuff so they have to approve.
  9. joechang New Member

    ok, but it is still a parameter sniffing issue
    you need to search this matter

    for now, try


    CREATE PROC [dbo].[proc_service_address_select] @jv INT, @id VARCHAR(15)
    AS
    BEGIN

    DECLARE @jv2 INT
    DECLARE @id2 VARCHAR(15)

    set @jv2=@jv
    set @id2=@id

    SELECT s.field1, s.field2, first_name, last_name, addr_house_num,addr_street,
    addr_supp1,addr_zip, dbOther.dbo.func_get_name(p.element_id) as element,
    vsa.transform_id as el,
    CASE COALESCE(campaign_status_cd, '') WHEN 'M' THEN 'Y' ELSE '' END as meter,
    c.customer_id, s.service_id
    FROM clink.dbo.service s
    LEFT OUTER JOIN prospect.dbo.prospect p ON s.service_id = p.service_id
    LEFT OUTER JOIN structure.dbo.vw_transform vsa ON s.service_id = vsa.service_id
    LEFT OUTER JOIN customer.dbo.customer c ON s.service_id = c.service_id
    AND isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
    WHERE vsa.venture_id = @jv2
    AND vsa.transformer_id = @id2
    ORDER BY addr_street, addr_house_num, addr_supp1
    END

  10. FrankKalis Moderator

  11. jholovacs New Member

    this still took 6:00.
  12. joechang New Member

    now you need to look at the execution plan for each

    also
    does it take 6min to execution the sp from the client app
    does it take 6min to exec the sp from Management Studio?
  13. jholovacs New Member

    is there a way to "capture" the execution plan for the ad hoc query, and force the sp to use it?

Share This Page