sql server logical reads are more with where clause?

Question

SELECT C.custid, C.companyname, O.orderid, O.orderdate FROM Sales.Customers AS C INNER JOIN Sales.Orders AS O ON C.custid = O.custid (830 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SELECT C.custid, C.companyname, O.orderid, O.orderdate FROM Sales.Customers AS C INNER JOIN Sales.Orders AS O ON C.custid = O.custid WHERE O.custid < 5 (30 row(s) affected) Table 'Customers'. Scan count 0, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In second query I'm limiting the rows with O.custid < 5 still it gives me logical reads 60? It should be less than 21 which shown for first query in customer table.

Logical reads don't mean a lot. Measure elapsed time. A where clause can mean both less or more work for SQL Server. — usr
– usr, Commented Dec 28, 2013 at 15:05

Nimantha · Accepted Answer · 2022-01-05 03:59:32Z

You need to look at the query plans for the two queries. SQL Server has several options for performing these queries. For instance, here are some examples:

Scanning Customers and looking up each order in Orders using an index.
Scanning Orders table and looking up each customer in Customers.
Creating a hash table for Orders and Customers and then merging the results in the hash table.
Sorting both Customers and Orders and doing a merge join.

(and there are undoubtedly other possibilities as well).

In any case, the SQL optimizer chooses the best method for doing the join. When you add a where clause, the best solution may be different. This is expected, and an indication that the optimizer is doing its job.

Collectives™ on Stack Overflow

sql server logical reads are more with where clause?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related