More Logical Reads but Query Executing in Less time

Question

Situation 1

Table 'lead_transaction'. Scan count 10, logical reads 394, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'appt_master'. Scan count 20, logical reads 4532, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Situation 2

Table 'lead_transaction'. Scan count 36466, logical reads 117088, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'appt_master'. Scan count 36466, logical reads 195492, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In Situation 1 Query is executing in 4 seconds, Used Left join

&

In Situation 2 Query is executing in 3 seconds, Used outer apply ,but Logical reads are very high.

So what is good as per performance?

1) What are the CPU stats? 2) Show us the actual queries, 3) Post the Query Plans (Actual) — RBarryYoung
– RBarryYoung, Commented Mar 28, 2014 at 22:27
Because there is only 1 second difference in performance, I am wondering whether the second query performed better due to the data being in the cache already. Have you tried flushing the cache and then seeing the performance of the queries. — user3248346
– user3248346, Commented Mar 31, 2014 at 13:29

CathalMF · Accepted Answer · 2014-03-27 09:37:02Z

Since Logical Reads are from the data cache (memory) I would think the fact that there is a lot of reads will make little difference and it would seem that the second query is more efficient when reading a lot of data in small chunks while the first query reads the data in large chunks.

Id be interested to see how the performance would work if it was making physical reads and not logical reads.

Try clearing the buffers and execution plans before running each query and see what the performance is like.

•DBCC DROPCLEANBUFFERS clears buffer pool •DBCC FLUSHPROCINDB clears execution plans for that database

Thanks for ans, but I am still not clear, which query is better?
If I execute these commands on my productions sever, will it heart anything?
It wont "hurt" anything but it will cause ALL queries to run slower until the buffers and caches are built again. Depending on how busy your LIVE instance is you might not want to run it on LIVE. I would suggest you research these two functions and make your own decision to run it or not.

Collectives™ on Stack Overflow

More Logical Reads but Query Executing in Less time

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related