SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ganatra.neha
ganatra.neha
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 167
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
Steve Jones
SSC Guru
SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)

Group: Administrators
Points: 632979 Visits: 21355
That sounds strange.

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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ganatra.neha-768406
ganatra.neha-768406
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 168
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
Grant Fritchey
SSC Guru
SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)

Group: General Forum Members
Points: 367362 Visits: 34554
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
ganatra.neha-768406
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 168
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
GSquared
SSC Guru
SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)

Group: General Forum Members
Points: 241558 Visits: 9733
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
Grant Fritchey
SSC Guru
SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)

Group: General Forum Members
Points: 367362 Visits: 34554
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
ganatra.neha
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 167
Hi i am back again but with sad newsCrying

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
Grant Fritchey
SSC Guru
SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)SSC Guru (367K reputation)

Group: General Forum Members
Points: 367362 Visits: 34554
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search