3

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'

Wrong number of rows

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
5

13.83 is the number of rows it thinks it needs to satisfy the 15 rows you're asking for. It guesses that after that it can stop.

Paul White goes into it a lot more in his post Inside Optimizer Row Goals in Depth.

Since he spends a lot of time at this site you'll probably see an extensive answer, going into a lot of detail about why it's 13.83 and not 13.84. ;)

I suggest you to read below blogs

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.