Post reply

sp_prepexec results in different and bad execution plan

  • April 10, 2014 at 2:12 pm

    #396583

    Hi,

    We massively imported 1.5 million rows in the ab_abcontact table a couple of weeks ago. Since that time, the queries in the search screen perform pourly. I caught those requests and figured out an index that would help them. In my tests the index helped the queries a lot but in the application it didn’t change anything. I found out that the queries, when ran in a sp_prepexec, didn’t use the new index, even if I updated the statistics. What am I missing here?

    The queries :

    declare @p1 int

    exec sp_prepexec @p1 output,N'@P0 int,@P1 int,@P2 int,@P3 int,@P4 int,@P5 varchar(8000)',N'SELECT COUNT(*) FROM (SELECT TOP (301) 1 as countCol FROM ab_abcontact qRoot WHERE qRoot.Subtype IN (@P0,@P1,@P2,@P3,@P4) AND qRoot.KeywordDenorm LIKE @P5 COLLATE French_CI_AI AND qRoot.StateDenorm = 10063 AND qRoot.CountryDenorm = 10039 AND qRoot.Retired = 0) countTable',5,8,9,11,13,'%fix auto beauport%'

    select @p1

    Returns in 3975ms.

    SELECT COUNT(*) FROM (SELECT TOP (301) 1 as countCol FROM ab_abcontact qRoot WHERE qRoot.Subtype IN (5,8,9,11,13) AND qRoot.KeywordDenorm LIKE '%fix auto beauport%' COLLATE French_CI_AI AND qRoot.StateDenorm = 10063 AND qRoot.CountryDenorm = 10039 AND qRoot.Retired = 0) countTable;

    Returns in 59ms.

    Thanks a lot,

    Jean-Michel

  • April 11, 2014 at 4:33 am

    #1705189

    Just looking at pictures of execution plans and not able to see the properties, this is a bit of a guess, but I'd say it's probably bad parameter sniffing.

    When you use sp_executesql you're passing parameters. Depending on how SQL Server interprets those, versus hard coded values, you can see radically different plans. Read through the properties on the operators to understand how it came to decide it needed that more complex plan. Especially focus on the properties in the SELECT operator to see what compile time values it used.

    That LIKE statement is going to result in scans pretty much every time.

    "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

Viewing 2 posts - 1 through 1 (of 1 total)

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