Wrong estimated number of rows using TOP condition
3
votes
1
answer
82
views
I guess this is a very basic question but I can't get my head around it..
I have perfect statistics in place for a column but while using a TOP condition the estimated number of rows are always a number very close to the number used for the TOP operator giving me a complete wrong number.
Is there any easy explanation for this knowing that the statistics for the InterviewerID on the application table know exactly that theID 12868 rows have more than 3K rows ? Why is telling me 13.83?
Version being used: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Thank you all!
How I call the SP ?
EXEC agy.sp_searchTest3 @interviewerId = 12868,@searchTerm = 'aar'
Query causing the issues :
ALTER PROCEDURE [agy].[sp_searchTest3] (
@interviewerId INT
,@searchTerm VARCHAR(50) = NULL
,@searchApplicationId INT = NULL
)
AS
BEGIN
IF @searchApplicationId IS NOT NULL
SELECT TOP 10 u.userId
,u.firstName + ' ' + u.lastName AS 'fullname'
,app.applicationId
,u.loginId AS email
,adi.URI AS imageurl
FROM [app].[application] AS app
INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
INNER JOIN [usr].[user] AS u ON u.userId = a.userId
LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
AND ud.documentTypeId = 5 -- (Portrait pic)
AND ud.documentStateId NOT IN (
3
,5
) -- (Rejected, Deleted)
LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
WHERE app.interviewerId = @interviewerId
AND u.isActive = 1
AND app.applicationId = @searchApplicationId
IF @searchTerm IS NOT NULL
SELECT
TOP 15 -- everytime I change the top value the estimated number of rows change as well
u.userId
,u.firstName + ' ' + u.lastName AS 'fullname'
,app.applicationId
,u.loginId AS email
,adi.URI AS imageurl
FROM [app].[application] AS app
INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
INNER JOIN [usr].[user] AS u ON u.userId = a.userId
LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
AND ud.documentTypeId = 5 -- (Portrait pic)
AND ud.documentStateId NOT IN (
3
,5
) -- (Rejected, Deleted)
LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
WHERE app.interviewerId = @interviewerId
AND u.isActive = 1
AND (
(u.firstName LIKE @searchTerm)
OR (u.lastName LIKE @searchTerm)
OR (u.loginId LIKE @searchTerm)
)
END
Query causing the issues :
ALTER PROCEDURE [agy].[sp_searchTest3] (
@interviewerId INT
,@searchTerm VARCHAR(50) = NULL
,@searchApplicationId INT = NULL
)
AS
BEGIN
IF @searchApplicationId IS NOT NULL
SELECT TOP 10 u.userId
,u.firstName + ' ' + u.lastName AS 'fullname'
,app.applicationId
,u.loginId AS email
,adi.URI AS imageurl
FROM [app].[application] AS app
INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
INNER JOIN [usr].[user] AS u ON u.userId = a.userId
LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
AND ud.documentTypeId = 5 -- (Portrait pic)
AND ud.documentStateId NOT IN (
3
,5
) -- (Rejected, Deleted)
LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
WHERE app.interviewerId = @interviewerId
AND u.isActive = 1
AND app.applicationId = @searchApplicationId
IF @searchTerm IS NOT NULL
SELECT
TOP 15 -- everytime I change the top value the estimated number of rows change as well
u.userId
,u.firstName + ' ' + u.lastName AS 'fullname'
,app.applicationId
,u.loginId AS email
,adi.URI AS imageurl
FROM [app].[application] AS app
INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
INNER JOIN [usr].[user] AS u ON u.userId = a.userId
LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
AND ud.documentTypeId = 5 -- (Portrait pic)
AND ud.documentStateId NOT IN (
3
,5
) -- (Rejected, Deleted)
LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
WHERE app.interviewerId = @interviewerId
AND u.isActive = 1
AND (
(u.firstName LIKE @searchTerm)
OR (u.lastName LIKE @searchTerm)
OR (u.loginId LIKE @searchTerm)
)
END
Asked by Tiago
(289 rep)
May 15, 2016, 08:58 PM
Last activity: May 12, 2020, 02:32 PM
Last activity: May 12, 2020, 02:32 PM