June 6, 2008 at 11:05 am
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.
June 6, 2008 at 11:12 am
That sounds strange.
Can you capture a simple example, save off the two plans and attach them?
June 6, 2008 at 11:35 am
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
June 6, 2008 at 11:38 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2008 at 12:35 pm
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
June 6, 2008 at 12:45 pm
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
June 6, 2008 at 12:51 pm
ganatra.neha (6/6/2008)
That was bulls eye. You were rightThank 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2008 at 3:25 pm
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 @user-id 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 = @user-id)
AND Pe.CompanyID = @CompanyID)
June 9, 2008 at 5:22 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply