Difference In Estimated number of Rows and Actual Number of Rows in Execution Plan

  • ganatra.neha

    SSC-Addicted

    Points: 423

    Hi folks

    I am tuning few stored procedures and when i analyze the execution plan for them one common feature in all execution plan is that estimated number of rows is 1 however the actual number of rows are in thousands.

    I checked the statistics and have updated the statistics too but the execution plan is same.

    It does index seek or CI seek but the number of reads and CPu isvery high, i have tried all other possibilities, but haven't found any resolution.

    Please can you let me know how I could resolve this matter.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717408

    That sounds strange.

    Can you capture a simple example, save off the two plans and attach them?

  • ganatra.neha-768406

    Old Hand

    Points: 343

    For example

    Declare @Username nvarchar(50)

    Declare @SearchType nvarchar(50)

    Declare @FilterValue nvarchar(50)

    DECLARE @CID smallint

    Set @Username = 'abc '

    Set @SearchType = 'abc'

    Set @FilterValue = N'pap'

    DECLARE @ModeSearch TABLE

    (

    PID int PRIMARY KEY,

    CID smallint

    )

    INSERT INTO @ModeSearch(PID, CID)

    SELECT DISTINCT P.PID,

    Patient.CompanyID

    FROM P WITH (READPAST)

    JOIN PSD WITH (READPAST) ON

    P.PID = PSD.PID

    AND P.CID= PDS.CID

    WHERE PSD.LastUnitMode IS NOT NULL

    AND PSD.LastUnitMode LIKE '%' + @FilterValue + '%'

    AND P.PID IN (SELECT PID FROM dbo.fnGetSearchPatients(@SearchType,@Username,@CID))

    The execution plan as follows

    There is nested loop join with 0% cost (which is good)

    But when we see the retrieval of data from P Table and PSD table

    There is Index Seek on both P and PSD table

    Cost is 11% and Actaul number of rows is 1

    But actual number of rows is 10,00

  • Grant Fritchey

    SSC Guru

    Points: 396247

    I don't see the attachment.

    I'll bet you're working with a table valued function, probably a multi-statement function instead of an inline. Right?

    If so, the optimizer doesn't and can't know what to do with that when it's desiging the execution plan, so it creates a plan for a one row table. That sure sounds like what you're seeing. It's a major reason why the multi-statement UDF is a construct to avoid when you're dealing with sets of data larger than 10's of rows.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ganatra.neha-768406

    Old Hand

    Points: 343

    That was bulls eye. You were right

    Thank you

    I shall try and rewrite the queries and avoid the the multi-statement UDF

    Thank you once again

  • GSquared

    SSC Guru

    Points: 260824

    Table variables will do the same thing. They'll estimate at one row, pretty much regardless of what's going to actually be in them. Recursive CTEs are another one that sometimes do that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Grant Fritchey

    SSC Guru

    Points: 396247

    ganatra.neha (6/6/2008)


    That was bulls eye. You were right

    Thank you

    I shall try and rewrite the queries and avoid the the multi-statement UDF

    Thank you once again

    Glad I could help. Thanks for the feedback.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ganatra.neha

    SSC-Addicted

    Points: 423

    Hi i am back again but with sad news:crying:

    I tried replacing the above function with a t-sql code and I am again back to where i started .

    The number of reads have decreased though not considerably and the execution plan is where it was , estimated number of rows 1 and actual number of rows = 11870

    Following is the query

    Declare @Username nvarchar(50)

    Declare @SearchType nvarchar(50)

    Declare @FilterValue nvarchar(50)

    DECLARE @CompanyID smallint

    DECLARE @userid smallint

    Set @Username = N'abc'

    Set @SearchType = N'abc'

    Set @FilterValue = N'abc'

    EXEC dbo.GetUserIDByname @Username,@USerID OUTPUT, @CompanyID OUTPUT

    DECLARE @ModeSearch TABLE

    (

    PID int PRIMARY KEY,

    CID smallint

    )

    INSERT INTO @ModeSearch(PID, CID)

    SELECT DISTINCT P.PID,

    P.CID

    FROM P WITH (READPAST)

    JOIN PSD WITH (READPAST) ON P.PID = PSD.PID AND P.CID = PSData.CID

    WHERE PSD.LastUnitMode IS NOT NULL

    AND PSD.LastUnitMode LIKE '%' + @FilterValue + '%'

    AND P.PID IN (SELECT DISTINCT PID

    FROM P WITH (NOLOCK)

    JOIN Pe WITH (NOLOCK) ON P.PID = Pe.PeID AND P.CID = P.CID

    WHERE Pe.OfficeID = (SELECT OfficeID FROM Pe WHERE PeID = @userid)

    AND Pe.CompanyID = @CompanyID)

  • Grant Fritchey

    SSC Guru

    Points: 396247

    As GSquared pointed out above, table variables work in a similar fashion. Statistics aren't maintained on them (except when there is a primary key) so the optimizer always assigns them one row.

    Why are you loading the data into a temporary table? Why not simply perform the select statement as is, or if you need to join to this data, make it a derived table?

    The query you show doesn't need the table variable at all.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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