I have a query based on two newly created tables in SQL Server 2005
. I have index created on these tables. But when I write a query with JOIN
condition, the number of actual rows returned is too high whereas the estimated number of rows is less. And the query plan uses a nested loop. [Query plan diagram is given below.] Since these are new tables, I think the usual stale statistics is not the reason here.
I can rewrite this query by using NOT EXISTS
(as shown in Query 2) and the actual number of rows is reduced. But I have other requirements to fetch details from LWTest table using INNER JOIN
– there the high number of actual rows is a problem.
So, any clue why is the actual number of rows is so high even though there is index and statistics? What can be done to bring it down?
Note: Number of rows in TransmittedManifests –904. Number of rows in LWTest -- 829785
UPDATE
Note2: Compatibility_Level is 90. Elapsed time for Query 1 is 64 ms. Query 2 elapsed time is 6 ms.
Note3: Tried OPTION (RECOMPILE), rebuild index and UPDATE STATISTICS WITH FULLSCAN on these two tables. But the actual number of rows is still high.
Thansk to Martin Smith for details of difference in count per execution (estimate) and total count (actual).The actual rows is count across all executions, the estimated is count per execution. Estimated rows, actual rows and execution count.
For Query 1, the ActualExecutions
="904" for LWTest table.
Query
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
PRINT 'BEGIN -----------------------------------------'
--Query 1
SELECT T.[Manifest]
FROM dbo.TransmittedManifests T
LEFT OUTER JOIN dbo.LWTest LW (NOLOCK)
ON LW.Manifest = T.Manifest
WHERE LW.Manifest IS NULL
PRINT 'QUERY 1 COMPLETED -----------------------------------------'
--Query 2
SELECT T.[Manifest]
FROM dbo.TransmittedManifests T
WHERE NOT EXISTS (SELECT Manifest FROM dbo.LWTest L
WHERE L.Manifest = T.Manifest
)
PRINT 'QUERY 2 COMPLETED -----------------------------------------'
--Query 3
SELECT T.[Manifest],LineItemID,InvoicedQuantity
FROM dbo.TransmittedManifests T
INNER JOIN dbo.LWTest LW (NOLOCK)
ON LW.Manifest = T.Manifest
Table Schema
Query Plan 2